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

[Shawn_Carrie]

I really wish Airtable’s forums were more like StackOverflow…

I’m having this issue and I’m sure I faced the same thing a few months ago, can’t can’t seem to find exactly how to apply it to my use case…

[Pierre_Lombard]

Also can’t seem to figure out how to make the myriad of suggestions work for myself.

[Pierre_Lombard]

Hi Nachliel, thank you for reply! Unfortunately this is not where the issue comes up for me.

I have the following tables:

1. Student
2. Course
3. Lesson

A lesson belongs to a course, so I have a junction table:
4. Course-Lesson

A student is enrolled into a course, so I have a junction table:
5. Student-Course (which has a record for each course into which the student is enrolled.)

A student completes lessons, so I have a junction table:
6. Student-Lessons (which is populated only with records of ‘lessons completed’)

My goal:

• Firstly, on the Course table, I want to list the total number of lessons associated with a course. This I can successfully achieve with your recommendation of using ‘Count’ and a linked field.

• Then, on the table, Student-Course, I want to be able to count the total number of ‘lessons completed’… for the associated course in that record.

But as for @jezburrows (in his post on Apr '19) a Count or Rollup here only produces the number 1.

If I attempt the solutions proposed by @Justin_Barrett @Paul_Warren then it produces the total number of lessons completed for that student, on all courses they are enrolled in, not just the course associated with that record in Student-Course.

I hope all this makes sense!

Thank you so much for your assistance.

Pierre

[test_google]

A rollup dose calculations on fields of records that are connected to a specific record in the same table.

So in your case assuming this is your Student-Lesson Junction table and you want to count the sum of lessons a student attended (3 courses - two lessons per course, and Jake Baker missed one lesson of Course C on the 13th, and one lesson of Course B on the 14th):

In order to count the checked Attendance field in the Student-Course Junction you will need to link each record to the Student-Lesson Junction table records, so the Student-Course Junction table will look like this:

And now that we can access the Student-Lesson Junction we can count how much lessons were attended like this:

[Pierre_Lombard]

Thank you @test_google for taking the time to create this demo.

This works! But… it appears to me that there should be a more elegant solution, without the need for:
a.) the extra field (Attendance) and
b.) the extra records that need to be added to Student-Course every time a lesson is completed.

Some context:

I have added all lessons to the table, Lesson.

I have added all these lessons to their associated Course in Course-Lesson Junction.

And student Jo Doe is already enrolled only in Course A. (Maybe Jake Baker is enrolled in both Courses A and B.)

I currently use Zapier to ‘listen’ to my LMS for every time that a lesson is completed, and then when this event occurs it adds a record to Student-Lesson Junction.

This means that all the information required to calculate lessons completed, by student (Jo Doe) for Course-A already exists. (Remember, in my base Student-Lesson Junction is populated only with completed lessons, so the ‘attendance’ field seems superfluous.)

–
The solution that you propose would require of me to also do the following:
1.) Add a field to Student-Lesson Junction for ‘Attendance’ (in my case ‘Completed’)
2.) Set Zapier to not only add records to Student-Lesson Junction, but also to Student-Course Junction at the same time.

Is this correct? Because this seems unnecessary to me.

Would it not be more elegant to use some type of formula that aggregates the tables Student, Course and Lesson (with the help of junctions) and counts the total number of records?

I hope this makes sense. Maybe I’m completely delusional :upside_down_face:

[Mamun_123]

I,ve faced the same problem and I found a easy formula solution for it:

IF({Lookup Field}=BLANK(), 0, LEN({Lookup Field}) - LEN(SUBSTITUTE({Lookup Field}, ',', ''))+1)

[Patrik_Hellstra]

If you want to, for example, count the number of dates (or any data by the number of entries) in a lookup field, this works:

COUNTALL(ARRAYUNIQUE({your_date_field}))

[Sean_Lake1]

I'm in the same boat.  When I attempt to count the number of "entries" of the Attendees(based on a lookup from 

This field is not available to count FROM.  

[J-B-Hinterland]

Thank you for figuring this out!! Huge savior for creating my inventory checkout system.