Help

Automation Assistance - Linked Records

Topic Labels: Scripting extentions
4521 10
cancel
Showing results for 
Search instead for 
Did you mean: 
Michael_Andrew
7 - App Architect
7 - App Architect

Hi I am trying to build an automation. When a record enters a view, I want to update another table for the linked record. For example:
In Table A there is linked record to Table B. In Table A there is a field Field A. When a record enters a view in Table A, I would like to update Field B in Table B for the linked record with the data in Field A. I think I need to run a script to get the record id for the linked record because you can’t select a linked record as the Record ID in the Update Record function in an automation. I tried to just ‘Lookup’ the record ID of the linked record, but this does not seek to work either. I could use some help to get the record id of the linked record.

Thoughts/assistance?

10 Replies 10
Paul_Warren
8 - Airtable Astronomer
8 - Airtable Astronomer

This wont get you all the way there, but you can use a FORMULA of the linked record ID.

Table A has a field “recordID” which is the following formula:
RECORDID()

Table B is linked to records in Table A.

  • Table B has a field “linkedRecordID” that performs a Lookup of “recordID” from Table A

  • Table B has another field “linkedRecordIDString” which is the following formula

    linkedRecordID

Now you can use the field linkedRecordIDString in your automation. I hope that makes sense.

In all truth, the automations really restrict what you can use, but provide for wonky workarounds. I hope Airtable just makes these other fields easier to work with.

The value of a Record Link field is returned as an array of objects (even if only one record is linked):

[
    {id: 'recXXXXX', name: 'Record A'}, 
    {id: 'recYYYYY', name: 'Record B'}
]

So if you want the record IDs out of that array you could do: (assuming you’ve already defined values to be the value of the intended field)

let ids = values.map(x => x.id)

If you want a singular ID, assuming that ID is the first in the array:
let id = ids[0]

If there is only one linked record, an update action can get the id of that linked record without resorting to scripting. In the “Update record” action, select [Table B] as the table containing the record. Then for the record Id, select the triggering record, then the linked record field.

This method will only work if there is only one linked record. If there are multiple linked records, you will have to resort to scripting or use another method of deciding which record to update.

I think you have the tables flip-flopped. Table A holds the triggering record, thus you need to get the record id of the record in table B.

  1. In table B, create a formula field with the function RECORD_ID()

  2. In table A, use a rollup of the record id field for the linked record with the formula ARRAYJOIN(values). A rollup field will return a string, while a lookup field returns an array and would need another formula field to convert the array to a string.

  3. In the automation, use the rollup field to get the record id.

However, this system will also work only if there is only one linked record. Thus, you might as well get the record id directly from the linked record as described in my previous post. If you have other logic that you want to use to pick only one of multiple linked records (without resorting to a script), that could be possible with this rollup method.

A superior answer as always! Thank you for filling in my mistake! :grinning_face_with_big_eyes:

@Paul_Warren Thanks, but you get credit for a correct, timely reply. I didn’t realize that this thread is almost three months old when I replied. It just showed up in my feed and I answered without realizing how old it was. Maybe these answers will help someone else.

This is exactly what i was looking for, thankyou for the solution @kuovonne!

medairtable
4 - Data Explorer
4 - Data Explorer

Hey Kuvonne,

If the trigger is a new created record in table Awith a field called {stage}, single select (Calls, and No Show).
To fined the related record in table B we use the find record and match the record ID string.
The goal is to update the {Sales Stage} of table B by the same value in {Stage} of table A.

For some reason the automation is not working.
Any idea?

medairtable
4 - Data Explorer
4 - Data Explorer

For some reason it just worked. All what i did is create a record ID for Leads table, and rolled up a function in Array in the connected table and for some reason it worked. Now i cant seem to replicate in another table lol

automadien
6 - Interface Innovator
6 - Interface Innovator

Hi! I’m trying to replicate this solution but am getting a test error come back saying it has received invalid inputs. I can’t see how/why as the ID string is showing and is only linked to one record?

Thanks!
Josie