Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Mar 11, 2019 08:20 PM
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
Apr 14, 2019 03:13 PM
Thanks to @Mac for figuring this one out. For anybody with a similar issue, it required a Rollup using SUM(values).
Oct 11, 2019 09:33 AM
There is a ‘COUNT’ field type that does this, without using formulas. I expect this is faster too.
Oct 15, 2019 01:20 PM
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.
Jan 13, 2020 09:14 AM
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!
Feb 25, 2020 11:48 PM
hey @jezburrows, just wanted to follow up on this. How did you ultimately count the number of records in a lookup?
cc:@mac
Feb 26, 2020 08:00 AM
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!
Apr 30, 2020 11:25 AM
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.
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.
May 01, 2020 11:08 AM
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.
May 01, 2020 01:27 PM
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!!
May 01, 2020 07:01 PM
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: