Combine Multiple Rollup Arrays into One

[spancakes]

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))

[salvadaor]

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. 

[spancakes]

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 · Accepted solution]

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);