Skip to main content

Dynamically filter the last record based on the view/grouping

H

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();

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

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

kuovonne
Forum|alt.badge.img+17
  • kuovonne
  • Brainy
  • 5993 replies
  • September 1, 2022

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.

H
1 person likes this
  • H
    Hendrik_Yang
Alexey_Gusev
Forum|alt.badge.img+12
  • Alexey_Gusev
  • Brainy
  • 1116 replies
  • September 2, 2022

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

H
1 person likes this
  • H
    Hendrik_Yang
H
  • Hendrik_Yang
  • Author
  • Inspiring
  • 118 replies
  • September 2, 2022

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:

kuovonne
1 person likes this
  • kuovonne
    kuovonne

Reply


Most helpful members this week

Terms & Conditions