Need to see which group didn't have a representative attend a meeting

[Jade_Payne]

Hi there,

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.

[TheTimeSavingCo · Accepted solution]

Hi Jade, I’ve created a solution for you here

Details:

• 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

Let me know if I can help further!

[Jade_Payne]

This is amazing and so incredibly helpful! Thank you so much. I’m still early on in learning Airtable, and I’ve learned a lot from this!