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]

[Edit] Actually you can do it on default script by using view @Sean_Wilson

[Alexey_Gusev]

Hi

insert this function somewhere upper the loop.

let linkValue=a=>a? a.map(x=>Number(x.name)).reduce((b,c)=>b+c):0

instead of runningTotal += record.getCellValue('Amount')
use
runningTotal += linkValue(record.getCellValue('Amount'))

[Hendrik_Yang]

Hi @Alexey_Gusev ,

I encountered this error. Why is it?

[Alexey_Gusev]

Hi,
sorry for late response
I suppose I misunderstood initial settings. What’s the type of Amount field and the field, which it links?

that’s how it should work

Note that in my example RT is string so I’ve added type conversion.
But that’s not related with your error message.
await table.updateRecordAsync(record.id, {
‘Running Total’:runningTotal.toString(),

[Hendrik_Yang]

Dear @Alexey_Gusev ,

In my case

So I want to do running total based on linked record of customer. So the script must be able to detect previous row and whether it’s the same cust (add) if no (will take the current row and continue adding subsequest rows).

Do you have any idea how to accomplish this?

Many thanks in advance!

[Alexey_Gusev · Accepted solution]

It seems like you mean grouping by customer.
I would solve that via Map JS function, but it needs whole script re-write.
You may transform current:
add sort to selectRecordAsync, by customer, then by date (check API example)
insert new variable,

let runningTotal = 0;
let currentCustomer='';

reset counter after each customer change

if (currentCustomer!=record.getCellValueAsString('customerfield')) {
  currentCustomer=record.getCellValueAsString('customerfield');
  runningTotal = 0  }

(that should be inside loop before
runningTotal += linkValue(record.getCellValue('Amount')))

[Hendrik_Yang]

Hi @Alexey_Gusev ,

// change these names to pick a view:
let table = base.getTable('Sales');
let view = table.getView('Overall');

let result = await view.selectRecordsAsync({fields: ['Sales Amount']});

let customerField = await view.selectRecordsAsync({fields:['Customer'],
    sorts: [
    {field: 'Customer'},
    {field: "Sales Date",direction:"desc"},
    ]
}
);




let runningTotal = 0;
let currentCustomer = '';
let linkValue=a=>a? a.map(x=>Number(x.name)).reduce((b,c)=>b+c):0;


for (let record of result.records) {
    // change the field names here to adapt this script to your base


console.log(record)

if (currentCustomer != record.getCellValueAsString('Customer')) {
    currentCustomer = record.getCellValueAsString('Customer');
    runningTotal = 0
}
    runningTotal += linkValue(record.getCellValue('Sales Amount'));
    await table.updateRecordAsync(record, {
        'Cumulative by Customer': runningTotal,
    });
}

However I am getting this error now.

[Alexey_Gusev]

That’s because you are using result variable in your loop, which has another field.

instead of

let result = await view....

let customerField = await view.selectRecordsAsync({fields:['Customer'],
    sorts: [
    {field: 'Customer'},
    {field: "Sales Date",direction:"desc"},
    ]
}
);

You should use:

let result =  await view.selectRecordsAsync({fields:['Customer','Sales Amount'],
    sorts: [ {field: 'Customer'}, {field: "Sales Date",direction:"desc"} ]});

btw, I cannot understand why you are sorting date descending, because if you want it to be like on your picture, you should remove direction:"desc"

[Hendrik_Yang]

Dear @Alexey_Gusev ,

Yes, I have removed the direction and it is working now.

Thank you so much! :grinning_face_with_smiling_eyes: