Skip to main content

I'm working with a database structure that has three tables: Publications, Artworks, and Individuals. I'm running into an issue with a Rollup field and hoping someone can help me find a solution.

My Table Structure:

  • Artworks table: Has a field that references one or more Individuals
  • Publications table: Has a field that references one or more Artworks
  • Publications table: I want to create a Rollup field that shows all Individuals associated with the Artworks in each Publication

The Problem:

When I use a Rollup field with an ARRAYUNIQUE() formula to get the individuals associated with the artworks, I'm getting unexpected results.

For example, if a Publication contains 10 Artworks, and each Artwork has 1, 2, or 3 associated Individuals (sometimes different ones), the Rollup field returns each combination of individuals rather than each individual individually.

What I Want:

I want the Rollup to return each unique Individual associated with any of the Artworks in the Publication, not the combinations.

What I've Tried:

Using ARRAYUNIQUE() in the Rollup formula, but this seems to treat each combination of individuals as a unique entity rather than breaking them down to individual records.

Has anyone encountered this issue before? Is there a way to modify the Rollup formula to flatten the results and show each Individual separately?

Any suggestions would be greatly appreciated!

Thanks in advance for your help.

​@Lea_Castonguay 

Yes, this is an extremely tricky one to solve, and I feel like this is a bug in Airtable.

I’m not 100% sure if this will work for you, but I’m hoping that you can solve it by taking the following steps:

  1. In your Artworks table, it sounds like you have a linked record field that is linking to all of your individuals.

    For some reason, Airtable doesn’t like doing “unique value rollups” with linked record fields, so you’ll need to create a lookup field instead.

    So, in your Artworks table, you’ll want to create a lookup field that looks up the Individuals based on the Individuals linked record field.

    When creating your lookup field, just choose your primary field in the Individuals table.

    Yes, you will end up with the exact same values in both your lookup field and your linked record field. That’s why this solution is so strange.
     
  2. Then, in your Publications table, change your rollup field to look at the Artwork table’s lookup field (instead of the linked record field), and use this formula for your rollup field: 
    ARRAYUNIQUE(ARRAYFLATTEN(values))

 

I’m not 100% sure if this will actually work, but I believe that this will solve the problem for you.

Hope this helps!

If you have a budget and you’d like to hire the best Airtable consultant to help you with this or anything else that is Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

Hey ​@Lea_Castonguay,

Unless I’m missing smth, I don’t think you actually need to arrayflatten the values in this specific case (might be useful for other reasons).

For a more visual representation of Scott’s answer above, I recorded this brief YouTube video. Hope it helps!
 


Feel free to reach out if you have any other question. I’d be happy to help out.

Mike, Consultant @ Automatic Nation 

Terms & Conditions