
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 08, 2022 12:33 AM
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?
Solved! Go to Solution.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 08, 2022 01:44 AM - edited May 02, 2024 10:00 AM
- Change the rollup field that you created into a lookup field instead. Lookup the multi-select field.
- Create a formula field that points to your lookup field like this: ARRAYUNIQUE({Your Lookup Field})
Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 08, 2022 01:44 AM - edited May 02, 2024 10:00 AM
- Change the rollup field that you created into a lookup field instead. Lookup the multi-select field.
- Create a formula field that points to your lookup field like this: ARRAYUNIQUE({Your Lookup Field})
Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 08, 2022 07:19 AM
I didn’t test it, but I think it’s not a bug in that case.
Rollup consider operation with array of values, but if any value is array, it receive array of arrays, or 2D-array.
It counts each different multi-choice as unique
For example, 5 users choose from 12 months multiselect
“January, February”
“January, March”
“March”
“February, March”
“February, March”
there are 4 unique answers here.
There are many ways to achieve desired summary, even dynamically supported summary table.
To remove array nesting, ARRAYFLATTEN formula used, but I don’t think it will help in that case. I think, of you create empty table, link reviews table to it and copy-paste whole multiselect field in link field (both in reviews table), it will create correct unique list with links and link count will be a number of choices for each item.
Filtering this table for some actions, which result passed to reviews table via lookup is equal to ‘having’ in SQL.
Also, pivot table app possibly doing the same or close to it.
Note that if your option contains comma(s), result will be counted as two (or more) different options in result table

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 08, 2022 07:38 AM
Right, unfortunately you can’t even get it to work with ArrayFlatten, which is what would be expected to work, because the REST API documentation refers to multi-select fields as arrays. So the array functions should work, but sadly, they don’t. It’s very disappointing.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 08, 2022 07:40 AM
I have found inconsistent behavior with flattening a rollup of multi-select values. I logged the issue with support a while back and they confirmed that it was a bug. I think there was a workaround, but I would have to dig it up.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 08, 2022 07:49 AM
Thanks for this info. I might need to email them today to nudge them about this issue. :stuck_out_tongue_winking_eye:

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 08, 2022 08:08 AM
Found the workaround.
- Create a rollup field that rolls up the multi-select field. Set the formula to
values
. Save the rollup field. - Convert the rollup field to a lookup field. Notice that the lookup will be formatted as plain text instead of the usual pill format.
- Create a new formula field that refers to the lookup field (that used to be a rollup) and use the formula
ARRAYJOIN(ARRAYUNIQUE(ARRAYFLATTEN({lookup field})), ", ")

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 08, 2022 08:10 AM
Thanks. Pretty similar to my workaround above. Mine is probably more straightforward.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 08, 2022 08:30 AM
Not just pretty similar, it is exactly the same. Notice that if you create a lookup directly (without it being a rollup first), it won’t work.
My formula has two additional optional functions that yours does not. I include ARRAYFLATTEN
in case the behavior changes it is needed in the future. I also threw in ARRAYJOIN
because I like being 100% sure of the resulting data type and knowing for sure if the commas are really there.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 08, 2022 08:47 AM
Holy cow, really?!? That is totally nuts!!! :scream:
(I’m on my phone now, so I can’t personally try this out at the moment.)
Great ideas! :grinning: :raised_hands:
