Help

Update Record step to append a record into a list of linked records rather than overwriting existing linked records

Topic Labels: Automations
5347 14
cancel
Showing results for 
Search instead for 
Did you mean: 
Anthony_Fennell
4 - Data Explorer
4 - Data Explorer

Hi,

I’m trying to use an automated Update Record step to add a new linked record into an existing list of linked records.

Unfortunately I’m finding that when the automation runs, it simply overwrites the field with the new record rather than adding the new record into the existing list.

Is there some way for the automated Update Record step to append a record into an existing collection of linked records?

Thanks very much.

14 Replies 14

Welcome to the community, @Anthony_Fennell!

I haven’t tested this myself yet, but I believe that you might be able to append an additional linked record by (1) inserting the original linked record field from the trigger record, (2) adding a comma, then (3) adding whatever is supposed to be appended.

Please try this and let me know if it works!

It works! Brilliant, ScottWorld. I could kiss you!

Ha, that’s great! Glad I could help! :slightly_smiling_face: If you don’t mind, could you please mark my comment above as the solution to your question? This will help other people who have a similar question in the future. :slightly_smiling_face:

While it was my question, I wasn’t the one who posed it, so I think @Anthony_Fennell is the one who has to do that.

Hey, @Anthony_Fennell, welcome to the community, and thanks for asking that question!

Oh, hahaha! Thanks for the clarification! :rofl: :joy: :grinning_face_with_sweat:

Eli_Kent
7 - App Architect
7 - App Architect

Though I had to make some changes to get around the fact that Airtable saves a linked record as the record ID and not the string that is seen, and therefore the automation was failing.

At first I created a formula field that was simply a copy of the linked record field I wanted to add a record to - in my case {All Employers}. In the automation I did as you said, but the automation was failing I think b/c AT failed to match the comma to a record in the linked table (screen shot of error message below).

So I changed my formula field to include the field for the linked record I wanted to add - in my case {Temp Employers}. At first, I made the formula {All Employers}&’,’&{Temp Employers}, but the comma was giving AT automation problems in the cases where {All Employers} was empty. I also realized that the automation broke down if the linked record in {Temp Employers} already existed in {All Employers}. My formula ended up as

IF({All Employers},IF(FIND({Temp Employer},{All Employers})>0,{All Employers},{All Employers}&’,’&{Temp Employer}),{Temp Employer})

Below are screen shots of before and after, and below that the screen shot of the error message I was getting because of the comma, I think.

image

image
image

With Airtable’s automations, you can actually use names instead of record ID’s in your linked record field.

A linked field is an array, so you might be able to make your formula work by using one of Airtable’s Array functions.

For example:

ARRAYFLATTEN({Your Linked Field Name Here} & "," & "New Linked Record Name Here")

That will create a single array (instead of a nested array) of all your existing linked record names, plus whatever the name of your new linked record is going to be.

Hmmmm… I’ve played around a little bit with the various array functions, and tbh, i can’t make out the theoretical difference between join and flatten. i see they do different things, but quite often it’s not what i expect them to do.

it looks like your solution is similar to mine, in that we both create a formula field. i’ll see how your formula works compared to the one i came up with, which, btw, i’m about to edit, b/c i came across another hiccup, which is that the automation fails if the linked record that you want to add to the linked field is already linked to. Below is the formula I wrote to solve that issue:

IF({All Employers},IF(FIND({Temp Employer},{All Employers})>0,{All Employers},{All Employers}&’,’&{Temp Employer}),{Temp Employer})

Meredith_Whitf1
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi there! I’m trying to do something similar and am not as AT-versed as y’all. Would someone mind to help?

I have a list of social media posts and I am trying to build automations to build lists of themes (which I have as linked records) based on keywords in the posts, but my automations overwrite my list of themes rather than append to my list.

Below are screenshots of one of my automations, about food insecurity.

Screen Shot 2020-11-02 at 4.15.28 PM Screen Shot 2020-11-02 at 4.15.20 PM

Pretend this post only had the UN/International theme there already, but I wanted to have my automation append the “Food insecurity” theme.

Screen Shot 2020-11-02 at 4.17.58 PM

Would I need to build a formula for my linked themes like @Eli_Kent did?

Eli_Kent
7 - App Architect
7 - App Architect

Hey @Meredith_Whitfield,

The solution I outlined above has been working perfectly for me thus far. There might be a more elegant solution :man_shrugging:

Anyway, if you have any questions about applying it, let me know. Happy to help.

Hey @Eli_Kent, would love your help! I think I might need a little step by step guidance, if you don’t mind to spend the time.

Eli_Kent
7 - App Architect
7 - App Architect

Hi Meredith,

No problem. I’m happy to have any excuse to procrastinate in learning code lol.

For real, I am a bit unclear about your context. How is the {Post text} field getting populated? Are you asking the automation to search for multiple issues in the same post? Under what circumstances would a post be linked to more than one issue? Are the records linked to going to change for any particular record in [All Posts], or is this a set-it-and-forget it type thing?

I’ve created a mock-up base for your review. There may need to be some changes made based on the answers to the questions above.

The [All Posts] table contains the following fields:

1- {Issue placeholder}: a linked records field that links to [Issues] table and does not allow for linking to multiple records.

2-{All issues for automation}: a formula field that asks two questions. 1. is there content in {All issues} and 2. does the content in {Issues placeholder} already exist in {All issues}. This formula combines all unique records in {All issues} + {Issue placeholder} to create a string that, through an automation, replaces whatever is in {All issues}.

3-{All issues}: A linked records field that links to [Issues] table and allows for linking to multiple records. This is the end product.

As you can see, when the automation finds “food insecurity” in {Post text}, in Step 1, it links to “Food insecurity” from the [Issues] table in the {Issue placeholder} field (overriding whatever was in {Issue placeholder} previously). In step 2, the automation copies/pastes {All issues for automation} into {All issues}. If you wanted, you could add a third step to the automation that would clear {Issue placeholder} after running the first two steps. It’s unnecessary, but might be a bit cleaner if you’re manually interacting with any of those fields.

Again, the solution you need might be a bit different based on your particular requirements. Also, FWIW, I’m pretty new to programming, so, as I said before, totally possible that there are more elegant solutions than this one.

Let me know if you have any questions.

Finally, here’s a diagram of what {All issues for automation} is doing, in case you want to see.
image

Hey @Eli_Kent, sorry it took me so long to respond! Would you be interested in working a few contract hours for my consultancy to help me get this set up, and help me with another automation issue? If you’re interested, shoot me an email at meredith@conspireforgood.com.

MW

Jake_Singer
4 - Data Explorer
4 - Data Explorer

Another solution: set the automation to set the link the other way!