Concatenate 2 collaborator fields

[Brite_Admin]

I have two fields that use the Collaborator field in a table (Lead & Support). In order to do some filtering, I need to have a field that concatenates both collaborator fields but Airtable doesn’t want to let me. Any workaround?

Thanks!

[JonathanBowen]

How are you doing the concatenation? This works for me:

Collab1 & ' - ' & Collab2

[Brite_Admin]

Thanks Jonathan, what I need to do for filtering purposes is have the result as Collaborators, not just text. Any way?

[JonathanBowen]

OK, I understand. It could be done with a script. Or it could be done with the new Automations feature. Try this:

• Create a new collab field that allows multiple collaborators
• Create a new view which is filtered where collab1 and collab2 are both not empty
• Now create a new automation with trigger “when a record enters a view” selecting your newly created view from above.
• In the “action” part select “update record”
• In Record ID select the ID from the trigger:

• in the “fields” section select your new multi-collaborator field and add in the two user ids of the collaborators in collab1 and collab2:

Note the comma between the two ID values (it won’t work without this)

So, when the lead and support collabs have been added, the record enters the view, the automation runs and it populates the multi-collab field with both.

[Brite_Admin]

Thanks again, Jonathan. This looks promising but I am not familiar with the new automations. I tried to follow your example.

When I get to the “fields” area,

1. I choose the new multi-collab field
2. I hit the plus button and chose “Field Values” but both collab fields are greyed out and it says “This field type can’t be added”. And I am stuck.

If I did it correctly, could the problem be that both of my collab fields are also multi-select? If not, what could be causing the fields to be unavailable?

I really appreciate your help.

[Brite_Admin]

In addition to the above, I have two questions:

1. Should the view filter be Collab1 is empty AND Collab2 is empty? (I was unsure if it should be “AND” or “OR”
2. If there are collaborators in both fields and I later add another collaborator to either the collab1 or collab2 field, will it update the Collabs field or will it only work the first time (which won’t be a viable solution). I need to be able to update the Collabs field when either collab1 or Collab2 fields gets edited.

[JonathanBowen]

Hi @Brite_Admin, OK maybe this approach won’t work. I assumed that your support and lead fields are “single” collaborators. You’re right that if the field is a multi collaborator you can’t select it in the automation. This approach also won’t work if you add an additional collab to one of the fields later as our definition of the view wouldn’t be re-triggered (although potentially, there could be a different view that does re-trigger).

As an aside, the view was based on:

Collab1 is NOT empty AND Collab2 is NOT empty

i.e. they both have an entry so are ready to be concatenated into the multi field.

At this point, I’d be looking at a script to do the work for you. Something like this will do the trick:

let table = base.getTable('Table 1');

let query = await table.selectRecordsAsync();

for (let record of query.records) {
        let collabs = [];
        if (record.getCellValue('collab1')) {
            for (let collab1 of record.getCellValue('collab1')) {
                collabs.push({id:collab1.id})
            }
        }
        if (record.getCellValue('collab2')) {
            for (let collab2 of record.getCellValue('collab2')) {
                collabs.push({id:collab2.id})
            }
        }
        // remove duplicate users
        let uniqueCollabs = Array.from(new Set(collabs.map(a => a.id)))
            .map(id => {
            return collabs.find(a => a.id === id)
            }
        )       
        let allCollabs = await table.updateRecordAsync(record, {
            "All collaborators": uniqueCollabs
        })
}

based on this table:

The script elements in red would need to be modified to match your table and field names. Note that each time you run it it runs for the whole table and replaces the “All collaborators” value with a new value (the two collab fields mashed together).

Try it out on a copy of your base first :slightly_smiling_face: