Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

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

Topic Labels: Formulas
Solved
Jump to Solution
171 2
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

1 Solution

Accepted Solutions

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!

See Solution in Thread

2 Replies 2

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!

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!