Skip to main content
Solved

Rollup of unique values of a lookup field?

  • March 14, 2023
  • 7 replies
  • 234 views
F
Forum|alt.badge.img+3

Hi my org does school lessons, we have lessons, teachers (who host the lesson), and our educators (who deliver the lesson). These are spread across 3 tables, with lessons having links to an educator and one or more teachers. I want to calculate the number of unique teachers an educator has reached. So I created a rollup field on the educator that uses COUNTA(ARRAYUNIQUE(values)) on the lesson's teachers. This works great if each lesson only has one teacher. But some lessons have multiple teachers, I tried a workaround using a lookup field and then a formula using ARRAYUNIQUE but that didn't work, as apparently the array formulas are just for rollup operations. Is there a workaround?

Here's an airtable base demonstrating the problem: https://airtable.com/shrSGt3bifbnmwnLp

Best answer by TheTimeSavingCo

Thanks for the reply, I was trying a rollup with COUNTA(ARRAYUNIQUE(values)) which was giving 4 when it should be 5, but switching it to ARRAYUNIQUE(values) seems to give an accurate list: "Teacher 1, Teacher 2, Teacher 3, Teacher 4, Teacher 5"

BUT I want to count the number of teachers, and when I try to use this formula for counting the number of commas, it gives me 2 instead of 5


LEN({List of Unique Teachers}&"") - LEN(SUBSTITUTE({List of Unique Teachers}&"",",","")) + 1

I created a formula with just {List of Unique Teachers}&"" and it looks like this: "Teacher 1Teacher 2Teacher 3Teacher 4, Teacher 5"

So despite looking like a string, "List of Unique Teachers" is something else behind the scenes. Any ideas how to approach this?

If you've got automations to spare you might want to just:
1. Add a linked field to your "Educators" table to the "Teachers" table
2. Add a lookup field to grab all the teachers this educator has ever interacted with
3. Add an automation that triggers when the lookup field is updated, and pastes the lookup field values into the linked field to the "Teachers" table

This will automatically generate a unique list for you:

Link to base

I've banged my head against this wall for awhile now and couldn't figure out a formula based solution sorry

> I was trying a rollup with COUNTA(ARRAYUNIQUE(values)) which was giving 4 when it should be 5, but switching it to ARRAYUNIQUE(values) seems to give an accurate list: "Teacher 1, Teacher 2, Teacher 3, Teacher 4, Teacher 5"
One thing to note here is that I think that this might break if you have the following:

M

7 replies

Nico_De_Ceulaer
Forum|alt.badge.img+7
  • Nico_De_Ceulaer
  • Participating Frequently
  • March 14, 2023

Airtable can be quirky in lookup fields.
The lookup field with multiple elements (e.g. "Teacher List" in "Educator" in your example base) is in fact a string. It is not an array field any more.
The solution is to use a rollup field instead, and perform the uniqueness filter as part of the rollup setup.
So in table Educators, create a new rollup field (called 'Unique teachers')
- Field in this table: Lessons
- Lesson's table field you want to rollup: Teacher
- Aggregation formula: ARRAYUNIQUE(values)

The resulting field should show unique teachers.

Irene_Pak1
F
Forum|alt.badge.img+3
  • flux
  • Author
  • New Participant
  • March 14, 2023

Airtable can be quirky in lookup fields.
The lookup field with multiple elements (e.g. "Teacher List" in "Educator" in your example base) is in fact a string. It is not an array field any more.
The solution is to use a rollup field instead, and perform the uniqueness filter as part of the rollup setup.
So in table Educators, create a new rollup field (called 'Unique teachers')
- Field in this table: Lessons
- Lesson's table field you want to rollup: Teacher
- Aggregation formula: ARRAYUNIQUE(values)

The resulting field should show unique teachers.


Thanks for the reply, I was trying a rollup with COUNTA(ARRAYUNIQUE(values)) which was giving 4 when it should be 5, but switching it to ARRAYUNIQUE(values) seems to give an accurate list: "Teacher 1, Teacher 2, Teacher 3, Teacher 4, Teacher 5"

BUT I want to count the number of teachers, and when I try to use this formula for counting the number of commas, it gives me 2 instead of 5


LEN({List of Unique Teachers}&"") - LEN(SUBSTITUTE({List of Unique Teachers}&"",",","")) + 1

I created a formula with just {List of Unique Teachers}&"" and it looks like this: "Teacher 1Teacher 2Teacher 3Teacher 4, Teacher 5"

So despite looking like a string, "List of Unique Teachers" is something else behind the scenes. Any ideas how to approach this?
TheTimeSavingCo
Forum|alt.badge.img+31

Thanks for the reply, I was trying a rollup with COUNTA(ARRAYUNIQUE(values)) which was giving 4 when it should be 5, but switching it to ARRAYUNIQUE(values) seems to give an accurate list: "Teacher 1, Teacher 2, Teacher 3, Teacher 4, Teacher 5"

BUT I want to count the number of teachers, and when I try to use this formula for counting the number of commas, it gives me 2 instead of 5


LEN({List of Unique Teachers}&"") - LEN(SUBSTITUTE({List of Unique Teachers}&"",",","")) + 1

I created a formula with just {List of Unique Teachers}&"" and it looks like this: "Teacher 1Teacher 2Teacher 3Teacher 4, Teacher 5"

So despite looking like a string, "List of Unique Teachers" is something else behind the scenes. Any ideas how to approach this?

If you've got automations to spare you might want to just:
1. Add a linked field to your "Educators" table to the "Teachers" table
2. Add a lookup field to grab all the teachers this educator has ever interacted with
3. Add an automation that triggers when the lookup field is updated, and pastes the lookup field values into the linked field to the "Teachers" table

This will automatically generate a unique list for you:

Link to base

I've banged my head against this wall for awhile now and couldn't figure out a formula based solution sorry

> I was trying a rollup with COUNTA(ARRAYUNIQUE(values)) which was giving 4 when it should be 5, but switching it to ARRAYUNIQUE(values) seems to give an accurate list: "Teacher 1, Teacher 2, Teacher 3, Teacher 4, Teacher 5"
One thing to note here is that I think that this might break if you have the following:

Adam, Airtable Consultant - thetimesaving.company
Nico_De_Ceulaer
F
F
Forum|alt.badge.img+3
  • flux
  • Author
  • New Participant
  • March 15, 2023

If you've got automations to spare you might want to just:
1. Add a linked field to your "Educators" table to the "Teachers" table
2. Add a lookup field to grab all the teachers this educator has ever interacted with
3. Add an automation that triggers when the lookup field is updated, and pastes the lookup field values into the linked field to the "Teachers" table

This will automatically generate a unique list for you:

Link to base

I've banged my head against this wall for awhile now and couldn't figure out a formula based solution sorry

> I was trying a rollup with COUNTA(ARRAYUNIQUE(values)) which was giving 4 when it should be 5, but switching it to ARRAYUNIQUE(values) seems to give an accurate list: "Teacher 1, Teacher 2, Teacher 3, Teacher 4, Teacher 5"
One thing to note here is that I think that this might break if you have the following:


Thank you so much for looking into it, I too spent an hour trying different formulas because the correct string is RIGHT THERE. But I assume although it's showing "Teacher 1, Teacher 2, Teacher 3, Teacher 4, Teacher 5" it's actually ["Teacher 1", "Teacher 2", "Teacher 3", ["Teacher 4", "Teacher 5"]]. I will try an automation but this has to run on 3000 records so I'm a little nervous about it.

And by that last part do you mean that the COUNTA(ARRAYUNIQUE(values)) breaks if you have multiple teachers per lesson?

TheTimeSavingCo
Forum|alt.badge.img+31

Thank you so much for looking into it, I too spent an hour trying different formulas because the correct string is RIGHT THERE. But I assume although it's showing "Teacher 1, Teacher 2, Teacher 3, Teacher 4, Teacher 5" it's actually ["Teacher 1", "Teacher 2", "Teacher 3", ["Teacher 4", "Teacher 5"]]. I will try an automation but this has to run on 3000 records so I'm a little nervous about it.

And by that last part do you mean that the COUNTA(ARRAYUNIQUE(values)) breaks if you have multiple teachers per lesson?


> I will try an automation but this has to run on 3000 records so I'm a little nervous about it.
Ah, for existing records just copy and paste the values in manually?  Click on the "Teacher" lookup field header to select the entire column, copy, paste it into the "Teachers" linked field?

The automations would only be needed for when you create new "Lessons" records

> And by that last part do you mean that the COUNTA(ARRAYUNIQUE(values)) breaks if you have multiple teachers per lesson?
Yeah, I wasn't able to get it to give me the results I expected there

Adam, Airtable Consultant - thetimesaving.company
M
Forum|alt.badge.img+7

I currently use the roll up field with unique array function for that, but that gives me a plain text string with commas.

It is one solution, but it is not visually appealing and not as easily identifiable.

We are a beverage manufacutrer. We make and sell, soda, juice, cider, wine and beer - We separate customers by Alc & non-alc.

I have a SKU source with colour coded categories for each beverage. The intricacies are many here, but all I ask for is a unique option for lookup fields so identifying data is easily visible (category being one of them). I have automations to spare, but it's not a sustainable solution and it makes the base too reliant on me.

Nico_De_Ceulaer
Forum|alt.badge.img+7
  • Nico_De_Ceulaer
  • Participating Frequently
  • July 10, 2024

If you've got automations to spare you might want to just:
1. Add a linked field to your "Educators" table to the "Teachers" table
2. Add a lookup field to grab all the teachers this educator has ever interacted with
3. Add an automation that triggers when the lookup field is updated, and pastes the lookup field values into the linked field to the "Teachers" table

This will automatically generate a unique list for you:

Link to base

I've banged my head against this wall for awhile now and couldn't figure out a formula based solution sorry

> I was trying a rollup with COUNTA(ARRAYUNIQUE(values)) which was giving 4 when it should be 5, but switching it to ARRAYUNIQUE(values) seems to give an accurate list: "Teacher 1, Teacher 2, Teacher 3, Teacher 4, Teacher 5"
One thing to note here is that I think that this might break if you have the following:


thank you for the provided solution, but I would like to point out that this (to me) is not a solution but a workaround at best.

If I need to implement this elaborate automation workaround for each instance where I need this solution, I don't feel like I'm practicing no-code no more. I was hoping the airtable team would stay aware of this missing functionality. By marking this question resolved, I'm afraid they consider this problem solved.

Sorry if this sounds like a rant, it probably is.

Terms & ConditionsAccessibility statement