Help

Concatenate 21 lookup fields with comma separators

Topic Labels: Formulas
1026 2
cancel
Showing results for 
Search instead for 
Did you mean: 
kts
5 - Automation Enthusiast
5 - Automation Enthusiast

I have 21 lookup fields that are used by different team members, which may or may not be filled in for a given Airtable record (i.e., one record might have lookup items tagged in 3 of the fields, another record 5 of the fields, yet another just 1, and so on). I’d like to summarize, with a formula field, all of the lookup items tagged for each record across these 21 fields.

When I just use an & operator, I’ll end up with something like this, if the record has items tagged in more than one of these 21 fields:

Tag 1A, Tag 1BTag2A, Tag 2B, Tag 2CTag 3A

…so I want to add a comma and space separator between the tags from each field, but only when values are actually present in any of the fields so as to not have redundant commas. I tried doing something like this:

{Field 1} & IF(AND({Field 1}, OR({Field 2}, {Field 3}, {Field 4}, {Field 5}, {Field 6}, {Field 7}, {Field 8}, {Field 9}, {Field 10}, {Field 11}, {Field 12}, {Field 13}, {Field 14}, {Field 15}, {Field 16}, {Field 17}, {Field 18}, {Field 19}, {Field 20}, {Field 21})), “, “)

& {Field 2} & IF(AND({Field 2}, OR({Field 3}, {Field 4}, {Field 5}, {Field 6}, {Field 7}, {Field 8}, {Field 9}, {Field 10}, {Field 11}, {Field 12}, {Field 13}, {Field 14}, {Field 15}, {Field 16}, {Field 17}, {Field 18}, {Field 19}, {Field 20}, {Field 21})), “, “)

& {Field 3} & IF(AND({Field 3}, OR({Field 4}, {Field 5}, {Field 6}, {Field 7}, {Field 8}, {Field 9}, {Field 10}, {Field 11}, {Field 12}, {Field 13}, {Field 14}, {Field 15}, {Field 16}, {Field 17}, {Field 18}, {Field 19}, {Field 20}, {Field 21})), “, “)

… and so on for all 21 fields. But this errors out. Could somebody help me? Thank you!

2 Replies 2

Struggling to see a concise formula for this, but you could use a script. Something like this:

Screenshot 2021-11-16 at 22.39.28

with script:

let table = base.getTable('Table')
let query = await table.selectRecordsAsync()

for(let record of query.records) {
    let array = []
    if(record.getCellValue('Tag1')) {
        array.push(record.getCellValue('Tag1'))
    }
    if(record.getCellValue('Tag2')) {
        array.push(record.getCellValue('Tag2'))
    }
    if(record.getCellValue('Tag3')) {
        array.push(record.getCellValue('Tag3'))
    }        
    let result = array.join()
    await table.updateRecordAsync(record, {
        'Result': result
    })
}

The script is a bit rough, could do with some polishing, but you can see where it is going. Running the script won’t be a real time thing of course, so this could be a problem for your use case. Also possible that you could run this script in an automation when the record is updated (so running it on one record at a time).

Here’s a possible formula-based approach:

{Field 1} & 
IF({Field 2}, ", " & {Field 2}) &
IF({Field 3}, ", " & {Field 3}) &
IF({Field 4}, ", " & {Field 4}) &
...

The presence of a comma before any given item only depends on that item. Items after it will take care of themselves later in the formula.

If {Field 1} isn’t always guaranteed, you could end up with a leading comma-space combo with that formula. This revision will take care of that:

REGEX_REPLACE(
  {Field 1} & 
  IF({Field 2}, ", " & {Field 2}) &
  IF({Field 3}, ", " & {Field 3}) &
  IF({Field 4}, ", " & {Field 4}) &
  ...
  IF({Field 21}, ", " & {Field 21})
, "(^, )?", "")