Scripting Block formula help on comparing two dates

Formulas
#1

I have a table of subtasks called subtasks. this is the todo list for the whole team.

I used self-linking to get predecessors setup, and so I have:

  • a rollup field showing the predecessor end date (PredEnd)
  • a manual task start date (START)
  • a manual task duration in days (LT)
  • a formulated task completion date that adds the task duration in workdays to the start date (END)

What I want to do now is create a scripting block that can load all the records in this table only, and compare each records PredEnd vs START, and if they are different, to update the START using the correct PredEnd value.

But I keep getting errors and this is my first script… no idea what im doing…

let table = base.getTable('subtasks');
let field = table.getField("START")
let predEnd = table.getField("PredEnd");
let taskStart = table.getField("START");

// Load all of the records in the table
let result = await table.selectRecordsAsync();

// Find every record we need to update
let replacements = [];
for (let record of result.records) {
    let startValue = record.getCellValue(taskStart);
    let predValue = record.getCellValue(predEnd);
    if (predValue !== startValue) {
        replacements.push({
            record,
            before: startValue,
            after: predValue,
        });
    }
}

if (!replacements.length) {
    output.text('No replacements found');
} else {
    output.markdown('## Replacements');
    output.table(replacements);
    let updates = replacements.map(replacement => ({
        id: replacement.record.id,
        field: {
            [field.id]: replacement.before,
            }
        }));
        // Only up to 50 updates are allowed at one time, so do it in batches
        while (updates.length > 0) {
            await table.updateRecordsAsync(updates.slice(0, 50));
            updates = updates.slice(50);
    }
}
#2

What is the error you are getting? Are you able to share a screenshot of the error text, or copy-paste it here?

#3

sorry… it seems I corrected my original problem, but now have a new problem…

I want to:

  1. Remove the need to click save or review changes… I just want to trust in code :smiley:
  2. Repeat the process over and over until it finds no more fixes needed

see updated code below.

// edit these values to customize your script!
let table = base.getTable("subtasksTEST");
let wrongStart = table.getField("START");
let correctStart = table.getField("PredEnd");
let result = await table.selectRecordsAsync();

// Find every record we need to update
let replacements = [];
for (let record of result.records) {
    let originalValue = record.getCellValue(wrongStart);
    // Skip records which don't have the value set, so the value is null
    if (!originalValue) {
        continue;
    }
    let newValue = record.getCellValue(correctStart);
    if (!newValue) {
        continue;
    }
    if (originalValue !== newValue) {
        replacements.push({
            record,
            before: originalValue,
            after: newValue,
        });
    }
}
if (!replacements.length) {
    output.text('No replacements found');
} else {
    output.markdown('## Replacements');
    output.table(replacements);
    let shouldReplace = await input.buttonsAsync('Are you sure you want to save these changes?', [
        {label: 'Save', variant: 'danger'},
        {label: 'Cancel'},
    ]);
    if (shouldReplace === 'Save') {
        // Update the records
        let updates = replacements.map(replacement => ({
            id: replacement.record.id,
            fields: {
                [wrongStart.id]: replacement.after,
            }
        }));
        // Only up to 50 updates are allowed at one time, so do it in batches
        while (updates.length > 0) {
            await table.updateRecordsAsync(updates.slice(0, 50));
            updates = updates.slice(50);
        }
    }
}