Skip to main content
Solved

Rolling up a multi-select field correctly

P

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!!

Best answer by Justin_Barrett

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.


View original

4 replies

J

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.


ScottWorld
P
J
7 people like this
  • ScottWorld
    ScottWorld
  • P
    Process_Boost
  • J
    joohwee
  • C
    Candice_Saruwat
  • T
    Thomas_de_Beau1
  • J
    Jean_Francois_B
  • G
    gekonwi
P
Justin_Barrett wrote:

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.



Thank you Justin! For some reason it crossed my mind to use a Lookup, but I didn’t try it. It always ends up being the most obvious solution… :man_facepalming:

J
1 person likes this
  • J
    Justin_Barrett
J
Process_Boost wrote:

Thank you Justin! For some reason it crossed my mind to use a Lookup, but I didn’t try it. It always ends up being the most obvious solution… :man_facepalming:



I wouldn’t have considered it obvious in this case. It might have been obvious if that subtle difference between lookup and rollup fields were more clear and more widely known. For quite a long time, I operated on the assumption—as you might have—that the only difference between the two field types was that rollups have an aggregation formula. This deep dive I’m doing opened my eyes to this other important difference.

ScottWorld
Forum|alt.badge.img+20
  • ScottWorld
  • Brainy
  • 8711 replies
  • June 25, 2020
Justin_Barrett wrote:

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.



Wow, @Justin_Barrett! This is a huge breakthrough that even Airtable Support is unaware of! You might be the first person to have discovered this breakthrough. :grinning_face_with_big_eyes: 🙌

Scott Rose, Expert Airtable Consultant at ScottWorld.com

Reply


Most helpful members this week

Terms & Conditions