A formula for running total based on LINKED records

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?

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

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.

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"

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"

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?

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?

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.

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)