Skip to main content

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

 






Hi,
TBH, I don't quite understand the full picture
- do you want script to pass the whole table each time new record submitted?
- what's the sense of updating null balance values at first if the script then will count second record Balance according to 1st Balance + 2nd Income - 2nd Expense.  Then count 3rd record Balance... and so on, whole table counted.
- you don't need running total variable then, as Balance play it's role

(after some time)..... Well, the task reminded my own struggle with RT, but I knew JS much worse that time, so I tried to write my own code.
Note it will update only those balances that really need it, so if you run it twice, second will do nothing. And if you try to delete some Balances and run , it will restore whole chain.

const table = base.getTable('Actual');
const view = table.getView('Grid view');
const query=await view.selectRecordsAsync({fields:table.fields,sorts:[{field:'Autonumber'}]})
const num=r=>r.getCellValue('Autonumber'); const upd=[];
const balances=new Map(query.records.map(r=>[num(r),r.getCellValue('Balance')]))
const previous=n=>balances.get(Math.max(...[...balances.keys()].filter(k=>k<n)))
balances.set(num(query.records[0])-1,query.records[0].getCellValue('Balance'))
const curBalance=r=>previous(num(r))+r.getCellValue('Income')-r.getCellValue('Expense')
query.records.forEach(r=>{
if(curBalance(r)!==balances.get(num(r))) { balances.set(num(r),curBalance(r))
upd.push({id:r.id,fields:{'Balance':curBalance(r)}})
}})
if(upd.length) console.log(...upd.map(r=>({'Autonumber':num(query.getRecord(r.id)),...r.fields})))
while(upd.length) await table.updateRecordsAsync(upd.splice(0,50))

 

Reply


Terms & Conditions