Automation using Update Record not working

[Andrea_Maney]

I’m trying to use an automation to look for a record in one table to update a record on another table.

1. I’ve created a form that I’d like my team to use to update students testing status (Yes/No)
2. I tried to set up the automation with a Trigger on a new record created on the Test Update table
3. I’d like to use the student name on this new record from the Test Update table and look up their info on a separate table to update their Test Status. It looks very easy to set up but I must be missing something obvious because I can’t get this to work. The trigger appears to be working, but in the update step, I get an “invalid input” error.
   Attached screenshots of how I set this up and the error info.

[ScottWorld]

You have to choose the Record ID for where it asks for Record ID.

[Andrea_Maney]

Thank you for the reply!
I’ve probably tried this a million different ways lol. I set the Record ID to Record ID and now it says “record does not exist”

Here’s the data in the table that the form will populate

I’m trying to set the automation to use 'Name" field from the Test Update table in the above image and find that name in another table (for this test, I created “Test 2” and update the Test Status field on that table.

Here’s the error I’m getting now.

[ScottWorld · Accepted solution]

You can’t do that with automations unless:

(1) you link your tables
or
(2) you write a custom JavaScript script to do this
or
(3) you use a no-code/low-code automation platform like Integromat.

Automations can’t search for something in a table, such as someone’s name. Automations can only specify records by their Record ID.

You might be confused because you’ve never seen the Record ID before. You can see it by creating a formula field and setting it to this function: RECORD_ID(). That will show you the record ID for each record.

If you link your tables, Airtable actually stores the Record ID in the linked record field (even though you visually see the text of the primary field). So you can use an automation to grab the Record ID from the linked record field and then use that same Record ID in the other table.

BTW: One very important thing to note is that this technique will only work if you have ONE linked record in your linked record field. If you have multiple linked records in your linked record field, you’ll have to use another method of doing this (see options #2 and #3 above).

[Andrea_Maney]

Thank you again for all the great advice! You are correct that I was confusing the Record ID and the Primary Field. Thanks for explaining! after reading through this, I think I can simplify what I want to do in another keeping the test results & test status on the same table & form and just add a record type field. Then I can do a rollup to find the latest test result. Unfortunately I don’t know JavaScript and trying to stay away from needing to learn another platform. Thanks again for all your help. Much appreciated!