Solved

How to add a filter script to running total script

Forum|Forum|2 years ago
January 31, 2023
6 replies
129 views

Ann_Yeom
Known Participant

Hello,

I'd like to add a filter to my running total script. I have a checkbox field and would like the script to only calculate the running total if the checkbox is checked. Any help appreciated.

let table = base.getTable('Resource Capacity Planning_Production');
let view = table.getView('Grid View');
let result = await view.selectRecordsAsync({fields: ['Pillar & Domain','FY Q2 23 - Demand (FTE)'],
sorts: [ {field: 'Pillar & Domain'}, {field: "Stack Rank"}]});
let runningTotal = 0;
let currentGrouping='';

for (let record of result.records) {
    if (currentGrouping !=record.getCellValueAsString('Pillar & Domain')) {
  currentGrouping=record.getCellValueAsString('Pillar & Domain');
  runningTotal = 0  }
    runningTotal += record.getCellValue('FY Q2 23 - Demand (FTE)');
    await table.updateRecordAsync(record, {
        'TEST Running Demand': runningTotal,
    });
}

Best answer by Ben_Young1

Hey @Ann_Yeom!

To accomplish this, you'll want to add the checkbox field to your view.selectRecordsAsync query return.
From there, we can utilize a callback function to filter out any records that do not have the checkbox field checked.

const table = base.getTable("Resource Capacity Planning_Production");
const view = table.getView("Grid View");

const checkedRecords = await view.selectRecordsAsync({
  fields: [
    "Pillar & Domain",
    "FY Q2 23 - Demand (FTE)",
    "your_checkbox_field"
  ],
  sorts: [
    {field: "Pillar & Domain"},
    {field: "Stack Rank"}
  ]
})
.then(queryResult => {
  return queryResult.records.filter(record => {
    record.getCellValue("your_checkbox_field") === true;
  })
});


for (let record of checkedRecords) {

  let recordPillarDomain = record.getCellValueAsString("Pillar & Domain");
  let recordFte = record.getCellValue("FY Q2 23 - Demand (FTE)");

  if (currentGrouping != recordPillarDomain) {
    currentGrouping = recordPillarDomain;
    runningTotal = 0; 
  }
  runningTotal += recordFte;
  await table.updateRecordAsync(record.id, {
    "TEST Running Demand": runningTotal
  })
};

I've added in the a placeholder field for you to replace with your checkbox field.
I didn't test this script, so you'll want to read over the field names and variables to make sure they align with the exact formatting of your field names.

Let us know if you run into any issues with the script. We'd be happy to hop in and help troubleshoot!

+22

Ben_Young1
Brainy
Answer
Forum|Forum|2 years ago
January 31, 2023

Hey @Ann_Yeom!

const table = base.getTable("Resource Capacity Planning_Production");
const view = table.getView("Grid View");

const checkedRecords = await view.selectRecordsAsync({
  fields: [
    "Pillar & Domain",
    "FY Q2 23 - Demand (FTE)",
    "your_checkbox_field"
  ],
  sorts: [
    {field: "Pillar & Domain"},
    {field: "Stack Rank"}
  ]
})
.then(queryResult => {
  return queryResult.records.filter(record => {
    record.getCellValue("your_checkbox_field") === true;
  })
});


for (let record of checkedRecords) {

  let recordPillarDomain = record.getCellValueAsString("Pillar & Domain");
  let recordFte = record.getCellValue("FY Q2 23 - Demand (FTE)");

  if (currentGrouping != recordPillarDomain) {
    currentGrouping = recordPillarDomain;
    runningTotal = 0; 
  }
  runningTotal += recordFte;
  await table.updateRecordAsync(record.id, {
    "TEST Running Demand": runningTotal
  })
};

Let us know if you run into any issues with the script. We'd be happy to hop in and help troubleshoot!

Ann_Yeom
Author
Known Participant
Forum|Forum|2 years ago
January 31, 2023

Hey @Ann_Yeom!

const table = base.getTable("Resource Capacity Planning_Production");
const view = table.getView("Grid View");

const checkedRecords = await view.selectRecordsAsync({
  fields: [
    "Pillar & Domain",
    "FY Q2 23 - Demand (FTE)",
    "your_checkbox_field"
  ],
  sorts: [
    {field: "Pillar & Domain"},
    {field: "Stack Rank"}
  ]
})
.then(queryResult => {
  return queryResult.records.filter(record => {
    record.getCellValue("your_checkbox_field") === true;
  })
});


for (let record of checkedRecords) {

  let recordPillarDomain = record.getCellValueAsString("Pillar & Domain");
  let recordFte = record.getCellValue("FY Q2 23 - Demand (FTE)");

  if (currentGrouping != recordPillarDomain) {
    currentGrouping = recordPillarDomain;
    runningTotal = 0; 
  }
  runningTotal += recordFte;
  await table.updateRecordAsync(record.id, {
    "TEST Running Demand": runningTotal
  })
};

Let us know if you run into any issues with the script. We'd be happy to hop in and help troubleshoot!

Hi @Ben_Young1 , thanks for the reply!
I went through the script and it makes sense to me but I am getting this error which Im not sure what I need to fix...

ERROR
TypeError: records.filter is not a function
    at <anonymous> on line 16
    at async main on line 4

Any ideas?

+22

Ben_Young1
Brainy
Forum|Forum|2 years ago
January 31, 2023

Hi @Ben_Young1 , thanks for the reply!
I went through the script and it makes sense to me but I am getting this error which Im not sure what I need to fix...

ERROR
TypeError: records.filter is not a function
    at <anonymous> on line 16
    at async main on line 4

Any ideas?

Ah! Appreciate the call out!
I just fixed the original snippet I posted in the original reply.

Give that a shot and lemme know if it tosses you any issues.

Ann_Yeom
Author
Known Participant
Forum|Forum|2 years ago
February 1, 2023

Ah! Appreciate the call out!
I just fixed the original snippet I posted in the original reply.

Give that a shot and lemme know if it tosses you any issues.

Works great! Thank you so so much!

Bachko
New Participant
Forum|Forum|2 years ago
April 24, 2023

What about resetting the Running Total to 0 for each grouped record set? I have a list of daily records and I am looking at having a Running Totals per month. I have created a field that pulls the year and the number of the month so it can be used as a parameter on an iteration for month+1 to return running totals back to 0. Could anyone help with this?
I guess this is another type of filtering....

let table = base.getTable("TB9S")
let view = table.getView("Period View")
let result = await view.selectRecordsAsync({fields: ["Total Sales", "Running total", "Month"]})
let runningTotal = 0
const updates = []
result.records.forEach(record => {
    runningTotal += record.getCellValue("Total Sales")
    if (record.getCellValue("Running total") !== runningTotal)
        updates.push({
            id: record.id,
            fields: {
                "Running total": runningTotal
            }
        })
})
while (updates.length)
    await table.updateRecordsAsync(updates.splice(0, 50))

+22

Ben_Young1
Brainy
Forum|Forum|2 years ago
April 25, 2023

let table = base.getTable("TB9S")
let view = table.getView("Period View")
let result = await view.selectRecordsAsync({fields: ["Total Sales", "Running total", "Month"]})
let runningTotal = 0
const updates = []
result.records.forEach(record => {
    runningTotal += record.getCellValue("Total Sales")
    if (record.getCellValue("Running total") !== runningTotal)
        updates.push({
            id: record.id,
            fields: {
                "Running total": runningTotal
            }
        })
})
while (updates.length)
    await table.updateRecordsAsync(updates.splice(0, 50))

Hey @Bachko!

Since it seems you're trying to get reporting numbers for monthly performance, I would recommend that you actually create a Months table, where each record corresponds to a unique calendar month.

From there, you would link your records in your TB9S table to their corresponding month record.
To get your consolidated reporting metrics, you would use rollups and formulas on the month object.
Once it's all together, it's quite intuitive and it works incredibly well.

Scripting this type of operation is redundant and more work than it's worth.

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded