Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. 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
811 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