Update record action failing (error: received invalid inputs.)

[Brian_Nana-Sink]

Could really use some quick help from the community here!
Don’t be frightened by the length of this ticket. We think there is probably a very simple answer here that we aren’t seeing. Fairly urgent as this automation is critical to the business.

Our team has created an automation meant to update records in a table called UNIFIED WORKSPACE DATA when a record is updated in a different table called WORKSPACE PRODUCT DATA. The automation watches a field called “stripe_customer_id” (type: single line text) in table WORKSPACE PRODUCT DATA – when this field is updated for any given record in the table (this is the trigger), the automation then updates a field in the UNIFIED WORKSPACE DATA table called “CUSTOMER ID” (type: Link to another field) with the “stripe_customer_id” that was updated in the WORKSPACE PRODUCT DATA table (this is the action). The Record ID for the action is found in a field called “workspace_id” (type: single line text) in the WORKSPACE PRODUCT DATA table.

The reason we’re running this automation is so that the “CUSTOMER ID” field is automatically updated when the “stripe_customer_id” field is updated. The “CUSTOMER_ID” field is a field type “Link to another record”, so a record in this field is updated via the automation, it will populate a number of lookup fields with values from a third table called WORKSPACE STRIPE DATA. This link + lookup to the third field shouldn’t affect the automation at all, but maybe it is helpful context.

The issue we’re seeing is during the testing of our automation action. Testing the trigger works perfectly (image attached) but when we test the action, the test fails and we receive an error message: “Received invalid inputs.” (Image attached) We don’t understand why the test is failing which of our inputs could be invalid. Could this be related to the Record ID that we’ve chosen in the action step? The Record ID we’ve chosen (“workspace_id”) exists in both tables, so there shouldn’t be an issue finding the record to be updated, correct? Maybe it is because the field type of “CUSTOMER ID” is different than that of the the field that’s being used to update it?

It’s worth noting that we created an almost identical automation that creates a record in UNIFIED WORKSPACE DATA when a record is created in WORKSPACE PRODUCT DATA and this automation performs as expected!

Could you PLEASE help us to understand why this automation test is failing (and subsequently why the automation won’t work, as we have tried running it despite the failed test, to no avail)?

I’ve included screenshots of the automation setup, the successful trigger test, the failed action test, the WORKSPACE PRODUCT DATA table and the UNIFIED WORKSPACE DATA table.

To summarize:

Trigger : “stripe_customer_id” field of a record in WORKSPACE PRODUCT DATA table is updated

Action : “CUSTOMER_ID” field of a record in UNIFIED WORKSPACE DATA table is updated with the “stripe_customer_id” value from WORKSPACE PRODUCT DATA table. A field in WORKSPACE PRODUCT DATA table called “workspace_id” is used as the Record ID to find and update the correct record in UNIFIED WORKSPACE DATA table.

Problem : Action test fails with error message “Received invalid inputs” and automation does not perform as expected.

[ScottWorld]

You can only update records based on their RECORD_ID, but you’re not allowed to create your own Record ID’s in Airtable.

If you want to see a record’s Record ID in Airtable, then you need to create a formula field with this formula:

RECORD_ID()

That will reveal the record’s Record ID to you.

If you need to search on other fields to find the record you’re looking for, then you can’t use the built-in actions of Airtable’s automations.

You would either need to:

1. Write a custom JavaScript script to do this.

or

2. Use a no-code automation platform like Integromat. If you don’t want to dive into JavaScript code, then Integromat is my favorite way to go.

If you have a budget for this project and need someone to set all of this up for you, I am an expert Airtable Consultant and a Registered Integromat Partner, so feel free to reach out to me through my website at scottworld.com.

[Brian_Nana-Sink]

Hi Scott - thanks for getting back to me so quickly!

I understand the issue now. I’m passing a unique id that is not a RECORD_ID in the “Record ID” field of my action step step. It’s unfortunate that Airtable doesn’t allow you to update a record based on a value other than RECORD_ID. It would be great to use a value from the table in my trigger step to find and update a record in the table of my action step.

I see that someone else ran into the same issue here and community member Kamille_Parks suggested that they run a script, not unlike your 1st suggestion. Would you mind letting me know if the script they’ve suggested seems like a viable solution?

[ScottWorld]

I’m still learning JavaScript myself, but @Kamille_Parks is one of our resident JavaScript experts, so I’m sure her script is perfect!

[Kamille_Parks · Accepted solution]

ScottWorld:

@Kamille_Parks is one of our resident JavaScript experts

not even sure how that happened, but here I am lol.

@Brian_Nana-Sinkam I do believe that same script will work for you. That being said, it does look like your PRODUCT and UNIFIED tables are linked together, so in theory you should be able to run this automation using just the UNIFIED table. You have a lookup field pulling in the {stripe_customerIid} field into the UNIFIED table, so couldn’t you run the automation once the lookup field’s value changes and have the Update Record step copy the value of the lookup to the link field?

If that isn’t the case, then here’s an adapted set of instructions for using a script to link the appropriate customer:

1. Add a Script Action step above your Update Record Action step.
2. In your script action step define an input variable with the name workspace and use the {workspace_id} field value from the trigger record as the value for the input.
3. Add the following script:

const inputConfig = input.config()
const workspace_id = inputConfig.workspace
const table = base.getTable("UNIFIED WORKSPACE DATA")
const query = await table.selectRecorsAsync()
const records = query.records

const matchesFound = records.filter(x => {
    return x.getCellValue("workspace_id") == workspace_id
})

output.set('recordID', matchesFound[0].id)

4. In your Update Record step use the output from your script step in the “Record ID” boc.

[Brian_Nana-Sink]

Thanks @ScottWorld ! Really appreciate your help here :slightly_smiling_face:

Hi @Kamille_Parks! I had gone ahead and tested the script that you had suggested in the other Topic with our own base information, just as you’ve detailed below, and it worked! That said, I see what you mean about only needing to run the automation on the UNIFIED table and without the script – that seems like the cleaner option. When I test this out, I’m unable to select the lookup field {stripe_customer_id} in the Update Record step. “This property cannot be used right now” is the message I receive. Any idea why that might be?

Would love to go with the script-less option but in any case, it’s working and I so appreciate your help thinking through this!

[Kamille_Parks]

Lookup fields aren’t yet supported by automations, but the good news is you should be able to convert that field to a Rollup using CONCATENATE(values)

[Brian_Nana-Sink]

:bulb: That worked! Thanks so much for your help @Kamille_Parks.