Updating Multiple Records with Script/Automation

[Wendy_French]

I have inventory that is permanent and gets checked in and out. I have created a form that includes the status change, the inventory ID, and the name of the person who checked it out. We often check out 10+ items at one time, making them all part of 1 transaction.

The form form has a multiple select option for choosing every item they are checking out. The form data goes to a transactions table that is linked to a parent table that shows UTD inventory of all of our items at any given time.

Essentially, the process would be:

form submission → record created in transaction table → update status of selected items in the parent table

Here is the current automation I’ve been trying to work with, where it will run successfully:

And how it is running unsuccessfully:

I’m aware that the issue directly comes from AT not reading both Record IDs to update.

I do not have access to apps, only to scripts. Please help!

[kuovonne]

Wendy_French:

I have an enterprise account, but our AT admin has disabled company use of non-AT apps

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.

[Wendy_French]

@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:

[TheTimeSavingCo]

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

[TheTimeSavingCo]

kuovonne:

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.

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

[Wendy_French]

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

And here are the corresponding options in the transactions table:

[Wendy_French]

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

[TheTimeSavingCo]

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

[kuovonne]

Wendy_French:

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

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

Adam_TheTimeSavingCo:

Hm this is puzzling.

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

[Wendy_French]

@Adam_TheTimeSavingCo Here is the link!

[TheTimeSavingCo]

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

kuovonne:

Looks like there are more upper/lower case issues to match up.

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

---

kuovonne:

Also the script uses an “Error” status that doesn’t exist.

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