Help

Add multiple running total script into one script

Topic Labels: Scripting
Solved
Jump to Solution
1103 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Ann_Yeom
6 - Interface Innovator
6 - Interface Innovator

Hello,

I'm using this running total script which works great but I want to add another running total column based on another field to this. Any solutions on how to add another running total in the same script?

The table and view are the same
The fields would be different (Field Pillar & Domain is the same but instead of Q2 Demand it would be Q3 Demand)
The sort would be the same

Running total field would be Q3 Running Demand

 

let table = base.getTable('Resource Capacity Planning');
let view = table.getView('Capacity Planning Include Only');
let result = await view.selectRecordsAsync({
  fields: ['Pillar & Domain','Q2 Demand'],
  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('Q2 Demand');
    await table.updateRecordAsync(record, {
        'Q2 Running Demand': runningTotal,
    });
}

 Thank you!

1 Solution

Accepted Solutions
TheTimeSavingCo
18 - Pluto
18 - Pluto

Probably something like:

let table = base.getTable('Resource Capacity Planning');
let view = table.getView('Capacity Planning Include Only');
let result = await view.selectRecordsAsync({
  fields: ['Pillar & Domain','Q2 Demand', 'Q3 Demand'],
  sorts: [ {field: 'Pillar & Domain'}, {field: "Stack Rank"}]});
let q2RunningTotal = 0;
let q3RunningTotal = 0;
let currentGrouping='';

for (let record of result.records) {
    if (currentGrouping !=record.getCellValueAsString('Pillar & Domain')) {
      currentGrouping=record.getCellValueAsString('Pillar & Domain');
      q2RunningTotal = 0; 
      q3RunningTotal = 0;  
    }
    q2RunningTotal += record.getCellValue('Q2 Demand');
    q3RunningTotal += record.getCellValue('Q3 Demand');
    await table.updateRecordAsync(record, {
        'Q2 Running Demand': q2RunningTotal,
        'Q3 Running Demand': q3RunningTotal,
    });
}

See Solution in Thread

2 Replies 2
TheTimeSavingCo
18 - Pluto
18 - Pluto

Probably something like:

let table = base.getTable('Resource Capacity Planning');
let view = table.getView('Capacity Planning Include Only');
let result = await view.selectRecordsAsync({
  fields: ['Pillar & Domain','Q2 Demand', 'Q3 Demand'],
  sorts: [ {field: 'Pillar & Domain'}, {field: "Stack Rank"}]});
let q2RunningTotal = 0;
let q3RunningTotal = 0;
let currentGrouping='';

for (let record of result.records) {
    if (currentGrouping !=record.getCellValueAsString('Pillar & Domain')) {
      currentGrouping=record.getCellValueAsString('Pillar & Domain');
      q2RunningTotal = 0; 
      q3RunningTotal = 0;  
    }
    q2RunningTotal += record.getCellValue('Q2 Demand');
    q3RunningTotal += record.getCellValue('Q3 Demand');
    await table.updateRecordAsync(record, {
        'Q2 Running Demand': q2RunningTotal,
        'Q3 Running Demand': q3RunningTotal,
    });
}

This is perfect! Thank you Adam! 👍