Set Values For Multiple Records Using Script Automation

[Andrew_Miller2]

Hey all!

I am trying to set up an automation that will update multiple records using a script. From reading around the forum, I think I’m definitely almost there but need help crossing the finish line. Here’s the breakdown:

I have two two tables: one has pieces written by authors, while the other has feedback notes for those pieces. They have a one-to-many relationship: via linked record, several notes can be associated with one story.

Each time a new note comes in via submission form, it receives a number via automation. So the first note submitted for a piece has its note # set to “1”, the second to “2”, etc. This is a number field, not multiple select, single select, etc.

I’d like to set up another automation that renumbers notes whenever one is flagged for content. So, say that record A has 8 notes linked to it. Note #6 has inappropriate content, so I don’t want it to count towards the tally. I have two of three parts set up. When a note is flagged, it prompts the automation to change that note’s value to blank. Then, the automation pulls a list of notes from the same parent record whose #s are greater than the note # for the trigger record. In this example, that would be the notes for record A that have the #s 7 and 8.

Now, I want to plug those two note record IDs into a script and update their numbers to 6 and 7. I read this very helpful article about setting up a script that plugs in records from a previous step in the automation, but didn’t have the clout to morph that script into updating numerical cell values. Can anyone help?

[Justin_Barrett]

Have you already made modifications to the script that you found, and you just need a nudge with the remaining pieces? If so, please share what you’ve done so far (formatted as preformatted text using the </> button on the comment editor toolbar). It’s tough to know how to help without seeing your current progress.

[Andrew_Miller2]

So I’m looking at using the following script: (@CT3 's, from the topic linked above)

let records = input.config().step2Records //.step2Records must match the Name field of the input field.
let table = base.getTable("NAME OF TABLE STEP 2 RECORDS ARE IN"); // Replace with your table name.
for (let record of records) {
    await table.updateRecordAsync(record, {
    "Emailed": true, //Replace Emailed with the field name of the checkbox field you want to update, if different.
    })
}

So, first the automation waits for a multiple-select “Reason for Deletion” field to be not empty:

Then, the note number of that note is set to blank:

Then I need the list of Record IDs with the higher note value:

Okay, so now I have the Record IDs to plug into the script. The one provided in the linked article is meant to work with a checkbox field, so I tried (and failed) to adapt it to work for changing values in a number field. Part of what I’m unsure of is whether it’s better to add some kind of subtraction to the script (like I tried to do here), or use a formula field that is {Note #}-1 that I can use to set the value of the selected records from the previous step.

[CT3 · Accepted solution]

You’re pretty close. I don’t think you need to complicate it with an additional formula field.

The difference between the original application of the script and this one is that in the former, nothing was needed from the record being updated itself, besides the record ID, passed in from the previous step. We knew from the way the automation was set up that only records we would want “checked off” were coming into the script in the first place—so we could get right to assigning them a true value.

In this case, in order to update the record, you need to get the note number first. Subtle distinction, but in order to do that, you need to use getCellValue, as you are on your way to doing. However, in order to use getCellValue, you’ve got to pass the records to selectRecordAsync first. Then you can go about your business inside the updateRecordAsync function. Basically, you can’t chain getCellValue directly to updateRecord Async—one is for “getting” information and one is for “sending” it.

Try this:

let records = input.config().step2Records //.step2Records must match the Name field of the input field.
let table = base.getTable("Note Log"); // Replace with your table name.
for (let record of records) {
    let recordtoUpdate = await table.selectRecordAsync(record) // selects records to update
    await table.updateRecordAsync(record, {
    "Note #": recordtoUpdate.getCellValue("Note #") - 1, //Replace Note # with the field name of the number you want to update, if different.
    })

}