Help

Re: Return a running total from a subset of a view

1196 0
cancel
Showing results for 
Search instead for 
Did you mean: 
aaron_altamura
6 - Interface Innovator
6 - Interface Innovator

Hello,

I'm trying to:

  1. Insert a variable to a script; | Complete
  2. Filter a view by this variable compared to a field (fldL0YbZsYuNJJwTP);  | Partially Incomplete
  3. Return a running total from that subset of a view; | Partially complete

Right now I'm getting 0, so I suspect my filter is off.

Here's the code I've been able to cobble together:

 

// change these names to pick a view:
let inputConfig = input.config();
const table = base.getTable('Deals');
const view = table.getView('Pipeline Stages 2-4');


const checkedRecords = await view.selectRecordsAsync({fields: [
    'Amount',
    'fldL0YbZsYuNJJwTP'
    ]})
    .then(queryResult => {
  return queryResult.records.filter(record => {
    record.getCellValue('fldL0YbZsYuNJJwTP') === inputConfig.Individual;
  })
});

let runningTotal = 0;

for (let record of checkedRecords) {
    // change the field names here to adapt this script to your base
    runningTotal += record.getCellValue('Amount');
}

return runningTotal

 

And here's a screenshot of the automation step:

Screenshot 2023-06-21 at 7.25.23 PM.png

Any feedback or support is appreciated, thank you so much!

5 Replies 5

inputConfig.Individual is returning a record ID string. Is the value of fldL0YbZsYuNJJwTP also expected to be a record ID string?

Try inserting

output.set('checkedRecords', checkedRecords)

right before your for loop to see what checkedRecords contains. I'm guessing it's empty.

Hi Stephen, appreciate the support!

The output is showing an empty set:

Screenshot 2023-06-22 at 3.23.02 PM.png

So does your field fldL0YbZsYuNJJwTP contain record IDs or some other value? It sounds like the filter here 

queryResult.records.filter(record => {
    record.getCellValue('fldL0YbZsYuNJJwTP') === inputConfig.Individual;
  })

is removing everything because the value of fldL0YbZsYuNJJwTP is not a record ID or you don't have a record where this field contains the exact record ID that you're filtering on. What does this field look like in your table?

It contains the string in an array, per the documentation:

Screenshot 2023-06-22 at 3.58.30 PM.png

Stephen_Orr1
10 - Mercury
10 - Mercury

Try this

 

return queryResult.records.filter(record => 
    record.getCellValue('fldL0YbZsYuNJJwTP').includes(inputConfig.Individual)
  )