Re: Rolling up a multi-select field correctly
Hi,
I am having some issues with some fields that I’m rolling up.
My goal is to use an automation via Integromat to take a Rollup field from a multi-select field in Base 1 and populate that data in Base 2 as another multi-select field.
The rollup itself is generated from data in Base 1, where I have a “Posts” Table that shows various Social Media Posts. Each Post can be in multiple categories in a Multiple Select field, and each Post will link to a Report on a “Reports” Table.
On the Reports Table, I am using a Rollup field to get all of the unique Categories associated with all of the linked Posts. This works perfectly when each Post only has 1 Category attached to it, but when the Post has multiple Categories attached to it, the data doesn’t show up or send to Base 2 correctly (like shown in the first screenshot).
I have tried plenty of different things but can’t seem to get the data to populate correctly. I believe the issue has to do with Airtable reading each multi-select field as an array in it’s entirety. For example, a Post with multiple categories becomes “Category 1, Category 2” in a Rollup instead of “Category 1” and “Category 2”.
I can break it into a string and eliminate the spaces, but then I can’t pull out the unique values, like an ARRAYUNIQUE formula would do.
Is there any way to split up the data correctly so that each Multi-Select array coming through on the Reports Table (“Category 1, Category 2”) can be split up, and then a formula would be able to choose the unique values from that?
Any help or insight on this problem would be very appreciated. Thank you!!
Accepted Solutions
You’re in luck! I’ve been doing a deep dive into how Airtable treats certain fields (the results of which will be shared when I’m finished), and I’ve got a solution for you: use a lookup, not a rollup. Here’s the backstory…
When pulling from a multiple-select field, a rollup field builds its array using the string equivalent of the multiple-select field output, which is effectively an automatic ARRAYJOIN({fieldname}, ", "). The resulting string array is then processed by the rollup field’s aggregation formula. However, a lookup field pulling from that same multiple-select field treats that field as an array, extracts each item separately, and builds a single array of all items. From there, you can use ARRAYUNIQUE() in a formula field to find the unique values.
You’re in luck! I’ve been doing a deep dive into how Airtable treats certain fields (the results of which will be shared when I’m finished), and I’ve got a solution for you: use a lookup, not a rollup. Here’s the backstory…
When pulling from a multiple-select field, a rollup field builds its array using the string equivalent of the multiple-select field output, which is effectively an automatic ARRAYJOIN({fieldname}, ", "). The resulting string array is then processed by the rollup field’s aggregation formula. However, a lookup field pulling from that same multiple-select field treats that field as an array, extracts each item separately, and builds a single array of all items. From there, you can use ARRAYUNIQUE() in a formula field to find the unique values.
