Need Help Optimizing Script to Update Records in Airtable

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

[Alexey_Gusev]

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

I

[devdoc]

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

[Alexey_Gusev]

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.

[devdoc]

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:

[devdoc]

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