Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

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

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