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.

Dynamically filter the last record based on the view/grouping

Topic Labels: Scripting extentions
273 3
cancel
Showing results for 
Search instead for 
Did you mean: 

Hi Guys,

Previously I was using filtered.pop (thanks to @Alexey_Gusev) to find the last record that has no amount

    let filtered=(result.records.filter(item => item.getCellValue('Cumulative by Customer') > 0));
    let last=filtered.pop();

image

However when I am adding more data, let’s say different customers, it’s not dynamically filtering those data. The amount $12,203.50 below is based on last amount of $10,253.5 above (they are different customers). It’s supposed to take the last amount of $22,776 instead

image

Is it advisable to build custom index of the data or is there any better method to calculate cumulative sales amount by customer?

Thank you.

3 Replies 3

Are you selecting records from the table or the view? Are you specifying a sort when you select the records? If you are selecting the records directly from the table without specifying a sort, the records are not going to be in a dependable order.

Are you processing all the records at once from Scripting Extension or are you using a scripting automation that triggers off individual records? If you are using scripting automations with a synced table, you can also run into problems with a race condition since several records can trigger simultaneously.

Your method to write updates based on last changes only has a strong disadvantage - it doesn’t track the changes in earlier.
Reliable way - to build whole running total column ‘in memory’, compare with existing values, and write array of difference

Thanks for the input @kuovonne and @Alexey_Gusev.

I am specifiying records in the scripts and processing all the records at once. Perhaps the best solution is to combine both suggestions.

Build whole running total by customer ‘in memory’ and trigger with automation whenever there are new records for them.

I need to go back to my codecademy first :grinning_face_with_sweat: