Help

Re: Count the entries in a lookup field?

2446 0
cancel
Showing results for 
Search instead for 
Did you mean: 
jezburrows
5 - Automation Enthusiast
5 - Automation Enthusiast

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

48 Replies 48
Paul_Warren
8 - Airtable Astronomer
8 - Airtable Astronomer

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

Stephanie_Lynn_
4 - Data Explorer
4 - Data Explorer

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=276a551d2a2e59db96920a363477c39...

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

Paul_Warren
8 - Airtable Astronomer
8 - Airtable Astronomer

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=3c7e7853cf62c44c1892b97f69faacb...

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

Natalie_Zdan
6 - Interface Innovator
6 - Interface Innovator

@Paul_Warren @Stephanie_Lynn_Sapie
I was so excited to find your posts since it seemed like that formula would do exactly what I needed it to. When it didn’t work, I opened Stephanie’s read-only link to see if I could somehow see the formula there, but it looks like the formula isn’t working there anymore either… Says there’s a configuration area and there are no counts appearing in the column. Any idea why it might have broken? (See screenshot below)

I too want to perform a count of the number of items rolled up using the ARRAYUNIQUE(values) aggregation function. Would be very grateful for any guidance!

Formula I’m trying that won’t work, next to rollup field that uses ARRAYUNIQUE(values):
image

Broken formula from Stephanie’s read-only:
image

Good Morning Natalie!

I am not certain what is broken on Stephanie’s read-only link, but after some head-scratching I figured out the issue with the formula above. Airtable ONLY supports a specific type of quotation mark. I’m a little fuzzy on the nomenclature, but I know @ScottWorld has lodged several complaints about this frustrating issue.

Simply delete the quotation marks and type them again with your keyboard and the formula should work. I’m not sure why they are incorrect in my previous post. I think the code below should work.

IF({Sites (from Volunteering Schedule)},LEN({Sites (from Volunteering Schedule)})-LEN(SUBSTITUTE({Sites (from Volunteering Schedule)}, ',', ''))+1)

I hope that helps!

Also, this works with Look-up fields. I’m not sure how it works with a roll-up field


*EDIT to put formula in a code block, per @Justin_Barrett’s suggestion.

That is correct — quotation marks in formulas must be straight quotes, not curly quotes.

At my request, Airtable has updated this support page with this information:

They’re styled because the default behavior in this forum is to apply styling to all quotes. To display non-styled quotes, select the formula/code in the post/comment editor and apply the “preformatted text” style using this button in the toolbar: </> That will turn your text into this, which uses non-styled quotes:

IF({Sites (from Volunteering Schedule)},LEN({Sites (from Volunteering Schedule)})-LEN(SUBSTITUTE({Sites (from Volunteering Schedule)}, ',', ''))+1)
Sean_Lake1
8 - Airtable Astronomer
8 - Airtable Astronomer

For me the field I require(the lookup field) isn’t an option on count nor rollup, so how am I to get a count if it’s not an option?
image

As you can see the lookup field that I want to get a count of total number in field, isn’t an option.(Attendees_Autogenerated)
Any ideas?

Base your count field on the same linked record field that the lookup field uses.

The attendee field has multiple entries ,comma separated, so the field it’s based on wouldn’t return the same number.

Really, there’s no simple formula for this?

=LEN(TRIM({attendee}))-LEN(SUBSTITUTE(TRIM({attendee}),",",""))+1

Yup, doesn’t work nor does a rollup, nor count field as the field we’re drawing from is a lookup field. Lookup fields seem difficult to access, except for maybe array related formulas.

@Sean_Lake1

Assuming I understood the issue; try this:

IF({attendee}&'',LEN({attendee}&'')-LEN(SUBSTITUTE({attendee}&'', ',', ''))+1)

LEN() returns the lenght of a string, not an array, but by adding &’’ at the end of a field name you get arround that issue.

Absolutely, largely because many users don’t realize that a lookup field is returning an array (i.e. a collection of values), not a single item (i.e. a string, a number, etc.). The solution mentioned by @momentsgoneby80 solves this problem. By concatenating the array with an empty string, Airtable converts the array to a string, and then processing by string functions works as expected.

You cannot base a count or a rollup off of a lookup field. You need to base it off of the same linked record field that the lookup field uses, and set the same conditions. If your {Attendees_Autogenerated} field is based off the {Attendees} field, then the count or rollup field needs to also be based off of the {Attendees} field.

Also, is it a lookup of an editable field, or a lookup of a lookup? A lookup of a lookup needs to be flattened before counting. COUNTALL(ARRAYFLATTEN(values))