Help - Summarising Data by filtered column

[btg202]

I’ve created a script which counts the number of rows grouped by the column ‘Deal stage’. I now want to tweak it to sum the values in column ‘First credits’ and group by deal stage, rather than count the number.

Any ideas? It’s basically a =SUMIF equivalent.

let table = base.getTable('Origination');
let result = await table.selectRecordsAsync();

let recorddeal = result.records.filter(recorddeal => recorddeal.getCellValue('Deal stage'));
let records = result.records.filter(recorddeal => recorddeal.getCellValue('Deal stage'));

function countByField(records, groupByField) {
    let counts = {};
    for (let recorddeal of records) {
        let key = recorddeal.getCellValueAsString(groupByField);
        if (key in counts) {
            counts[key] = counts[key] + 1;
        } else {
            counts[key] = 1;
        }
    }
    return counts;
}

let categoryCounts = countByField(records, 'Deal stage');

const primaryFieldId = table.fields[0].id;
const queryResult =	await table.selectRecordsAsync({fields: [primaryFieldId]});

let recordCount = queryResult.records.length;

console.log(categoryCounts);

output.set('Total', recordCount)

output.set('CountDeal', categoryCounts)

[Jono_Prest]

Hi @btg202,

Welcome to the community!

I’ve edited your code according to how I’m understanding your problem. I’ve commented the edits I’ve made with an explanation of why. Let me know if this what you’re looking for.

let table = base.getTable("Origination");
let result = await table.selectRecordsAsync();

// Only need to create your filter once.
let records = result.records.filter((record) =>
  record.getCellValue("Deal stage")
);

// Removed your function definition as it was only called once
// Renamed your object since it is no longer dealing with counts
let cummulativeFirstCreditsByDealStage = {};

for (let record of records) {
  let key = record.getCellValueAsString(groupByField);

  // Coerce "First credits" to a number in case you get a returned null value.
  // Depending on the field type you may not get a number back from the .getCellValue method
  // If this is the case either use getCellValueAsString or figure out the structure to ensure
  // you get a number value.
  let firstCreditsCellValues = Number(record.getCellValue("First credits"));

  if (key in cummulativeFirstCreditsByDealStage) {
    cummulativeFirstCreditsByDealStage[key] =
      cummulativeFirstCreditsByDealStage[key] + firstCreditsCellValues;
  } else {
    cummulativeFirstCreditsByDealStage[key] = firstCreditsCellValues;
  }
}

// Record count can come straight from your result referenced at the top
let recordCount = result.records.length;

// Use output.text for a script (unless this is for an automation)
output.text("Total", recordCount);

// Output each key and value of your object as a line of text
for (let key of Object.keys(cummulativeFirstCreditsByDealStage)) {
  output.text(`${key}: ${cummulativeFirstCreditsByDealStage[key]}`);
}

[btg202]

Thank you so much, I’ll add it into the script

[btg202]

Hi!

I ran this but got the following error;

Any ideas?

Many thanks,

Ben

[Justin_Barrett]

In the script shared in your initial post, groupByField is an argument in the countByField function. However, in the altered version of the script that @Jono_Prest shared, that function doesn’t exist, so groupByField is never defined. Without that variable, you’ll need to replace groupByField with the literal name of the field.