Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Unique array combining arrays from 2 rollup fields

6142 21
cancel
Showing results for 
Search instead for 
Did you mean: 

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?

21 Replies 21

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.

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?

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?

The Rollup fields are both referencing the same table.

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

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.

:slightly_frowning_face: Thank you for following up.

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.

I realize it’s been a little while, but I’d be interested in your solution, Enrique. I’m having same issue.

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

compile linked field

Some caveats up front:

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

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!

Yes! Works perfectly, thank you SO much Adam!

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.