Bulk update (append) linked records without deleting existing linked records
I have Two tables. People and Groups. The groups table is used for an assortment of things in our organization. So most everyone is a part of 3-20 groups.
My problem comes when I need to add more than 30 or so people to a single group. Bulk adding overwrites all of the varied groups that a person is in and i can’t find an angle to add more than one person at a time to the group without overwriting all of that person’s existing groups.
I’m attempting to create an automation that I can use a view that automatically appends a person to a group but I’m thinking there has to be a better way.
Im assuming this is a onetime thing that you wanna do every now and then?
You can have a helper field which is a linked to another record field, and another field that is a formula concatenating the existing groups with the new group, then copy paste the formula to the original field and delete the helper field (or at least delete the data)
There are 2 different ways of handling this:
In an automation action that updates a linked record field, you can append entries to the field by inserting the existing field into itself (which brings in all the existing values), adding a comma, and then inserting the new value afterwards.
Use Make’s advanced automations for your automations, which allows you to handle arrays like linked record fields.
Im assuming this is a onetime thing that you wanna do every now and then?
You can have a helper field which is a linked to another record field, and another field that is a formula concatenating the existing groups with the new group, then copy paste the formula to the original field and delete the helper field (or at least delete the data)
Great idea! I’ll try that tomorrow to see how it fits my workflow.
At some point Airtable has to make this easier right?
In an automation action that updates a linked record field, you can append entries to the field by inserting the existing field into itself (which brings in all the existing values), adding a comma, and then inserting the new value afterwards.
Genius move. Are there any random consequences that come from doing this? Like could it affect any other automations that might be looking at a view to trigger emails etc??
I guess what I’m asking is…Does Airtable see this as taking them out of the group/record and then suddenly putting them back in?
Love your solution. How do we get Airtable to simply add an “append” function to make this easier.
Genius move. Are there any random consequences that come from doing this? Like could it affect any other automations that might be looking at a view to trigger emails etc??
I guess what I’m asking is…Does Airtable see this as taking them out of the group/record and then suddenly putting them back in?
Love your solution. How do we get Airtable to simply add an “append” function to make this easier.
Hmmmm… that’s a great question! I don’t know the answer to that one! We should test that out to determine the answer to that question! I wonder if anybody else knows off the top of their head, like @kuovonne?
That would be much easier! You may want to email support@airtable.com to ask them to add this as a feature request.
Genius move. Are there any random consequences that come from doing this? Like could it affect any other automations that might be looking at a view to trigger emails etc??
I guess what I’m asking is…Does Airtable see this as taking them out of the group/record and then suddenly putting them back in?
Love your solution. How do we get Airtable to simply add an “append” function to make this easier.
No. The people are not temporary taken out of any groups with this method. The original list of groups is simply replaced with the new list of groups.
There are some aspects where you need to be careful. You should make sure that your group names are 100% unique and do not contain any commas, quotes, or new line characters. (Other special characters might also cause problems.) I tend to prefer using record IDs in these cases to avoid these problems, but if you are manually typing in a record name you may have no choice.
You may also need to use conditional actions to account for the situation where the group being added is the first group and there were no previous groups. In this case there should be no comma.
Finally, you may also run into a problem if you try to add someone to a group who is already in the group. In this case the group name would appear twice. However a linked record field can only link to a given record once. If the record appears twice in the list, this will cause an error. (At least it caused an error the last time I tested it.)
No. The people are not temporary taken out of any groups with this method. The original list of groups is simply replaced with the new list of groups.
There are some aspects where you need to be careful. You should make sure that your group names are 100% unique and do not contain any commas, quotes, or new line characters. (Other special characters might also cause problems.) I tend to prefer using record IDs in these cases to avoid these problems, but if you are manually typing in a record name you may have no choice.
You may also need to use conditional actions to account for the situation where the group being added is the first group and there were no previous groups. In this case there should be no comma.
Finally, you may also run into a problem if you try to add someone to a group who is already in the group. In this case the group name would appear twice. However a linked record field can only link to a given record once. If the record appears twice in the list, this will cause an error. (At least it caused an error the last time I tested it.)
I think the workaround to this (instead of using a conditional action) is to put the new values first, then add the comma, then insert the field itself. This should work all the time, even if the field was previously empty.
Super helpful everyone, thank you!! I’m running some tests in our test base now and will see which approach works best.
Email sent to Airtable support for good measure in the append function.
Austin_Adams:
Does Airtable see this as taking them out of the group/record and then suddenly putting them back in?
No. The people are not temporary taken out of any groups with this method. The original list of groups is simply replaced with the new list of groups.
There are some aspects where you need to be careful. You should make sure that your group names are 100% unique and do not contain any commas, quotes, or new line characters. (Other special characters might also cause problems.) I tend to prefer using record IDs in these cases to avoid these problems, but if you are manually typing in a record name you may have no choice.
You may also need to use conditional actions to account for the situation where the group being added is the first group and there were no previous groups. In this case there should be no comma.
Finally, you may also run into a problem if you try to add someone to a group who is already in the group. In this case the group name would appear twice. However a linked record field can only link to a given record once. If the record appears twice in the list, this will cause an error. (At least it caused an error the last time I tested it.)
kuovonne:
You may also need to use conditional actions to account for the situation where the group being added is the first group and there were no previous groups. In this case there should be no comma.
I think the workaround to this (instead of using a conditional action) is to put the new values first, then add the comma, then insert the field itself. This should work all the time, even if the field was previously empty.
Hmm.. It appears that this comma-separated method may not work if you're trying to append a list, rather than a single record, say, the results from a “Find records” action joined with the original list of linked records.
In my case, I'm trying to merge Timesheet Entries coming from multiple Task records and link them all to a single Invoice Line Item record. My setup may admittedly be more complicated than it needs to be — I'm still iterating — but the principle should work, in theory.
The value I'm trying to add to the linked record field in the Invoice Line Item is:
`List of Airtable record ID's (Find action results), Same Linked Record field (another list of record ID's)`
The error I keep getting is that the linked record field “cannot accept the provided value: Could not find matching rows for string.” And the output value shows a lone comma at the start of the field (from an empty Find result), followed by the comma-separated list of record ID’s that were already present.
Unless I'm missing something, this suggests that, as @kuovonne stated, there may need to be a conditional step in here to handle empty fields. Unfortunately in this case, you'd need both Repeat and Conditional logic blocks — to loop through each Task and get its time entries, then for each resulting list, if it's empty, ignore, but otherwise append — and Airtable doesn't currently allow you to do both using the drag-and-drop automation editor.
Either way, this is so much easier to conceive and execute using a “Run script” action, where you can loop through all the records and create a clean list (and because Airtable actually provides support documentation for appending records to linked record fields using scripts), so unfortunately I'm giving up on this for now. If anyone has any ideas about what might be wrong in my approach, or if you’ve successfully merged two lists of records into a single linked record field using an automation, please share!
I do a combination of both, kinda. I use a Run a Script action to handle merging the array properly and then feed that into the Repeating Group