Previously I was using filtered.pop (thanks to @Alexey_Gusev) to find the last record that has no amount
let filtered=(result.records.filter(item => item.getCellValue('Cumulative by Customer') > 0)); let last=filtered.pop();
However when I am adding more data, let’s say different customers, it’s not dynamically filtering those data. The amount $12,203.50 below is based on last amount of $10,253.5 above (they are different customers). It’s supposed to take the last amount of $22,776 instead
Is it advisable to build custom index of the data or is there any better method to calculate cumulative sales amount by customer?
Are you selecting records from the table or the view? Are you specifying a sort when you select the records? If you are selecting the records directly from the table without specifying a sort, the records are not going to be in a dependable order.
Are you processing all the records at once from Scripting Extension or are you using a scripting automation that triggers off individual records? If you are using scripting automations with a synced table, you can also run into problems with a race condition since several records can trigger simultaneously.
Your method to write updates based on last changes only has a strong disadvantage - it doesn’t track the changes in earlier.
Reliable way - to build whole running total column ‘in memory’, compare with existing values, and write array of difference
I am specifiying records in the scripts and processing all the records at once. Perhaps the best solution is to combine both suggestions.
Build whole running total by customer ‘in memory’ and trigger with automation whenever there are new records for them.
I need to go back to my codecademy first :grinning_face_with_sweat: