Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Sep 01, 2020 05:21 PM
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?
Sep 01, 2020 09:23 PM
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.
Sep 02, 2020 12:57 AM
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]
Nov 30, 2020 04:36 PM
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.
Nov 30, 2020 04:45 PM
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.
In table B, create a formula field with the function RECORD_ID()
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.
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.
Nov 30, 2020 05:00 PM
A superior answer as always! Thank you for filling in my mistake! :grinning_face_with_big_eyes:
Nov 30, 2020 05:32 PM
@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.
Dec 23, 2020 11:41 AM
This is exactly what i was looking for, thankyou for the solution @kuovonne!
Mar 06, 2021 04:43 PM
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?
Mar 12, 2021 11:27 AM
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