Running Total - Script Trigger

[Jenny_Arntzen]

I have been able to replicate the script for Running Total as per

console.log(Hello, ${base.name}!);
let table = base.getTable(‘Invoices’);
let view = table.getView(‘Grid view’);
let result = await view.selectRecordsAsync({fields: [‘Amount’]});
let runningTotal = 0;
for (let record of result.records) {
// change the field names here to adapt this script to your base
runningTotal += record.getCellValue(‘Amount’);
await table.updateRecordAsync(record, {
‘Running total’: runningTotal,
});
}

The Automation is turned ON

My question is - I can only seem to get the Running Total result to re-calculate when I am in Automations view and I click to Test action.

Is there something I am missing in my setup for the automation? Also - what is the recommended setup for this automation - do I want it running for every entry or should I have it only run when I need it? Will it slow down my table if it is processing every record as they are inputted?

Thanks!

[Justin_Barrett]

What’s your automation trigger? If it’s “When record is created”, then the automation is running before any data is entered. That trigger means exactly what it says: the automation will run at the exact moment that the record is created; i.e. before data is entered into any fields. If you’re using a form to enter your data, though, the form creates the record with all data immediately in place, so that trigger would work. However, if that’s not the case, the automation is running on an empty record when it’s triggered.

If that’s not the trigger that you’re using, then please mention what it is and we can help more precisely.

To answer your question about whether it’ll slow down interaction with your data, it probably won’t. Automations run in the background and largely don’t affect real-time performance. That said, the script that you’re running is updating one record at a time, which is inefficient. The more records are in your table, the more likely it will be that the script will exceed the maximum run time of 30 seconds before all records are processed.

A more efficient method is to capture all changes into an array, and then update records in batches from that array. The largest batch that Airtable allows is 50 records. Here’s the end of your script updated to use a batch-update method:

let runningTotal = 0
let updates = result.records.map(record => {
    runningTotal += record.getCellValue("Amount")
    return {
        id: record.id,
        fields: {
            "Running total": runningTotal
        }
    }
})
while (updates.length)
    await table.updateRecordsAsync(updates.splice(0, 50))

[Jenny_Arntzen]

Thank you for your advice!

My script trigger is currently “When a records enters a view” and the view does not have any filters on it - I’m not using forms - there is probably a better way for me to activate this automation.

Thanks for the batch piece! This is my first time coding in Airtable (I am definitely on the low-code side of the spectrum)

[Justin_Barrett]

Jenny_Arntzen:

My script trigger is currently “When a records enters a view” and the view does not have any filters on it

In that case it’s going to be the same result as the other trigger that I mentioned. The moment that you create a new record, it enters that view and triggers the automation. However, because it’s a new record with no data, the automation doesn’t calculate it properly.

Because you want to wait to trigger the automation until after there’s an actual value to calculate, I recommend setting up a different trigger that will be based on what you enter into the {Amount} field. Add a formula field with this formula:

IF(Amount & "", NOW() > DATEADD(LAST_MODIFIED_TIME(Amount), 5, "minutes"))

That formula will output nothing if the {Amount} field is empty, 0 (zero) if a change was made within the last 5 minutes, and 1 when the change is more than 5 minutes ago. (While I’m sure that you’d prefer to run the automation without that delay, it’s unfortunately not possible to track changes more precise than that, and even 5 minutes is not as precise as reality; check the notes for the NOW() function on the formula field reference page).

Create a new view with a filter that only shows records where that formula field’s output is 1. Because of the delay in the refreshing of the NOW() value, records will likely appear in that view in batches. This way the automation won’t run for every single record, but for each batch of records that need to be processed, which will keep your automation usage lower.

To optimize the automation a bit further, use this variation of the script, which will only update records where the running total actually needs to be updated:

let table = base.getTable("Invoices")
let view = table.getView("Grid view")
let result = await view.selectRecordsAsync({fields: ["Amount", "Running total"]})
let runningTotal = 0
const updates = []
result.records.forEach(record => {
    runningTotal += record.getCellValue("Amount")
    if (record.getCellValue("Running total") !== runningTotal)
        updates.push({
            id: record.id,
            fields: {
                "Running total": runningTotal
            }
        })
})
while (updates.length)
    await table.updateRecordsAsync(updates.splice(0, 50))

[Jenny_Arntzen]

Thank you for this explanation. I only need to run the calculation when I am processing periodic reports. I will see if I can implement your idea and make it work.

[Bachko]

Hello, I managed to make the running total work however I would like to restart the sum month by month. Any idea how can I do this? 

[Bachko]

I have a field called month which is the one filtering the records and should restart on ) at every different month value