Mar 25, 2020 08:13 AM
I’ve had to do this quite a few times… my old solution was:
Now I do it all inside the rollup with the following snippet:
IF((values),
LEN(
IF(
FIND(",", ARRAYJOIN(ARRAYUNIQUE(values), ",")&"") = 1,
SUBSTITUTE(ARRAYJOIN(ARRAYUNIQUE(values), ",")&"", ",", "", 1),
IF(
FIND(",", ARRAYJOIN(ARRAYUNIQUE(values), ",")&"", LEN(ARRAYJOIN(ARRAYUNIQUE(values), ",")&"") - 1),
This file has been truncated. show original
And yes - I’m keeping it as a Gist on GitHub for easy access. I’ll be adding more of these gems as I go.
Solved! Go to Solution.
Feb 10, 2022 09:04 AM
If there might be empty values, you could use
COUNTA(ARRAYUNIQUE(values))
instead of
COUNTALL(ARRAYUNIQUE(values))
The differences between COUNTA()
and COUNTALL()
are documented in the formula field reference. COUNTALL()
counts all values, including blanks. COUNTA()
does not count blanks.
Sep 02, 2022 08:25 AM
Doesn’t work:
COUNTALL(ARRAYUNIQUE(values))
for
Rollup fields aren’t an option for other rollups, so in my case, trying to count the unique values in the rollup field won’t work. I have the above in a formula field and it only returns a 1.
Sep 02, 2022 08:48 AM
You probably need a rollup of a lookup with ARRAYFLATTEN
. It is hard to tell without a deeper dive into your schema.
Sep 22, 2022 02:44 AM
This is nice @Collin_Schwantes, I like the [^,]
For a rollup you’d need to convert it into a string and add the separators
IF(
LEN(ARRAYJOIN(ARRAYUNIQUE(values),',')&"") > 0,
LEN(REGEX_REPLACE(ARRAYJOIN(ARRAYUNIQUE(values),',')&"","[^,]",""))+1,
0)