Need Help Optimizing Script to Update Records in Airtable

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:

It retrieves records from the "Tasks" table, selecting specific fields: "Predecessors", "Tasks", and "Next task".
It filters the retrieved records to find tasks that don't have any successor tasks (i.e., "Next task" is empty).
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.
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!

Page 1 / 1

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

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

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

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!

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

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

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

Thanks!

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

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

Thanks!

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.

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

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

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

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

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!!

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!!

Hi, it works, but today i'm too lazy to make code a bit cleaner ))

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},[])), 


...finalTasks.map(({id,name})=>({id,fields:{'Milestone Name':name}}))]


while(updates.length) await table.updateRecordsAsync(updates.splice(0,50))

Hi, it works, but today i'm too lazy to make code a bit cleaner ))

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},[])), 


...finalTasks.map(({id,name})=>({id,fields:{'Milestone Name':name}}))]


while(updates.length) await table.updateRecordsAsync(updates.splice(0,50))

Actually this works on the example base but when i paste it to run on my real base I get the following error:

ERROR
RangeError: Maximum call stack size exceeded
at pred on line 3
at chain on line 6

Actually this works on the example base but when i paste it to run on my real base I get the following error:

ERROR
RangeError: Maximum call stack size exceeded
at pred on line 3
at chain on line 6

Hmm... i need additional script output to realize.
How much records in a base and how long might be the longest chain?
Is it possible you have a kind of circular reference, like record linked to itself (i never tried whether it's doable)?
Or maybe something like Record A linked to record B (as predecessor), but record B linked to A in the same field.
Well, I;ll think and output some additional 'console.log' in script, to be able to diagnose the error.

I think you guessed it right- there was a circular reference which I resolved and then it worked. but on closer look- i saw only some records were updated. then figured out that those "Tasks" that had 2 or more than 2 predecessors- the "other" predecessor chain wasnt updated with the milestone names. i tried that just now in the example base and it showed the same result- the task a, b, c series didnt update the milestone names:

I forgot to mention in my initial description of the case that I am also trying to have a "Number" field get updated with values starting from 1, 2, 3, etc based on the position of the task in the chain. here is the updated code which works but I am seeing the numbers are coming off: like 3,3,3,3, for a lot of tasks in the same chain despite there being a heirarchy. And yes there are no circular references for that chain or any branching etc.

const table = base.getTable('Tasks'); // Replace 'Tasks' with your table's name 


const query = await table.selectRecordsAsync({


    fields: ["Predecessors", "Tasks", "Next task", "Number"] 


}); 





// Recursive function to handle multiple predecessor chains (with circularity protection)


function updatePredecessorChains(record, milestoneName, visited = new Set()) {


    const updates = [];


    const predecessors = record.getCellValue('Predecessors') || []; 





    for (const predecessorId of predecessors) {


        const predecessorRecord = query.getRecord(predecessorId.id); 





        // Circular Reference Check


        if (visited.has(predecessorRecord.id)) { 


            console.error('Circular dependency detected for:', predecessorRecord.id);


            continue;  // Skip to avoid infinite recursion


        }





        // Update Milestone Name of predecessors


        if (predecessorRecord) { 


            visited.add(predecessorRecord.id); // Mark current record as visited





            const recordUpdates = {


                id: predecessorRecord.id, 


                fields: { 'Milestone Name': milestoneName }


            };





            // Force update of 'Number' field


            recordUpdates.fields['Number'] = 1;  // Start new chain count 





            // Counting logic - remains the same


            if (predecessorRecord.getCellValue('Predecessors')) {


                recordUpdates.fields['Number'] = (predecessorRecord.getCellValue('Number') || 0) + 1;  


            }





            // Recursive calls (with visited tracking)


            const nestedUpdates = updatePredecessorChains(predecessorRecord, milestoneName, visited); 


            updates.push(...nestedUpdates);





            updates.push(recordUpdates); 


        }


    }


    return updates;


}





const finalTasks = query.records.filter(


    r => (!r.getCellValue('Next task')) && r.getCellValue('Predecessors')


);





const updates = [];


for (const finalTask of finalTasks) {


    const updateChainResult = updatePredecessorChains(finalTask, finalTask.name);


    updates.push(...updateChainResult); // Spread syntax to flatten nested updates





    // Update the final task itself


    updates.push({


        id: finalTask.id,


        fields: {'Milestone Name': finalTask.name}


    });


}





// Batch updates within API rate limits


while (updates.length) {


    await table.updateRecordsAsync(updates.splice(0, 50)); 


}

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded