Rolling up linked records from multiple columns

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:

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.

@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!

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.

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).