How to add a linked record to multiple records without overwriting the existing data

[Gargazaur]

Hi Airtable Community!

Problem: I'm having trouble adding a linked record to multiple records without erasing the data that is already in the cell.

Context

I've got one table of studies, and another table of people. Each person can be linked to any number of studies in a "Contacted" field. Sometimes that field is empty, if that person hasn't been contacted yet, and sometimes it has a few linked records (studies) in there already.

If I contact 50 people, I want to add the study I contacted them for to their record. If I copy/paste the study to the cell, this overwrites the existing data , so if there were 3 studies in there to start, I'll erase them if I paste in the new study.

What I tried

I made a field that concatenates the study name I'm adding with the study names already in the "contacted" field, and it comes out as text. If I then paste the text into the "contacted" field, voila, I have all of the records, or so I thought! Turns out this just makes NEW duplicate records of all of the studies I pasted and that's what is added to the peoples' records. I then have to go delete all of the duplicate, empty study records and I'm back to where I started.

My other option is to add the study to each of the 50 records one by one... which is what I've been doing. But it's not great.

So for my first question to this lovely forum is how to do better than that. How can I quickly add a study record to a ton of people's records at once without overwriting the existing records?

[Dan_Montoya · Accepted solution]

hi @Gargazaur ,  I would use an automation.  

fields:  linked records of studies, linked record of new study to add.

Automation:  When field new study to add is updated, update the same record using the old values of linked records of studies and the new linked record of studies

https://airtable.com/appP32e2NuQS9wwa0/shr8l4rhrBmTytFNs

[Gargazaur]

Hi Dan,

Thank you for the reply! I'm going to go try this out right now and let you know if it works out!

I actually have a few fields that would use this same workflow, as we track "Reserved for study" which moves into "Contacted" which then goes to "Participated". So I think I can make this same automation twice to move the data twice? And the "new study to add" field is meant to be cleared out each time with no consequences, since it being updated to blank would trigger the automation to update to the old values + nothing, so no change! Am I understanding that right?

Very excited to try this out, will update to "Accept as Solution" as soon as I can confirm.

Update: Works great! I even added a step to auto-clear out the "New study added" field right after so I don't have to do that either. 🙂

Thanks again, you are very appreciated!

[Dan_Montoya]

There should really be an "IF" statement that if it isn't empty use the 2 values with the comma.  If it is empty only use the new value.  That way you don't end up with random commas.  (Not sure you would but it is a more defensive way to code it.)

[Dan_Montoya]

You should be able to do that with one automation by making the "new study" field empty in the update step.