Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

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

Topic Labels: Scripting extentions
812 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