Need help with a Running Total automation script. Here is the script I have doctored from Airtable sample called Running Total.
Need to update current record Balance field (X) by adding the value of previous record value of Balance (Y) plus current Income value (I) or minus current Expense value (E). Formula should be X=(Y+I-E).
Here is the logic:
Trigger: When Form Is Submitted
Action: Run Script
Here are my data fields: Date, Income, Expense, Balance, Autonumber.
Previous Record: Autonumber 113 has a Balance value of $4,400.93
Current Record: Autonumber 114 has a Balance value of null, Income of $1, then updated Balance value is $4,401.93.
Next Record: Autonumber 115 has a Balance value of null, Expense of $5, then update Balance value is $4,396.93.
Need help updating my script to look at any null Balance value and update it with the Balance value from the preceding Autonumber. Also needs to update all Balance values. If Income or Expense is updated on a previous record, it should update accordingly based on the sequence of Autonumbers.
I would be grateful for any help I can get.
// Specify the table and view
let table = base.getTable('Actual');
let view = table.getView('Grid view');
// Select the records from the specified view
let result = await view.selectRecordsAsync({fields: ['Income', 'Expense', 'Autonumber']});
// Create a new array from the records and sort it by the "Autonumber" field
let records = Array.from(result.records).sort((a, b) => a.getCellValue('Autonumber') - b.getCellValue('Autonumber'));
let runningTotal = 0;
for (let record of records) {
// Get the income and expense values for the record
let income = record.getCellValue('Income') || 0;
let expense = record.getCellValue('Expense') || 0;
// Update the running total
runningTotal += income - expense;
// Update the Balance field for the record
await table.updateRecordAsync(record, {
'Balance': runningTotal,
});
}