Help

"Number" field doesnt get updated based on the position of the task after running the script

Topic Labels: Scripting
417 0
cancel
Showing results for 
Search instead for 
Did you mean: 
devdoc
6 - Interface Innovator
6 - Interface Innovator

Issue: I am trying to have a "Number" field to get updated with values starting from 1, 2, 3, etc based on the position of the task in the chain. 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 hierarchy. And yes there are no circular references for that chain or any branching etc.

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:

 

 

const table = base.getTable('Tasks');
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}
});
}

// Handle isolated tasks (no predecessors, no next tasks)
const isolatedTasks = query.records.filter(
r => (!r.getCellValue('Predecessors')) && (!r.getCellValue('Next task'))
);

for (const isolatedTask of isolatedTasks) {
updates.push({
id: isolatedTask.id,
fields: {'Milestone Name': isolatedTask.name}
});
}

// Batch updates within API rate limits
while (updates.length) {
await table.updateRecordsAsync(updates.splice(0, 50));
}

 

 

 

0 Replies 0