Rollup to count # of linked records excluding duplicates

[Dani_Oliver]

I’m having trouble understanding why this isn’t working. Essentially what I’m trying to do is find out how many authors were published each calendar year and each fiscal year. There are multiple issues published each year and each fiscal year, and sometimes they don’t line up. Each issue links to a record for the year and FY, like so: (this is my issues table)

I have a second table for the year and fiscal year records: stats by year & fy. What I’m trying to do is a rollup that will count how many author records are connected to each year and FY. There are some authors that will occur over multiple magazine issues, so those duplicates need to be weeded out from the total number of unique authors for each year/FY.

However, when I use the COUNTALL(VALUES) rollup, it seems to only be calculating how many issues are connected to the record, rather than authors:

Here’s what I’m trying:

I’m not sure why this isn’t totaling “All authors”, which is the table field I’ve selected. I’ve tried other aggregation formulas and it all seems to be doing the same thing. When I select ARRAYUNIQUE(values), thinking I could count unique records that way, I get a list of the records, but it doesn’t weed out the duplicates:

What am I doing wrong?

[Justin_Barrett]

The various count-themed functions in a rollup field count the number of links that meet the necessary criteria for that function type. They don’t count any specific data in the target field (i.e. links). In your example, when looking at the {All authors} field and applying the condition “Where ‘All authors’ does not contain ‘The Editors’”, that result only drives whether or not that linked record in {Issues} is part of the end count.

The second attempt that you made using ARRAYUNIQUE() is closer, but presents a different problem. Even though that list you see looks like a single list (i.e. a single array), it’s actually an array of strings. When Airtable collects items from a link field, it doesn’t keep them in an array. It immediately converts them into a comma-separated string. Thankfully there’s a way around that.

In your [Issues] table, add a lookup field that retrieves the names of the authors based on the {All authors} link field. I’ll call this field {Author Names}. This lookup field will create an array. Now go back to your [stats by year & fy] table. Change the rollup to pull from {Author Names}, and set the aggregation formula to:

ARRAYUNIQUE(ARRAYFLATTEN(values))

values will be a nested array of arrays from all linked {Issues} records, which ARRAYFLATTEN() will convert into a single array. That single array can then be parsed by ARRAYUNIQUE() to return only the unique names.

[Dani_Oliver]

That’s great! Thank you! Then how would I take that rollup — ARRAYUNIQUE(ARRAYFLATTEN(values)) — and create another column in the same table that counts the # of names that appears in that rollup? (That’s my ultimate goal, here. To have a column that just tells me how many authors per year.)

[Justin_Barrett · Accepted solution]

If you want, you could add one more element to the rollup field’s aggregation formula, which will give you the count in that field:

COUNTA(ARRAYUNIQUE(ARRAYFLATTEN(values)))