A formula for running total based on LINKED records

[Sean_Wilson]

Hi everyone.
We all know the script in the airtable scripting examples to create a running total.
The script below is as such:

// change these names to pick a view:
let table = base.getTable('Marketing');
let view = table.getView('Grid view');
let result = await view.selectRecordsAsync();
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 issue with the script is that it takes the running total from the previous row and adds the number of the current row to create the running total for the current row.
I was wondering if there was a way to filter based on linked records.
That is: for the current record, you only add the current number to the sum of running totals of linked records.

Would anyone know how to do this?

[Hendrik_Yang]

Hi @Alexey_Gusev ,

I have one LAST question :grinning_face_with_sweat:

Instead of running through all records, I just want to update those new records or where cumulative amount field is still empty.

for (let record of result.records) {
    let currentAmount = record.getCellValue('Cumulative by Customer');
// skip records
    if (currentAmount == null && currentAmount == '') {
        continue;
    }

But it still loops all records. Is there a way to filter it and only run for new records instead?

[Hendrik_Yang]

It should be

if (currentAmount !== null) {
   continue;
}

[Karlstens]

Having a read through this thread, when I anticipate that my code will be filtering records - I save and exit my code, and then head to the Airtable Views, create the filtered view to see the actively filtered records live (and check that they’re what I’m expecting) - and then use that newly created View in my Script/Automated Script.

I try to leverage as many non-coded elements in Airtable as possible. There are no doubt pro’s and con’s to hard coding things, but just through my experience I often enjoy to use the visual aids, such as a Table View, to then drive my code.

[Alexey_Gusev]

Totally agreed.
My automations usually zero-code or low-code.
Typical script is:

1. define input data
2. process array of input data into array of update/create
3. write output data

automation lets us manage 1&3 and in many cases it has enough ways to do 2, without code.

usual case - maintain links between 2 tables. so I create view “where link is empty” (lock it and set warning ‘don’t touch’) and set trigger to “when record enters view”.

just for scripting exp…

if (currentAmount !== null) {
   continue;
}

that’s correct. but…

if (currentAmount) continue
is enough

[Hendrik_Yang]

Appreciate your tips @Alexey_Gusev

I am now trying to improve the code, now instead of running all records, I want to store the last record that has cumulative amount and add it to new record.

I am trying to get the last cumulative amount of “00054408” and inserted below code above the if statement.

let previousAmount = result.records.indexOf(result.records.filter(item => item.getCellValue(‘Cumulative by Customer’) > 0).pop())

But it is showing error. Any idea why or how to achieve this using other methods?
Thanks

[Alexey_Gusev]

Hi,

in such cases I usually split complex statements to debug, like
let filtered=(result.records.filter(item => item.getCellValue(‘Cumulative by Customer’) > 0)
let last=filtered.pop()

and so on

I have no time to check, but I think you have error because result.records is array that doesn’t contain cell values. As i remember, it’s an array of objects like {‘id’:XXX, ‘name’:YYY}. So your item.getCellValue(‘Cumulative by Customer’) can’t be part of it

there are plenty of ways to achieve your goal. that’s how I did something alike, with using Index, , and it’s not a better way.
(I quoted part of script that contains necessary action)

const query=await table.selectRecordsAsync({fields:[field]});
const index=arr=>arr.indexOf(Math.max(...arr));
const val=query.records[index(query.records.map(compare))].getCellValueAsString(field);
output.text(val)

[Hendrik_Yang]

Thank you so so much. In the end I don’t use index/array but I manage to filter out the last amount of a filtered array and store that amount in a variable and now it is working!

Airtable is blessed to have someone like you in the community!