Updating linked records with script

[Ben_Jackson]

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.

[Justin_Barrett · Accepted solution]

Welcome to the community, @Ben_Jackson! :grinning_face_with_big_eyes: 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:

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)
}

[Ben_Jackson]

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!

[Justin_Barrett]

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!

[airballer86]

@Justin_Barrett Is there any limit to the number of records that can be updated by the above automation/script combo? I have two tables, one with 23K records that I need to join.

[Justin_Barrett]

@airballer86 The only limit would be based on how long the update takes to run. Automation scripts are only allowed to run for 30 seconds before timing out. My gut says that 23K records would not update during that timeframe.

[airballer86]

@Justin_Barrett Your gut is correct. It takes about 15 min, and click "wait" when Chrome tries to kill the page. It does work, but not ideal.