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?
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.
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:
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.
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.
Hi William, I’ve put something together here that uses the scripting block that you may find useful
Some caveats up front:
The data won’t update unless you click the button. If you’ve got a Pro account, the script can be modified to run in an automation as well though, which would keep it up to date
This isn’t a formula field
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
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!
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?
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.
