Updating linked records with script

Hi all,

My scripting ability is pretty basic, so bear with me.

I’m trying to automatically update linked records based whenever a record in a table is updated. Basically as follows.

We have a table called “Jobs in Progress”, and each record in that table has separate records attached from time to time in another table called “Tasks”. It isn’t uncommon to have several records in “tasks” linked to one record in “jobs in progress”. Records in “Jobs in progress” have a checkbox called “completed” which is either manually or automatically selected depending on several conditions. Records in tasks also have a checkbox called “completed”, which are usually manually checked.

What I would like to achieve is that if any record in “Jobs in progress” has the “completed” checkbox checked (true), that any linked records in the “Tasks” table with the “completed” checkbox unchecked, update so that they are checked, or true as well. The reason for this is that on some simple tasks we overtake the process that would normally lead to us manually checking these checkboxes, and it would be helpful to avoid having to manually go back through the “tasks” table and check the “completed” checkbox.

Any suggestions or examples would be greatly appreciated.

Thanks in advance.

Welcome to the community, @Ben_Jackson! :smiley: This can be done via an automation that runs a script action (your base will need to be in a Pro plan workspace). Set up the automation using the “When record matches conditions” trigger, with the trigger being a check in the {Completed} field in the [Jobs in Progress] table.

The script action will need the triggering record’s ID as an input variable:

Screen Shot 2020-11-21 at 5.37.23 PM

Here’s the script:

// Setup
const config = input.config()
const jobsQuery = await base.getTable("Jobs in Progress").selectRecordsAsync()
const tasksTable = base.getTable("Tasks")
const taskRecords = await tasksTable.selectRecordsAsync()
const checkedJob = jobsQuery.getRecord(config.recordID)

// Collect linked tasks and find those that aren't checked
let tasks = checkedJob.getCellValue("Tasks")
let uncheckedTasks = tasks.filter(linkObj => !taskRecords.getRecord(linkObj.id).getCellValue("Completed"))

// Build array of updates
let updates = uncheckedTasks.map(linkObj => {
    return {
        id: linkObj.id,
        fields: {
            "Completed": true
        }
    }
})

// Update task records
while (updates.length > 0) {
    await tasksTable.updateRecordsAsync(updates.slice(0, 50))
    updates = updates.slice(50)
}

3 Likes

Hi Justin,

Thanks a bunch, I got a quarter of the way there. I have no coding background so it was always going to be an uphill battle.

I tested it and it works a treat. Thanks heaps for your help!

1 Like

Glad to know that you got the answer you were seeking! If you would, please mark my comment (the one above, not this one) as the solution to your question. This helps others who may be searching with similar questions. Thanks!