Help

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

Topic Labels: Scripting extentions
466 0
cancel
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) {
        continue;
    }    


console.log(record)




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

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

image

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?

Thanks.

0 Replies 0