Rollup Array Unique?

[Richard_Foxwort]

I have a table that collects user reviews, and includes a multi-select field as one of the elements for their reviews

I want to aggregate the data from this table to generate average ratings etc.

I wan to generate an aggregated summary of the multi-select field content, so that the summary field will contain a single instance of each element in multi select fields belonging to the various reviews.

I’ve tried using a Rollup field with this formula : ARRAYUNIQUE(values) but I get repeated instances of values that appear in more than one of my original reviews.

What’s the right way to distill this down so I see only 1 instance of each multi-select option in my aggregated record?

[kuovonne]

ScottWorld:

Holy cow, really?!? That is totally nuts!!! :scream:

I agree. It is nuts. But that has been my experience. If yoU can get it to work otherwise, please share!

[ScottWorld]

I just tested this, and I was able to get it to work by creating a lookup field from scratch. So no need to convert a rollup field to a lookup field.

[ScottWorld]

Although I did notice new odd behavior. If you convert a rollup field to a lookup field, it shows the values in black & white text. If you create a lookup field from scratch, it shows each value with its original multi-select color.

[Kamille_Parks]

ScottWorld:

Unfortunately, Airtable’s Rollup Array functions don’t work properly on multi-select fields (even though multi-select fields are supposed to be arrays). As far as I’m concerned, this is a bug in Airtable

The bug isn’t that Rollup functions don’t work for multiselects, its that Rollup functions are available in formula fields. The Formula Field Reference specifically says that the array functions only work in Rollup fields.

Airtable Support

Formula field reference

Formulas may involve functions, numeric operations, logical operations, and text operations that operate on fields. For an overview of formula fields, please refer to the Guide to Formula, Lookup, ...

Rollup functions should be reworked to be able to work for multiselects because that would be a good feature to have, but its not really an actual bug.

[ScottWorld]

Kamille_Parks:

The bug isn’t that Rollup functions don’t work for multiselects, its that Rollup functions are available in formula fields. The Formula Field Reference specifically says that the array functions only work in Rollup fields.

It says that “array functions can only be used in rollup fields or when the input field is a lookup.”

When using a multi-select field in a rollup field, that IS using a rollup field, which is exactly what they are telling us to do. So we’re doing what they’re telling us to do, but it doesn’t work.

I just reached out to Airtable Support on this issue, and they just confirmed with me that this is a known bug with rollup fields, and they do not have any information on when this will be fixed. However, they recommended that people use the workarounds that we listed in this thread.

[Kamille_Parks]

That’s my bad, I misread this thread. I thought they were using an array function in a formula field pointed at a multiselect, which shouldn’t work be design.

[kuovonne]

ScottWorld:

I just tested this, and I was able to get it to work by creating a lookup field from scratch.

That was not my experience. I will test when I have access to a computer again.

ScottWorld:

If you convert a rollup field to a lookup field, it shows the values in black & white text.

This matches my experience. I was only able to get the unique multi-select options when using a formula field pointing to a lookup that is in back & white text, not pills.

[Richard_Foxwort]

Thank you very much @ScottWorld @kuovonne @Kamille_Parks - once again you have absolutely nailed the problem for me.

Can I just say that despite my many gripes with Airtable - currently including the insane lack of any reasonable scaling/upgrade path in their ‘product ladder’ - the amazing quality speed and generosity of you and many others in this community forum is one of the main reasons my team continues to use Airtable heavily. If only Airtable themselves were as attuned to their users as you all are to fellow forum members.

Your work here is very much appreciated :pray:

[Silver5866]

I'm having a similar problem, but with the Single Select. Some items are linked to multiple records from Table 2, so a single item in Table 1 might technically be linked to more than one of the Single Select options. 

So I tried the steps you mentioned (I looked, & that's the last thing I tried before looking it up, oddly enough), but my formula field is still showing duplicate answers. The formula field is: ArrayUnique({My Lookup Field})

The Single Select field currently only has like 4-5 options. And the lookup field shows the results, including duplicates (as expected). But the formula field isn't doing anything except showing the same results as a list instead of showing each in a little bubble like in Multiple Select or Single Select fields. I'd really love a way to get the list cleaned up to exclude duplicates.

[Solomon12345]

Hi. I tried this and I still have repeated instances. I also have the same issue when it's looking up a single select field.