Skip to main content

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!

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





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


, "(^, )?", "")

Reply


Terms & Conditions