I am creating a participation tracker that I can use for an upcoming project.
The concept: There are 10 different groups, and each group can have 3-10 members. One “leadership” group contains at least one member from each of the other groups, so theoretically each member can belong to up to 2 groups. I have used a many-to-many relationship to link members to groups.
Here is what I have:
A table that lists all members with their group(s)(& other pertinent info)
A table that lists all groups with their members (& other pertinent info)
A table that lists all meetings with the members that attended and their group(s) as a lookup field
A table that lists all emails and phone calls with the member they’re with and their group(s) as a lookup field
What I need:
identify how many times a group has participated in some way (meeting, email, or phone call)
identify which groups did not attend each meeting
identify how many meetings a group has attended
A group “attends” a meeting by having at least one member show up. As you can see, individual member participation doesn’t matter as long as a group has at least one member in attendance, but we only get a list of names that attended a meeting and not their associated group, hence needing Airtable to help track.
identify how many times a group has participated in some way (meeting, email, or phone call)
Found in the “Groups” table, under “# Emails / Calls”
Since each email / call is linked to a person, in the “Person” table, we create a “Count” field to count the number of emails / calls
In the “Groups” table, we do a rollup of that, and sum it together, giving you the # Emails and Calls
identify which groups did not attend each meeting
Found in the “Meetings” table, under “Groups Rollup”
Since each meeting has the people who attended it as linked records, we do a rollup of the of the “Groups” field from the “Persons” table, with “ArrayUnique” as the aggregation formula
identify how many meetings a group has attended
Found in the “Groups” table under “# Meetings”
This one is trickier. Airtable’s “ArrayUnique” aggregation formula treats multiple linked records in a cell as a string,
Consider: Two “People” records, who have attended “Meeting 1, Meeting 2”, and “Meeting 1, Meeting 3” respectively
On using “ArrayUnique”, one would expect the result to be “Meeting 1, Meeting 2, Meeting 3”. Unfortunately, as it’s treated as a string, one ends up with “Meeting 1, Meeting 2, Meeting 1, Meeting 3”
And so to handle this, we have to use an automation that will look through the “Meetings” table and find all the records that have the group’s name in the “Groups Rollup” field. We then use the “Length” value of the result to find out how many meetings that group went to
To trigger it, we create a field called “Meetings - Rollup” that rolls up all the meetings in the “People” table, and set the automation’s trigger to “When record updated” and set it to watch the “Meetings - Rollup” table