Script comparing and updating records

[devdoc]

I have a script that I'm using to update records in my Airtable base, but it seems to be taking longer than expected and sometimes hangs indefinitely. I'm seeking assistance to optimize the script for better performance.

Script Description: The script aims to update records in the "Tasks" table of my Airtable base. Here's what it does:

1. It retrieves records from the "Tasks" table, selecting specific fields: "Predecessors", "Tasks", and "Next task".
2. It filters the retrieved records to find tasks that don't have any successor tasks (i.e., "Next task" is empty).
3. For each of these final tasks, it traverses the chain of predecessors to update a field called "Milestone Name" with the name of the final task.
4. It performs updates in batches of 50 records to avoid hitting Airtable API rate limits.

Code:

async function updateChainWithFinalTaskName() {
const table = base.getTable("Tasks");
try {
const query = await table.selectRecordsAsync({
fields: ["Predecessors", "Tasks", "Next task"]
});

const finalTasks = query.records.filter(record => {
const nextTask = record.getCellValue("Next task");
return !nextTask || nextTask.length === 0;
});

for (let finalTask of finalTasks) {
let currentRecordId = finalTask.id;
let finalTaskName = finalTask.getCellValueAsString("Tasks");
let updates = [];

while (currentRecordId) {
const currentRecord = query.getRecord(currentRecordId);
if (!currentRecord) {
console.error('Record not found, ID:', currentRecordId);
break; // Exit if the current record is not found
}

updates.push({
id: currentRecordId,
fields: {"Milestone Name": finalTaskName}
});

const predecessors = currentRecord.getCellValue("Predecessors");
currentRecordId = predecessors && predecessors.length > 0 ? predecessors[0].id : null;
}

// Perform updates in batches
while (updates.length > 0) {
try {
await table.updateRecordsAsync(updates.splice(0, 50));
} catch (error) {
console.error('Error updating records:', error);
break; // Exit the loop on error
}
}
}
} catch (error) {
console.error('Error selecting records:', error);
}
}

// Call the function with await to ensure it waits for the records to be selected
await updateChainWithFinalTaskName().catch(console.error);

I would appreciate any insights or suggestions on how to optimize this script for better performance. Thank you!

[TheTimeSavingCo]

Could you provide an example base with everything set up so that helping you troubleshoot it is easier? 

[Alexey_Gusev]

Hi,
Well, I guess it's better to be created from scratch. If the script above written by chatGPT, it has too much 'overengineering', usually there is a low probability script written by AI will work (sometimes, at least). Also it might be remix or the old script written when some ES6 features weren't supported.
It's hard to understand 100% without example, but I think it should be something like:

const table=base.getTable('Tasks')
const query=await table.selectRecordsAsync({fields: ["Predecessors", "Tasks", "Next Task"]})
const pred=r=>r.getCellValue('Predecessors') // to simplify code text
//take records with empty Next task, ignoring empty Predecessors
const finalTasks=query.records.filter(r=>(!r.getCellValue('Next Task'))&&pred(r))
const update=r=>({id:pred(r)[0].id,fields:{'Milestone Name':r.getCellValue('Tasks')}})
const updates=finalTasks.map(update)
while(updates.length) await table.updateRecordsAsync(updates.splice(0,50))

[devdoc]

Thanks- yes it was created using ChatGPT's help!

The script you shared only updates till 1 level up in the chain but doesnt go till the top of the chain when the chain has >2 records. 

Also here is an example base- https://airtable.com/appFfzc1PTp24Evqv/shrAiwTwCNiNIhnHO

There are 2 scripts- one creates backlinks to overcome the limitation of being limited to a row. I use the "Predecessor" field as the real source and then that script automatically updates the  "Next task" field. 

I am using your script in the 2nd script- but it only updates till 1 level up.

Thanks!

[devdoc]

Thanks, here is an example base- https://airtable.com/appFfzc1PTp24Evqv/shrAiwTwCNiNIhnHO

There are 2 scripts- one creates backlinks to overcome the limitation of being limited to a row. I use the "Predecessor" field as the real source and then that script automatically updates the  "Next task" field. 

I am using the code mentioned in the other answer in the 2nd script- but it only updates till 1 level up.

Thanks!

[Alexey_Gusev]

Hi,
I think I got it, but there are several possible outputs.
Please use example base to show the desired result. Preferably, add one more element in any of chains, I mean, make its length >3
I never used self-linked connections in my real-life tasks, so I have no clear vision of the end result.

[devdoc]

Thanks, just added task 0 to make it a 4 level chain from task 0 to task 3.

[devdoc]

Thanks. here is an example base- https://airtable.com/appFfzc1PTp24Evqv/shrAiwTwCNiNIhnHO

There are 2 scripts- one creates backlinks to overcome the limitation of being limited to a row. I use the "Predecessor" field as the real source and then that script automatically updates the  "Next task" field. 

[Alexey_Gusev]

So, the example base has 2 values in "Milestone", created by existing script.
What I asked - to show what do you want script to do. (update: now when I finished the script, it looks so obvious, that I'm not surprised you didn't understand my question. Or maybe my English is worse than I think 😀)
So, I'm trying to guess. The bold are values set by script, and others are what do you want to set as well.

Well, despite I've added just two lines, and now it looks easy for me, it's the hardest and therefore the most interesting programming task I've met for Airtable for last year. Of course it could be rewritten in other way with several FOR loops, and my solution can be optimized too, if needed. But I wanted to solve real-life task with recursion (function calling itself) and it worked.

const table=base.getTable('Tasks')
const query=await table.selectRecordsAsync({fields: ["Predecessors", "Tasks", "Next task"]})
const pred=r=>r.getCellValue('Predecessors') 
const finalTasks=query.records.filter(r=>(!r.getCellValue('Next task'))&&pred(r))
const next=x=>query.getRecord(pred(x)[0].id)
const chain=(r,fields,arr)=>pred(r)? [...arr,{id:next(r).id,fields},...chain(next(r),fields,arr)]:arr
const updates=finalTasks.flatMap(r=>chain(r,{'Milestone Name':r.name},[]))
while(updates.length) await table.updateRecordsAsync(updates.splice(0,50))

[devdoc]

Thank you, you did it!! The only other thing that will make it perfect (allow me to group by "Milestone name") will be if the "Milestone name" is updated for the final tasks in the chain too.

Below is the screenshot where I have highlighted it:

Thanks again!!