How to update record in another table automatically

[Louise_Parker]

Hi!
I’m building out a base where I have tables for different types of contacts (lets call them blue contacts, red contacts and green contacts), they all feed into a larger table where I can see all of them (rainbow contacts list) - I have set up automations to automatically create a new record in the rainbow contacts sheet when a new record is created in any of the other tables. So I put Joe Bloggs into green contacts and he automatically appears in the rainbow list.
I want to setup an automation so that if I update the Joe Bloggs record in green contacts, it automatically updates in the rainbow list. Can anyone help.
I have tried setting up an automation, it watches all the fields in the green contacts and then I tried update record in rainbow list - I’ve tried a few combinations of things and can’t get it to update in the rainbow list - does anyone have any advice? Do I need to find a record first?

[ScottWorld]

Welcome to the community, @Louise_Parker!

It would typically be best for you to keep all of your contacts in the same table, and create different filtered views to represent the different types of contacts.

I discuss more about views in my free Airtable training course:

LinkedIn

Learning Airtable Online Class | LinkedIn Learning, formerly Lynda.com

Learn the basics of Airtable, a spreadsheet-database platform, including how to create tables, use different types of fields, leverage views, work with records, link tables, and more.

[Louise_Parker]

Hi Scott,
Thanks for your reply - I had given that some thought but my base is actually more complex - so each table (green, blue, red) have different uses - one feeds into some business development tables, one is for PR and the last of for something else - the workflow is: fill in the PR contact in the blue table and that automatically creates a record in the rainbow list (using selected fields). What I would like to happen is if I update that PR contact in tue blue table that it gets automatically updated in the rainbow list.

[TheTimeSavingCo]

Hmm, if you’re looking to accomplish this via automations, I think you could try the following:

1. In each contact sheet, add a new column called “Rainbow Contacts Record ID” or some such (you’ll understand why later)
2. In the automation that creates a record in the Rainbow Contacts sheet, update it so that it also adds the record ID of the new record in Rainbow Contacts, into the “Rainbow Contacts Record ID” column we created in the previous step for each contact sheet (For example, let’s say you just created a record in Green Contacts. Your automation creates a record in Rainbow Contacts, and the ID of this newly created record in the Rainbow Contacts sheet is “recQcfyaSahTeNdit”. Make the automation put that ID into the “Rainbow Contacts Record ID” column in the Green Contacts sheet)
3. In the automation that updates the record in Rainbow Contacts, use the value of “Rainbow Contacts Record ID” to update the correct Rainbow Contacts record.

I’ve created a working version here. To view it, duplicate the base into your own workspace.

---

I understand you have a need to keep the contact sheets separate. May I know what issues you faced when attempting to use linked records and lookups though?

They seem like the perfect solution in this situation, allowing you to keep said contact sheets separate while also having Rainbow Contacts populated and kept up to date automatically. I’d be happy to see how I could help you with that as, while the automation I suggested is what you’re looking for, feels overly complicated for this situation to me

[Louise_Parker]

Hi @Adam_TheTimeSavingCo thanks for this suggestion, I’m going to give it a go!
I didn’t want to use linked records and the lookup because I also manually add people who don’t fit into any of the other sheets to the Rainbow list e.g. George who’s tag is polka dot, I want to keep a record of having met him or for future reference but for now he doesn’t fit in the green, red or blue sheet. Having said that, now I’m writing this I realise I’ve got two choices - your suggestion or just another table that I use for everyone that doesn’t fit in green red and blue. I will test which is more elegant.

[TheTimeSavingCo]

Glad I could help!

Ah yes, having another table for everyone that doesn’t fit would work, we would just have to make an automation to handle transferring George from polka dot to green, or whereever else later; seems easy enough.

---

I’d be interested in attempting to help create a system that allows you to compile all your tables into one single contacts table, so let me know if you’re ever keen on doing that

[Louise_Parker]

For sure, do you have an email address? It will probably be much easier for me to explain what I actually need by sharing my base - I still haven’t fixed the problem of updating so I would be happy for a second set of eyes

[TheTimeSavingCo]

Sounds good, I’ll dm you