Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Oct 04, 2018 09:52 AM
I have 2 rollup fields in a table that are formatted to show ARRAYUNIQUE(values). I am trying to create a formula column that combines these values in an array, and only displays unique values. I was trying to use: ARRAYUNIQUE({Rollup1},{Rollup2}), but am only getting the values from Rollup1 to show up. Help?
Oct 04, 2018 09:59 AM
I think you’ll need a field in between that concatenates the two arrays first, then your field that eliminates duplicates.
So the formula field in between would look like this:
CONCATENATE(
{Rollup 1},
", ",
{Rollup 2}
)
That should combine the two arrays from the rollups into a string that looks like an array. The ", "
in the middle is to add a comma-space after the last entry in {Rollup 1}
and before the first entry in {Rollup 2}
.
Now you can make another formula field that does this:
ARRAYUNIQUE( {Combined Rollups}, ", " )
I didn’t test that, so let me know if it doesn’t work and we can try something else.
Oct 04, 2018 10:14 AM
In the CONCATENATE field, I’m getting extra ", "s at the beginning if Rollup 1 is empty, extra ", "s at the end if Rollup 2 is empty, and if there are multiple values in a Rollup, the ", " that was separating them in the rollup is being lost in the Concatenate field.
The ARRAYUNIQUE field isn’t filtering duplicates, possibly because of the syntax in the Concatenate field. Anything else to try?
Oct 04, 2018 10:19 AM
hmm… well, the extra comma would be an easy fix, but I didn’t realize that CONCATENATE()
would strip out the commas that were separating the array values.
Ya, you got that right. It’s got to be because of the missing/extra commas.
I’ll think about this one a bit more.
Question - are the two Rollup fields referencing the same table, or different tables?
Oct 04, 2018 10:26 AM
The Rollup fields are both referencing the same table.
Oct 04, 2018 10:40 AM
:frowning: This is proving to be a lot more difficult than I thought it would be at first glance…
CONCATENATE()
behaves pretty strangely when passed an array.
Oct 04, 2018 11:00 AM
A couple more questions, @Kat_Levine…
1) What type of data is being rolled up? (strings, numbers, dates?)
2) In the table that the rollups live in, are you linking all records from the first table to a single record, or are there many records that are linked across both tables?
3) Is the final unique list the only thing you are after, or is it mandatory that the first two rollups also contain only unique values? (ie, can we shift the removal of all the duplicates to the last step, rather than removing duplicates in two steps?)
EDIT
Nevermind about the questions, Kat. No matter what I’ve tried, I can’t get Airtable to perform ARRAYUNIQUE()
outside of a Rollup field. Which means using a formula that operates after the Rollups is not an option.
I tried to think of ways to combine the data prior to the Rollup in a way that still allows the rollup to see each value as a separate piece of data, but with no luck so far.
Sorry – looks like this may not be possible.
Oct 10, 2018 06:23 AM
:slightly_frowning_face: Thank you for following up.
May 17, 2019 06:25 PM
Hi, not sure if you’re still interested in getting around this issue, but I found a way of getting this done by utilizing Zapier and using its JavaScript action to combine the rolled up arrays and basically manipulate it in any way you like and then send an array back to Airtable. It is a bit involved so If you’re still interested let me know and I’ll put together a more detailed guide.
Jul 21, 2019 07:01 PM
I realize it’s been a little while, but I’d be interested in your solution, Enrique. I’m having same issue.