Help

Re: Rollup of unique values of a lookup field?

Solved
Jump to Solution
1388 2
cancel
Showing results for 
Search instead for 
Did you mean: 
flux
4 - Data Explorer
4 - Data Explorer

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

1 Solution

Accepted Solutions

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:

Screenshot 2023-03-15 at 12.51.37 PM.png

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:

Screenshot 2023-03-15 at 12.51.24 PM.png

Screenshot 2023-03-15 at 12.51.28 PM.png

See Solution in Thread

5 Replies 5
Nico_De_Ceulaer
5 - Automation Enthusiast
5 - Automation Enthusiast

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?

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:

Screenshot 2023-03-15 at 12.51.37 PM.png

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:

Screenshot 2023-03-15 at 12.51.24 PM.png

Screenshot 2023-03-15 at 12.51.28 PM.png

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