Automations: Update record when google sheets row is created based on REF

[sam-lsf-lst]

I have set up an automation:

Trigger> Google sheets: when a row is created

Action 1> find records where REF is [value from google sheet column with REF #s, which should match airtable REF values]

Action 2> update record

Action 2 is giving me trouble. I have 2 fields I want to update (a checkbox and a currency field).

When a row is created on the google sheet, for example with airtable REF #AB-0101 in the google sheets "airtable ref #" column, I want airtable to find record #AB-0101 and update that record's checkbox and currency field. 

I swear I had it working last week, but today's test failed due to "invalid inputs." 

I don't really understand how the "Record ID" part of the automation setup works for Action 2 (update record). 

I did blue plus sign > Use data from... find records step > ...... then I'm lost! I've been digging around trying to find an explanation for the different options here (Insert all records as list vs grid, vs Make a new list of...) but not having much luck. 

Can someone shed light on how these different "Record ID" options work and/or which one I need?

[TheTimeSavingCo · Accepted solution]

re: I swear I had it working last week, but today's test failed due to "invalid inputs." 

Hmm, looking at your second screenshot, the "Length" value of the "Find records" step is 2, which implies that there are two records with that REF, which is probably causing this test failure.  Could you check to see whether there are any duplicate REF's inside your table?

---
For your use case, you'll need to select the IDs of the records found in the 'Find Records' step, and you can do it like so:

Here's a guide by Airtable that shows how and what kind of data to input into the Record ID part of the automation:
https://support.airtable.com/docs/linking-existing-records-using-automations

[ScottWorld]

@sam-lsf-lst 

When you use the Update Record action, you can only update one record ID at a time.

If you are expecting more than one record, then you need to use a repeating action to loop through the found records so you can update each one individually.

[sam-lsf-lst]

Thanks ScottWorld! I only need to update one record at a time - when a new row is created in my google sheet, only one record needs to be updated based on that trigger. So am I ok?

[sam-lsf-lst]

Thank you TimeSavingsCo! I had some duplicates hiding in the background that I wasn't aware of. That seems to have fixed the issue! Much appreciated

[ScottWorld]

Yes, you should be okay if you don’t have duplicates in your Airtable base.

However, based on your screenshots above, it shows that you are finding more than one record as a result of your search, so that indicates that you have duplicates in your base.

If this will always be the case, then you will need to add a repeating loop into your automation so that Airtable can update ALL of the records that it found.

But this is all-or-nothing with Airtable. You can’t tell it to just update the first record that it found out of a batch of found records.

If you need more fine-tuned control over this, such as updating only the first record that it finds, remember that you can always turn to Make’s Airtable automations & integrations, which lets you monitor Google Sheets, and then search & update Airtable records based on new rows created… but it also lets you limit your search to a certain number of records that you want it to find (such as only finding 1 record). And this can be based on a custom sort that you specify.

p.s. If you have a budget for your project and you’d like to hire an expert Airtable consultant to help you with any of this, please feel free to contact me through my website: Airtable consulting — ScottWorld