Retrieve the last row that has value and add it to current row amount

Topic Labels: Scripting extentions
557 0
Showing results for 
Search instead for 
Did you mean: 

This is my current scripting code to do cumulative total based on customer field.

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

let result = await view.selectRecordsAsync({fields: ['Customer','Sales Amount','Cumulative by Customer'],
    sorts: [
        {field: 'Customer'},
        {field: 'Sales Date', direction:'asc'},

let runningTotal = 0;
let currentCustomer = '';

for (let record of result.records) {
    // change the field names here to adapt this script to your base
    let currentAmount = record.getCellValue('Cumulative by Customer');
    // skip records
    if (currentAmount !== null) {


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

    runningTotal += record.getCellValue('Sales Amount');
    await table.updateRecordAsync(record, {
        'Cumulative by Customer': runningTotal,


But now I have a problem. It doesn’t retrieve the last/previous cumulative amount record and instead it only adds current sales amount field and do the running total. How do I retrieve the last cumulative amount and add it to current running total?


0 Replies 0