Hi @Amanda_Livingston,
There are a few different ways you could set this up, but I think the simplest that should work for your use-case is to have, as you say, a Roster table and a Practices table.
-
Roster: 1 record per team member; primary field is usually the person’s name
-
Practices: 1 record per scheduled practice; primary field could be the date
Next, create a field in your Practices table that is a “Link to another record”, and link it to the Roster table, allowing multiple linked records. This allows you to attach the record of a team member to the record of a practice date, and you can do so for each team member in attendance for each practice date.
You’ll be able to see the names of each team member from the Roster that you have linked to a Practice record. And in your Roster table, you’ll be able to see the date of each Practice a team member has attended.
You can summarize monthly attendance per team member by following these general steps:
- In the Practices table, create a “Formula” field called
{Month}, and give it a formula something like this:
MONTH({Name of You Date Field})
This will extract the month as a numeral from the date you entered for that practice session.
- In your Roster table, create a “Rollup” field called “October Attendance” (I’m starting with current month, but you can use whatever months you need); point the Rollup field at the Practices table, the
{Month} field, select the “Only include linked records where…” filter and set it up Where Month = 10, and finally, give it this aggregation formula:
SUM(values)
This will sum up all instances where you have linked the current Team Member to a Practice Session that took place in the month of October.
- Repeat the steps above for each month you need
There’s a lot more you can do to continue to expand on the functionality and information this setup can give you – this is just a basic start. I encourage you (and Airtable encourages you) to play around with formulas, rollups, record links, new field types, etc. to see what you can do to meet your needs and goals. Mistakes are easy to fix in Airtable, and exploration is quite rewarding.
