Help

Combine Multiple Rollup Arrays into One

Topic Labels: Formulas
Solved
Jump to Solution
1357 3
cancel
Showing results for 
Search instead for 
Did you mean: 
spancakes
5 - Automation Enthusiast
5 - Automation Enthusiast

In my "CRM" database, I am building out a reporting table that gets rollups of IDs from other tables (i.e. interactions, etc) and am trying to get one array of IDs that I can count to determine the number of unique IDs

Interactions Table

One to Many Contacts, lookup field of Contact Record IDs from "Contacts" Table

Projects Table

One to Many Contacts, Lookup Field of Contact Record IDs from "Contacts" Table

Reports Table

Rollup of Contact IDs from Projects, Rollup of Contact IDs from Interactions

My formula below doesn't get all the IDs. I don't know if maybe there's some nesting going on. Any suggestions for combining these arrays? properly?

 

COUNTA(ARRAYUNIQUE(ARRAYFLATTEN({Interaction Contact IDs}, {Project Contact IDs})))

 

Each of my rollups in Reports has this formula

 

ARRAYUNIQUE(ARRAYFLATTEN(values))

 

 

 

1 Solution

Accepted Solutions
spancakes
5 - Automation Enthusiast
5 - Automation Enthusiast

So there's no good formula solution for this, but below is a modified script from @TheTimeSavingCo in this post.

Script Extension

Where "firstRollup" etc is your field name and OUTPUT_FIELD is the field you want to write to.

 

 

// Setup Start
let fieldNames = ["[ROLLUP 1]", "[ROLLUP 2]", "[ROLLUP 3]", "[ROLLUP 4]"];
let combinedFieldId = "[OUTPUT FIELD ID]";
// Setup End

let tableId = cursor.activeTableId;
let table = base.getTable(tableId);
let record = await input.recordAsync("Pick a record", table);

let combinedList = [];

for (let fieldName of fieldNames) {
  let fieldValue = await record.getCellValue(fieldName);
  if (fieldValue) {
    let ids = fieldValue.split(",").map(id => id.trim());
    combinedList.push(...ids);
  }
}

let uniqueCount = Array.from(new Set(combinedList)).length;

console.log(combinedList);
console.log(uniqueCount);

await table.updateRecordAsync(record.id, {
  [combinedFieldId]: uniqueCount
});

 

 

Automation Script

Make sure you properly specify your inputs, you can change "firstRollup" to whatever you want based on what you set on the sidebar.

 

 

let inputConfig = input.config();
let fieldNames = ['firstRollup', 'secondRollup', 'thirdRollup', 'fourthRollup'];

let combinedList = [];

for (let fieldName of fieldNames) {
  let arrayData = inputConfig[fieldName] || '';
  let ids = arrayData.split(',').map(id => id.trim());
  combinedList.push(...ids);
}

let uniqueCount = new Set(combinedList).size;

output.set('uniqueCount', uniqueCount);

 

 

See Solution in Thread

3 Replies 3
salvadaor
6 - Interface Innovator
6 - Interface Innovator

Hi @spancakes - I think I'm following. My first presumption would be to swap the placement of your COUNTA and ARRAYUNIQUE functions. When you say all of the IDs aren't being counted, I think it's because your arrayunique function is going off before your counta function. 

Custom builds for your needs. Reach out if you want to talk!
spancakes
5 - Automation Enthusiast
5 - Automation Enthusiast

That didn't work, that just counts everything in the array BEFORE eliminating non-uniques and nullifies the effect of ARRAYUNIQUE().

It almost seems like the ARRAYUNIQUE function is removing more than it should. I compared the expected output vs the actual output and it's removing more entries than it should be.

spancakes
5 - Automation Enthusiast
5 - Automation Enthusiast

So there's no good formula solution for this, but below is a modified script from @TheTimeSavingCo in this post.

Script Extension

Where "firstRollup" etc is your field name and OUTPUT_FIELD is the field you want to write to.

 

 

// Setup Start
let fieldNames = ["[ROLLUP 1]", "[ROLLUP 2]", "[ROLLUP 3]", "[ROLLUP 4]"];
let combinedFieldId = "[OUTPUT FIELD ID]";
// Setup End

let tableId = cursor.activeTableId;
let table = base.getTable(tableId);
let record = await input.recordAsync("Pick a record", table);

let combinedList = [];

for (let fieldName of fieldNames) {
  let fieldValue = await record.getCellValue(fieldName);
  if (fieldValue) {
    let ids = fieldValue.split(",").map(id => id.trim());
    combinedList.push(...ids);
  }
}

let uniqueCount = Array.from(new Set(combinedList)).length;

console.log(combinedList);
console.log(uniqueCount);

await table.updateRecordAsync(record.id, {
  [combinedFieldId]: uniqueCount
});

 

 

Automation Script

Make sure you properly specify your inputs, you can change "firstRollup" to whatever you want based on what you set on the sidebar.

 

 

let inputConfig = input.config();
let fieldNames = ['firstRollup', 'secondRollup', 'thirdRollup', 'fourthRollup'];

let combinedList = [];

for (let fieldName of fieldNames) {
  let arrayData = inputConfig[fieldName] || '';
  let ids = arrayData.split(',').map(id => id.trim());
  combinedList.push(...ids);
}

let uniqueCount = new Set(combinedList).size;

output.set('uniqueCount', uniqueCount);