Count the entries in a lookup field?

[jezburrows]

Hey there,

Is there some way to count the number of entries in a Lookup field in the same table?

I have a Lookup field called ‘Roles’—it references another field on the same sheet called ‘Movies’ and spits out a list of acting credits associated with those movies. I need to count how many credits there are, but the ‘Roles’ field doesn’t appear as an option in the Count drop-down. Is there some other way to do this? A formula maybe? Any help appreciated! Thanks!

Jez

[jezburrows]

Thanks to @Mac for figuring this one out. For anybody with a similar issue, it required a Rollup using SUM(values).

[David3]

There is a ‘COUNT’ field type that does this, without using formulas. I expect this is faster too.

[Justin_Barrett]

The Count field type only works with Link fields, to count the number of linked records. It won’t let you choose a Lookup field as a source to count.

[Cheyenne_Maddox]

Hi @jezburrows. I’m having the same problem and have tried using the Rollup feature with SUM(values) but cannot get it to work properly. Can you share your full Rollup formula? Thanks!

[Jon_Shay]

hey @jezburrows, just wanted to follow up on this. How did you ultimately count the number of records in a lookup?

cc:@mac

[kuovonne]

It looks like there was some confusion in this thread regarding counting linked records versus counting records in a lookup. There may also be some confusion in where to place the formula/function, as functions can be used in formula fields, rollup fields, and the summary bar.

---

Suppose you have an [Actors] table that lists one actor per row. It has a {Roles} field that contains links to multiple records in a [Roles] table.

You want a count of all the roles that an actor has played. Create a {Role Count} rollup field in the [Actors] table. In the rollup, select {Roles} as the linked record field and use the COUNTALL aggregation function.

---

Now suppose you also have a different table [My Friends] that lists one friend per row. It has a {Favorite Actors} field that contains links to multiple records in the [Actors] table.

You want a count of all the roles across all the favorite actors. Create a {Role Count of Favorite Actors} rollup field in the [My Friends] table. In the rollup, select {Favorite Actors} as the linked record field, and {Role Count} as the field in the [Actors] table to aggregate. Use the SUM aggregation function since you are adding up the count for all the individual actors.

---

Now suppose you have another table [Awards] with one award per row, and each award is granted to a single actor. There is an {Actor} field that is a link to a single record in the [Actors] table.

For each winner, you want both a list of all the roles and a count of the roles. Use two lookup fields: one for the {Roles} field and another for the {Role Count} field, both of which appear in the [Actors] table. Do not do a lookup of the {Roles} and try to do a count based on that lookup.

---

Now the table setup that I’ve described above does not quite match the screen shots of the original poster. His table setup does not show the relationships between roles and movies.

Another way to design the database would be to have tables for [Movies], [Roles], and [Actors]. Then there would be an additional table [Roles in Movies] that would join a role with a movie. This separate join table is necessary because different actors can play the same role, and the same role can appear in multiple movies. Finally, you would link the actor to records in [Roles in Movies].

You would no longer need to link actors to specific movies. You could use lookups and rollups to see all of the roles and movies for an actor, including doing counts of roles, unique roles, movies, and unique movies.

---

Hope this helps!

[Paul_Warren]

I am having a similar problem, except it is possible to the same record appear multiple times in the lookup field. I need to count the unique values within the look-up not within the individual records being looked up as this may also lead to double counting.

When I use ArrayUnique(values) it still returns duplicated, because each record has a slightly different combination of linked records.

• To correlate to the example above, imagine you want to count the number of movies all of the actors that won a certain award have been in.
• You have a linked field in movies which links to all the actors in that movie. (“Starring” field)
• Actors table automatically will have a linked records table. Lets call this (“Starred In”)
• You also have a linked field in awards which connects all of the actors who have received that award. (“Awardees”)
• Next you have a look-up field in the Awards table that uses the “Awardees” field to lookup the “Starred In” field from the Actors table.

This will lead to a collection of all of the movies all of the winners of that award have been in. However, if any of these actors have been in the same movie, there will be duplicates in this look up. How do I get a count of only unique values? The number of INDIVIDUAL movies that have actors which have won that award at some point.

This is honestly so far off from my actual use case, but the process is identical.

[Justin_Barrett]

I believe that the problem you’re running into is due to having nested arrays. Here’s a test that I put together using your movie example. I’ve got three tables: [Actors], [Movies], and [Awards]. Here’s how they look, including test links:

On the Awards table, I made a Rollup field named {Movies} using ARRAYUNIQUE(values), which gives me the duplicated movies as you described:

On the surface, it appears that each movie is a separate entry in a single array. However, if I replace the aggregation formula with ARRAYJOIN(values, " ❌ "), this is what comes back:

This spotlights the real problem: ARRAYUNIQUE(values) in this case is comparing arrays, not the individual items in those arrays.

At first I thought that the ARRAYFLATTEN() function would solve the nesting issue. The way I read it, it’s supposed to take nested arrays and return a single array. However, it’s not working as I expect it should. When I change the aggregation formula to this:

ARRAYUNIQUE(ARRAYFLATTEN(VALUES))

…the output is the same as using ARRAYUNIQUE() on its own. I’m going to write this up and submit it to Airtable support and see what they say.

[Paul_Warren1]

Justin,

Thank you for your clear effort! Yes, that is precisely the issue and the methods I tried without success. Please keep me updated!

Thank you!!

[Justin_Barrett]

Okay, I did a bit more digging, and found a solution in this post:

Going mad trying to avoid duplicates with ARRAYUNIQUE Ask the community

A-ha! Found it! I’m guessing you have things organized something along the lines of my Filmshoot Demo base, where {Name} is the primary field in the Kit table, and you’re performing a Rollup from Dates to Scenes aggregating on the Scenes->Kit linked record. If you look closely at your failing ARRAYUNIQUE call, you’ll notice the response isn’t Drone, Drone, Crane but actually is Drone, Drone,Crane That is, you’ve rolled up an array with two unique values, Drone and Drone,Crane. Presumably t…

It appears my earlier assumption was not correct. It’s not a nested array issue. It’s an issue of array data already being converted into a string. For example, the first “Movie 1, Movie 3” entry in my joined example above is a string, not a string representation of an array, which is why ARRAYFLATTEN() isn’t working: there’s nothing to flatten.

Here’s the solution. It may seem a little odd, but it works. In the [Actors] table, I added a Lookup field to pull in the movie names. (It might seem redundant because we can already see those in the linked records, but this works, so just hide the field once you’re done.)

In the [Awards] table, roll up this field using the ARRAYUNIQUE(values) aggregation function, and you’ve got no duplicates in the movie names: