Updating Multiple Records with Script/Automation

Hey Wendy, looks like the automation’s breaking as it’s trying to update two records at once.

With the way I’m guessing your base is set up, you’re going to need to use a script for this. If you’re not comfortable maintaining a script, we might want to consider rearranging the table / changing up the workflows a bit

If you could share a couple of screenshots of the relevant fields and tables of your base, or better yet, a read only duplicate of your base with sensitive data removed, that would be extremely helpful for anyone trying to provide advice

Hi @Adam_C , thanks for your quick reply!

Here is a screenshot of what my Parent table looks like:

Here is the form that reflects to the Transaction table:

Here is the Transaction table:

I also did create an automation that works, however even with AT’s increase in automations to 50, I may still end up with over 50 items in the future, so this may be a band-aid fix and will have to go back and be reconfigured as our inventory grows:

The updated record field input is just the record ID on the parent table.
I would have to create this automation per inventory item, which will max out at 50 automations, making in unsustainable long-term.

I’m comfortable with use/editing of scripts, but I need the jumpstart + a little explanation.

Thanks!

Hey Wendy, understood, thank you for the details!

I’ve thrown something together for you here and you should be able to see the script in the automation

Let me know if you’ve got any specific questions about the script!

Hi Adam,

How do you delay the automation run for few secs? I’ve always had this issue where the automation doesn’t run properly due to certain column has not been populated.

Thanks !

Hey Hendrik, to do this I usually have another formula field that checks if NOW() is later than the created time of the record + 5 minutes or something. This is for situations where any of the fields involved might require multiple updates (For example, when adding line items to an invoice)

In a situation where none of the fields involved require multiple updates, I use the “When record matches conditions”, and make it only trigger when all of the fields the automation needs are filled

Ah interesting! Thanks for the tips.

Is this because you are in a free workspace, or a different reason? If you are in a free workspace, you can only run button scripts, not automation scripts. Also, in order to update multiple records in an automation without scripting, you typically need to run the automation once per record, and you may not have enough automation runs in a free workspace.

@kuovonne I have an enterprise account, but our AT admin has disabled company use of non-AT apps & I had to get special permissions to use the script function.

I have an example of a script that introduces a pause in an automation in my set of Automation Helpers in my Gumroad store.

Keep in mind that this introduces a pause, but does not include a fresh read of the values in triggering record. You may need to do a fresh read of the record (either in a script or using a “find records” action) in order to get values that have changed after the automation was triggered.

Using NOW() does work, but it has some drawbacks.

• NOW() only updates at best every few minutes, and at worst not for a few hours (if the base is closed).
• Heavy use of NOW() can slow down your base.

Thanks for the clarification. Usually people who can access scripts but not apps are on free plans. Glad you got special permission to use scripts! Scripts are fun and very powerful.

@Adam_TheTimeSavingCo I essentially copy/pasted your script and adjusted my table names to match yours in the example base.

Here are the error messages I am receiving:

Wendy_French wrote:

@Adam_TheTimeSavingCo I essentially copy/pasted your script and adjusted my table names to match yours in the example base.

Here are the error messages I am receiving:

Hey Wendy, hm, could I confirm that the single select option you’re trying to update to already exists in the ‘Status’ field in Traps?

I usually see the error message you described when the option field I’m updating doesn’t already have the option I’m trying to update to.

For example, if you’re updating the Status field to “In the Field”, but your Status field does not have the option, or has the option “In the field” (lowercase F), that might be causing this error

Oh man, thanks for this. I’m going to need to start assuming the automations end up running a couple of hours later and see whether the use cases still fit, and also will need to start considering how many of these exist in the base

Hi @Adam_TheTimeSavingCo, here are the options in the Traps table:

And here are the corresponding options in the transactions table:

I updated the “in the field” to a capital F to match the Traps table format, but still threw this error:

Hm this is puzzling. Could you duplicate your base, clear out all your records, and then send me an invite please? I’ll try debugging it from there

Looks like there are more upper/lower case issues to match up.
Also the script uses an “Error” status that doesn’t exist.

Debugging issues like this is usually is easier if you have liberal use of console.log to see what your actual values are.

@Adam_TheTimeSavingCo Here is the link!

Hi Wendy, @kuovonne hit the nail on the head here

The script expects the options in the Transactions table to be of the format:

• Checked in
  • Corresponding line: case "Checked in":
• Checked out
  • Corresponding line: case "Checked out":

However, the options in the Transactions table are currently in the format:

• Checked In
• Checked Out

Note “in” vs “In” and “out” vs “Out”

So we should try either updating the script or updating the options in the Transactions table, and then running the script again

The script also expects an Error option in the Status field of the Traps table. I included this (and still recommend it) as, in a situation where we changed or added options to the Status field of the Transactions table, the script would then use said Error option, which would alert us to the fact that something was not working as intended.

And so to fix this, we should add an Error option to the Status field of the Traps table

Woohoo! It works! Thank you so much!

Hey :slightly_smiling_face:
Thank you for this script you’ve put out that matches perfectly one on my need. However, I have this error “Error: Request parameters failed validation.”, right on the while, any idea what could it be ?

while (updates.length > 0) {
    await table.updateRecordsAsync(updates.slice(0, 50));
    updates = updates.slice(50);
    console.log(updates)
}

I checked my table permission, profile permission, it doesn’t seem to be that :frowning:
Thanks !

while (updates.length > 0) {
    await table.updateRecordsAsync(updates.slice(0, 50));
    updates = updates.slice(50);
    console.log(updates)
}

Hi @Floriane_Hibelot, could you invite me to your base so that I can try to troubleshoot it from there please? Thanks

Hi, thanks for the quick reply ! I unfortunately can’t invite you but here is the automation

My code : `
let inputConfig = input.config()
let quoteStatus = inputConfig.quote_status
let requests = inputConfig.query

let statusToUpdateTo;
switch (quoteStatus) {
case “accepted”:
statusToUpdateTo = “To be ordered”
break;
default:
statusToUpdateTo = “Error”
}

// let updates = new Array
let updates = requests.map(x => ({
id: x,
fields: {
“Status update”: {name: statusToUpdateTo}
}
}))

console.log(updates)
let table = base.getTable(‘Requests’)
console.log(table);

while (updates.length > 0) {
await table.updateRecordsAsync(updates.slice(0, 50));
updates = updates.slice(50);
}

console.log(updates)`

I am really keen to explore some option if you have any idea, I already checked internet so much :grinning_face_with_smiling_eyes:

When updating a record, you need to use the internal record ID that starts with rec followed by 14 alphanumeric characters.

It looks like you are using a number from the input variable instead of the record ID. Try changing the input variable to use the record ID instead of the name of the linked variable.

That was it ! Can’t believe I didn’t think of that, thank you soooooo so much !