Help

Re: A formula for running total based on LINKED records

Solved
Jump to Solution
2916 13
cancel
Showing results for 
Search instead for 
Did you mean: 
Sean_Wilson
6 - Interface Innovator
6 - Interface Innovator

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?

1 Solution

Accepted Solutions

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')))

See Solution in Thread

16 Replies 16

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

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'))

Hi @Alexey_Gusev ,

I encountered this error. Why is it?

image

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

image

that’s how it should work

image

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(),

Dear @Alexey_Gusev ,

In my case

image

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!

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')))

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.
image

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"

Dear @Alexey_Gusev ,

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

Thank you so much! :grinning_face_with_smiling_eyes:

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?

It should be

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

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.

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

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.

Screenshot 2022-08-08 112721

Screenshot 2022-08-08 112743

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

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)

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!