Count the entries in a lookup field?

You should be able to use a roll-up field and COUNTALL to grab the number of linked records.

@Mac Forgive me if I’m misunderstanding, but I don’t want to reference fields on another table—I just want to count the number of entries in a Lookup field on the same sheet.

@jezburrows so this isn’t what you’re looking for?

https://airtable.com/shrmfrZgcN825AH4P

I’d love to include a link to the specific table I’m talking about, or even include a screengrab, but for some asinine reason I’m not allowed to.

I want to simply count the number of entries in a Lookup field that which collects all the roles belonging to a particular writer. This table is the only place where these roles are collected together by writer, so using a Rollup field to reference another table won’t work.

Ah, looks like I’m able to post stuff now. Here’s the table:

I just want to count all the entries in the ‘Roles’ field.

Jez,

Would you be willing to share a copy of your base through a private link?

I can copy your base into my workspace and see if I can get it to work for you.

Hi @jezburrows,

What is the formula currently in ‘Role Count’?

It looks to me that using COUNTALL({Roles}) should get you what you’re looking for.

Hey @Loic_Sanchez,

Sadly COUNTALL({Roles}) just lists the number 1 in every single cell instead of how many individual roles there are in the Roles lookup column.

@jezburrows - can you share your base or explain how the roles lookup is configured (or where it is coming from)? I tried to mock your table:

In my case, Roles is a field in the Movies table and is shown in this table as a lookup. Count of Roles then works using:

COUNTALL(Roles)

But…my lookup is displayed as a comma-separated list/array - yours shows individual “pills” for each role, so you must have this set up in a different way. If we can understand this, might be able to help.

JB

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

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

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.

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!

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

cc:@mac

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!

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.

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.

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

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

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:

@Justin_Barrett, You are a champion among men, thank you!! This worked perfectly. I love your comment " 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." This happens so much, I have over 50 fields which are hidden in all views and some with over 90! Haha, thanks a million!

Okay so I’ve read over and over this thread trying to discern the solution to my (rather urgent) problem and I’m still stumped.

I made a tiny version of my (very large) base tracking descriptive data about an archival radio collection, with just a handful of records. This is an Editor-level access invite link: https://airtable.com/invite/l?inviteId=invedrpRqDjTbNJiM&inviteToken=276a551d2a2e59db96920a363477c39e56ccb1c7827c0cdf9ab300a269edfd83

‘Programs’ - individual radio programs

‘Subject(s)’ - Linked field. Programs contain multiple subjects.

‘Theme(s)’ - Linked field. Subjects are connected to one or more Themes.

I need a field in the Theme(s) table displaying HOW MANY UNIQUE PROGRAMS HAVE BEEN CONNECTED TO THAT THEME, VIA THEIR ASSIGNED SUBJECTS. The reason this is important is because as I keep curating this collection, I can just assign a SUBJECT to a theme, and all the programs with that subject automatically inherit the correct Theme(s). Otherwise I’d have to assign Themes individually to 5,000+ programs, which is laboriously prohibitive.

I tried creating a Count field in Subject(s) which displays how many programs have that Subject assigned, then created a Lookup field in Theme(s) displaying that count, but it showed a list of count values separated by commas. Then I tried creating another Rollup column in Theme(s), linking to the Subject(s) table, with the aggregation formula SUM(ARRAYUNIQUE(values)). This did not work either.

Does anyone know the answer to this?? We’ve got a whole mess of curators looking at a read-only table of all Themes in the collection, and I need that count to work!

Hello Stephanie!

Welcome to the Airtable community. It is an awesome place :grinning_face_with_big_eyes:

I’ve taken a stab at it. I had to implement @Justin_Barrett’s suggestion. Create a lookup field in your subjects table to lookup the linked program’s names. I call this field: “Linked Programs - Look up”. Then create a roll-up field in the Themes table which does a look up on the linked subjects and aggregates values from the “Linked Programs - Look up” with ArrayJoin(ArrayUnique)). I call this field “Linked Programs”

Finally, create a formula to count the length of the rollup output:

IF({Linked Programs}, LEN({Linked Programs}) - LEN(SUBSTITUTE({Linked Programs}, ‘,’, ‘’))+1)

I hope this helps! Here is a link to an edited version of your base. For some reason, I couldn’t edit the link you sent, so I had to duplicate it: https://airtable.com/invite/l?inviteId=invQYgG9iIOYm2u39&inviteToken=3c7e7853cf62c44c1892b97f69faacb46fa5ec58b175278247f2dab87e451dc8

@Paul_Warren You just saved me!! I consider myself an advanced user of Airtable, but may have to reconsider the term ‘advanced’ in light of this group’s ingenuity.

I still don’t quite understand how your final formula works, but I’m glad it does and it helped me out a lot.

In case you’re curious, here’s the (read only) link displaying the implemented change. Now all Themes display a proper count of matching programs. FWIW, the ‘URL: Linked Programs’ column displays a read-only link to the programs matching each theme . https://airtable.com/shrJMBvdPPsaWuBB0

Here’s the breakdown. First, it looks to see if {Linked Programs} contains anything.

IF({Linked Programs}, ...

If that’s true, it takes the length of that field’s contents, subtracts that same length after replacing all commas with empty strings, then adds one. In short, it’s giving you a number that’s one greater than the number of commas separating the items. If you have one comma, it’s between two items; if you have five commas, they’re separating six items; etc.

This is the clearest explanation I’ve gotten so far. Thank you!!