Jul 20, 2024 02:12 PM - edited Jul 20, 2024 02:13 PM
My Rollup field has (correctly) returned this array to me ARRAYJOIN(values) by looking at linked records (4 People) and their dietary requirements (dietary preferences are determined by a multiple select field):
None,Allergy to Nuts (other than Peanuts),Gluten-free / No beef / Allergy to Nuts (other than Peanuts),None
Another formula field formats this array into an easier to read arrangement:
1 x None
1 x Allergy to Nuts (other than Peanuts)
1 x Gluten-free / No beef / Allergy to Nuts (other than Peanuts)
1 x None
What I would really like is to find a way to combine any duplicated items and show the correct count for them, such as:
2 x None
1 x Allergy to Nuts (other than Peanuts)
1 x Gluten-free / No beef / Allergy to Nuts (other than Peanuts)
Any advice on how to do this please? I want any formula to work dynamically with the content of the array, as this will make it easier to update the multiple select options without needing to rewrite any formulas that contain verbatim references to each and every multi-select text entry.
Jul 21, 2024 04:15 PM
Hiya! I think the rollup formula you may be looking for is:
ARRAYUNIQUE(values)
Let me know if this helps 🙂
Jul 22, 2024 03:08 AM
Thanks @Sistema_Aotearo That function will return the 3 unique values, but it will not show me the quantity of each of those values.
Jul 22, 2024 04:47 PM
Ah, I see! There isn't a clean way to do this natively within one formula in Airtable. You're looking to summarize your data, on the table itself, which can get tricky! Here are some options to build it manually.
Make a count field for each of the options you want to be counted. Then make a formula to join them together in an easy-to-read manner like you've described. This can get a bit messy if there are quite a few options you want counted.
Otherwise, you might have to build scripts to summarize your data or use some of the charts and graphs functionality in the interface to summarize the data.