Can't Figure Out How to Get a Total of the Number of Records Via Lookup / Rollup

Question

Hi all,I am stumped but I feel like the answer is staring me in the face!  Hoping yall can help!I have a membership tracking base set up as follows:TablesPeopleEventsAttendancePeople and events are stand-alone tables.  The Attendance table basically links them with a field for People and a field for the Event.What I need is a total number of events (within a date range) to be used in calculating a minimum attendance requirement per person.Anyone have an idea how to accomplish this?  TIA!EDIT:  I know how to calculate how many events each person has attended.  What I need is the total number of events so I can do a formula something like {# Attended} / {Total Events} = % of Events Attended.  Thanks!

kuovonne · Accepted Answer

If you want to know the total number of events in the events table, I’m afraid that Airtable does not handle this use case well.

Airtable rollups can only count linked records. The only way for a record to know the total number of records in another table is to link to every record in that table.

There are some workarounds. For example, if you will have less than 100 events, you could use an automation that puts the total number of events in an editable field in the events table. Then use rollups to pull that info across the tables. But if there will be more than 100 events, you would need to use code in the automation. Also, if you delete event records, that could also cause issues.

Trigger: when record created in the events table

Action 1: find all records in the events table

Repeating group based on found records

action inside repeating group: Update the current record with the number of found records.

Hi there, thank you! I was afraid that’s why I wasn’t able to solve it. I will explore your automation suggestion - sounds interesting!

Ashley_Jackson · Answer

If you want to know the total number of events in the events table, I’m afraid that Airtable does not handle this use case well.

Airtable rollups can only count linked records. The only way for a record to know the total number of records in another table is to link to every record in that table.

There are some workarounds. For example, if you will have less than 100 events, you could use an automation that puts the total number of events in an editable field in the events table. Then use rollups to pull that info across the tables. But if there will be more than 100 events, you would need to use code in the automation. Also, if you delete event records, that could also cause issues.

Trigger: when record created in the events table

Action 1: find all records in the events table

Repeating group based on found records

action inside repeating group: Update the current record with the number of found records.

Hi there, thank you! I was afraid that’s why I wasn’t able to solve it. I will explore your automation suggestion - sounds interesting!

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded