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?
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.