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.
Jul 15, 2022 04:50 PM
I am running into this same issue and wondering if anyone has found a good solution.
My current hack is:
CONCATENATE(
ARRAYJOIN(ARRAYUNIQUE([Array A]),“,”),
“,”,
ARRAYJOIN(ARRAYUNIQUE([Array B]),“,”))
But now I’m left with a string and cannot do Count/other rollups on this list. It also does not checking for common elements between Array A and Array B.
Jul 15, 2022 05:26 PM
Airtable has absolutely no shortage of bugs when it comes to arrays, rollup fields, lookup fields, multi select fields, and much more. I think I’ve developed a dent in my head from all the times per day I bang my head against the wall when using Airtable.
I have no idea if my tips in this thread will help or not, which is referring to a multi-select field instead of a rollup field, but maybe there are some gems in there that will help solve the problems outlined in this thread as well:
Jul 17, 2022 01:24 AM
I’m hesitant to cast such a wide net and call all of these behaviors “bugs.” To me bugs are cases where a feature is documented and intended to work one way but it works a completely different way, or just flat-out breaks and doesn’t work at all. The things you’re referring to feel more like idiosyncrasies to me. There may be inconsistencies across field types, but that doesn’t make them bugs. If you understand the rules for how each field operates, things are easier to set up. The problem is in figuring out those rules because they’re not all documented.
For example, lookup fields most often return arrays when queried by a formula, but not always. This inconsistency alone led me to create a table to document as much as I could figure out about lookup fields and how they pass data collected from linked records: Airtable - Airtable Field Reference
Another issue is with linked record fields themselves. Because the primary field values from multiple linked records will be fed to a formula field as a comma-separated list of items—which is the same thing that lookup fields do—it’s often assumed that the formula field is being fed an array. However, it’s actually being fed a string with those separating commas baked in. Inconsistent? Definitely. Annoying? Sure. A bug? I don’t think so.
@William_Greenbaum I find it hard to provide a single “good solution” to the original question because each situation is likely to be slightly different. Some are working with rolled up dates, others with rolled up strings or numbers. The problem is that some users just say “I have a rollup field” without talking about the data that rollup field is receiving, and how many linked records are involved. If that rollup field is receiving data that has been previously looked up or rolled up or even processed by a formula, that complicates things even further. In the end, knowing as much detail as possible about all of the fields involved helps a lot when trying to work out a solution for a specific use case.
Jul 17, 2022 01:53 AM
Airtable Support has responded to many of my emails regarding these field types as “known issues” (their words) that currently require “workarounds” (their words).
Whether that implies “bug” or “idiosyncrasy” is a matter of debate, but yes, the bigger problem is that these issues aren’t officially documented by Airtable. Airtable Support used to make updates to their support articles based on user feedback (through the form at the bottom of each support article), but they confirmed with me that they haven’t updated any articles based on user feedback in over a year now. This is disturbing to me that nobody is committed to that task anymore, particularly since I’ve submitted at least 25 pieces of feedback since then and I’m just one person. I’m sure that many other people have submitted feedback as well.
Jul 19, 2022 12:25 AM
Hi William, I’ve put something together here that uses the scripting block that you may find useful
Some caveats up front:
I realize that this compiles a linked field and not a rollup like you wanted, but figured this worked as a proof of concept that this is doable via a script extension, and you should be able to view the code as well.
This is pretty easily translatable to rollup fields I reckon, but without knowing more about your specific case I can’t comment much. If you’re interested in this solution this let me know and I’ll see what I can whip up for you
Sep 17, 2022 03:17 PM
GENIUS! This solved my issue. I had multiple rollups from multiple tables, but this worked perfectly to condense everything down to a simple non-duplicated list of those interactions. Thank you so much!
Dec 07, 2022 05:37 AM
This is great, and hugely beneficial to me and my team. And you mention that it's possible to modify it to run in an automation instead, however I am unable to do this myself. Can't find the proper documentation for it unfortunately. How would one retrofit it to an automation?
Dec 08, 2022 01:43 AM
Hi Anton, I've updated the original base with an automation and script and you should be able to copy it from there
Let me know if you have any issues!
Dec 08, 2022 02:04 AM
Yes! Works perfectly, thank you SO much Adam!
Dec 21, 2022 07:52 AM
This solved an issue I was having too, but I have a simple addon request I believe. I think I am getting an error when my second array is empty, which is most of the time. What would be a suggestion to remedy this? I was thinking an "if" statement that checks if Array#2 exists and stops compiling the script there if Array#2 doesn't exist. I haven't learned enough in JavaScript to make this myself yet.