Rolling up linked records from multiple columns

[TWu]

I’m trying to roll up linked records from multiple columns and return only the unique ones (ideally in record form, not string). For my example imagine I have different car models that can have different packages of wheels, chassis, and accessories. Each of those have separate tables where they have keywords associated with each package. I’d like to display a unique list of keywords associated with each car model that is a rollup of ALL the unique keywords associated with all 3 packages associated with each car.

I’ve tried 3 solutions on the far right but I’m running into a few challenges:

1. CONCATENATE({Keywords-Wheels},", “,{Keywords-Chassis},”, ",{Keywords-Accessories}). My solutions are returning strings. Is it not possible to return the actual records so they’re linked?
2. I have commas with empty spaces in between for cases where columns may not have any tags associated
3. ARRAYUNIQUE({Keywords-Wheels},{Keywords-Chassis},{Keywords-Accessories}). When I try to apply ArrayUnique() across the 3 columns, it only takes the records from the first column.

Any help would be appreciated! I’m an experienced google sheets/excel user but have no idea how to do this within the limitation of Airbase formulas.

[Justin_Barrett]

Welcome to the community, @TWu! :grinning_face_with_big_eyes: Currently Airtable’s array functions only operate on single arrays, and there aren’t any functions to combine arrays. The following request in the Product Suggestions category looks like the primary space where folks are asking for updates to Airtable’s array processing capabilities, if you want to voice your thoughts:

Comparing two arrays and remove values that appear in both Product Suggestions

Hi airtable! I have a list (an array) of items in two columns. Each row represents two different lists (one in each column). I’d like to be able to compare the two columns and remove what is common in both. For example Row 1: Column 1: Apple, Orange, Pineapple, Kiwi Column 2: Apple, Kiwi Result: Column 3: Orange, Pineapple Row 2: Column 1: Banana, Orange, Grape, Kiwi, Pineapple, Passion Fruit Column 2: Orange, Grape, Pineapple Result: Column 3: Banana, Kiwi, Passion Fruit etc. The …

What you want might be possible to accomplish with the help of custom code that ties into your base via the API, but I’ll have to defer to those with API experience to discuss the pros and cons of that option.

[TWu]

@Justin_Barrett If I’m ok with using strings instead of array, do you know of a solution that will remove the extra commas and also make the list unique? Thanks!

[Justin_Barrett]

For controlling the commas, here’s one way to do it:

{Keywords-Wheels} & IF(
    AND(
        {Keywords-Wheels},
        OR(
            {Keywords-Chassis},
            {Keywords-Accessories}
        )
    ),
    ", "
) & {Keywords-Chassis} & IF(
    AND(
        {Keywords-Chassis},
        {Keywords-Accessories}
    ),
    ", "
) & {Keywords-Accessories}

I tested this with all combinations of all three fields (and thankfully you’ve only got three fields to combine, or this would be a lot more complicated).

Where you hit a pretty solid roadblock is trying make the list unique. The only functions in Airtable (and, for that matter, in most programming languages) that are designed to do that operate on arrays, not strings. If Airtable had a function that would take a string and create an array from it by splitting it at specific separators—like the .split method on Python string objects—you’d be in business with ARRAYUNIQUE on that resulting array. Sadly, such a function doesn’t exist in Airtable yet, which takes me back to an API solution, where converting back and forth between strings and arrays is much easier.

[TWu]

Oh man, unique can only be on an array… that does throw a wrench in things. And unfortunately looks like I might be stuck until they can process multiple arrays because the 3 columns I described were for a sample base. The real one has 8 and I cannot guarantee the order of them.

I appreciate your time on this, even though I don’t have an implementable solution, it was useful to see how we could write code snippets in formula cells.

[Justin_Barrett · Accepted solution]

FWIW, here’s another approach for eliminating extraneous commas between concatenated pieces that doesn’t rely on field order, in case this could be useful elsewhere. This requires two fields: one to mash all the pieces together and remove the inner comma-space duplicates (I named this {Mashed Up}), and then one to extract the useful part out of the middle (I called mine {Final}). At first glance it might seem like the extra commas added at the beginning and end of {Mashed Up} don’t make sense, but they actually make the nested substitutes operate more efficiently, and make the final extraction much easier as well.

First, here’s the {Mashed Up} formula:

SUBSTITUTE(
    SUBSTITUTE(
        SUBSTITUTE(
            ", " & {Field 1} & ", " &
            {Field 2} & ", " &
            {Field 3} & ", " &
            {Field 4} & ", " &
            {Field 5} & ", " &
            {Field 6} & ", " &
            {Field 7} & ", " &
            {Field 8} & ", ",
             ", , ",
             ", "
        ),
         ", , ",
         ", "
    ),
     ", , ",
     ", "
)

Next, the formula in {Final}:

MID({Mashed Up}, 3, LEN({Mashed Up}) - 4)

And finally a screenshot of the result:

UPDATE: I should have pointed out when posting this yesterday that the number of nested SUBSTITUTE levels depends on how many fields you’re trying to combine. More fields will require more nesting, fewer fields will mean that you could remove one or more levels if you wished (though leaving the extras won’t hurt anything).

[TWu]

This is beautiful, I’ll use this in my base, thank you!