Skip to main content

I am using the following script for when a new record is added that will provide a running total. 

// Hard-coded table and view names
let tableName = 'Invoices'; 
let viewName = 'Grid view'; 

let table = base.getTable(tableName);
let view = table.getView(viewName);

let result = await view.selectRecordsAsync({ fields: ['Amount'] });
let runningTotal = 0;
for (let record of result.records) {
// Hard-coded field names
let amount = record.getCellValue('Amount');
runningTotal += amount;

// Hard-coded field name for updating the record
await table.updateRecordAsync(record, {
'Running Total': runningTotal,
});
}

Runs well and little too well. 😂

When I add a record it runs the script before I'm able to add the amount and a new blank must be added for the correct calculation to appear. Is there some sort of delay that I could add or am I taking the wrong approach?

Hi,
you can change trigger to 'When record matches condition' and choose condition to be sure that amount is entered. for example, 
primary field is not empty AND
amount is not empty AND
some field after amount is not empty.

The last is important because if you try to input '200' in amount, automation might start right after '2' and took it as value.

Instead, you can add delay with 'Date modified' and formula column like 'ISBEFORE( DATEADD(date_modified,1,minutes) , NOW() )', to have 5 min delay , but NOW() function refresh time is up to 5 minutes, so you might have to wait.
I tried much ways and found that my colleagues prefer simple and obvious way - just add checkbox field like 'Refresh Totals'. The last automation step with 'uncheck box' (just set 0 or space there) is a good way to tell that 'refresh completed'

Hi,
you can change trigger to 'When record matches condition' and choose condition to be sure that amount is entered. for example, 
primary field is not empty AND
amount is not empty AND
some field after amount is not empty.

The last is important because if you try to input '200' in amount, automation might start right after '2' and took it as value.

Instead, you can add delay with 'Date modified' and formula column like 'ISBEFORE( DATEADD(date_modified,1,minutes) , NOW() )', to have 5 min delay , but NOW() function refresh time is up to 5 minutes, so you might have to wait.
I tried much ways and found that my colleagues prefer simple and obvious way - just add checkbox field like 'Refresh Totals'. The last automation step with 'uncheck box' (just set 0 or space there) is a good way to tell that 'refresh completed'


By the way, when your number of records increases, you might have another timing issue due to looping single update operation.
but that's another story, search for "updateRecordsAsync"

By the way, when your number of records increases, you might have another timing issue due to looping single update operation.
but that's another story, search for "updateRecordsAsync"


Yes, good point. I actually have PA flows that had to be revised, and broken into scopes to keep the cart from getting in front of the horse. 

I also thought about an action button and once that button is activated the script would run. There is a good case for a couple of action buttons.

Reply


Terms & Conditions