Issue with Linking Payments to Attendance in Airtable
Hi!
I’m transitioning from managing a dance school in Google Sheets to Airtable and have encountered an issue when trying to link student payments with their attendance. I need the administrative staff to be able to see if a student has paid for a specific class in the corresponding period (month) when recording attendance, or if they owe for that month. In Sheets, this is incredibly easy, but in Airtable, it’s NOT (or perhaps I’m missing something, which is why I’m reaching out with the hope that’s the case).
Table Structure (simplified for this example):
- Students Table:
- Fields: Name, Contact Information, Attendance (Linked Records), Payments (Linked Records), etc.
- Classes Table:
- Fields: Class Name, Days, Schedules, Attendance (Linked Records), Payments (Linked Records), etc.
- Payments Table:
- Fields: Student (Link Record), Class (Link Record), Paid Period, Amount Paid, Payment Date, Unique Key.
- Attendance Table:
- Fields: Student (Link Record), Class (Link Record), Date, Present (Checkbox), Period, Unique Key, Amount Paid.
Problem Example:
A student, for example, "Juan Pérez," is enrolled in several classes, including "Ballet Mondays and Wednesdays" and "Contemporary Dance." Below is an example of how payments and attendance are recorded for a single period, August 2024:
Payments Table:
Student Class Period Amount Paid Payment Date Unique Key
| Juan Pérez | Ballet Mondays and Wednesdays | August 2024 | $100 | August 1st | JuanPérez-Ballet-August24 |
| Juan Pérez | Ballet Mondays and Wednesdays | August 2024 | $50 | August 5th | JuanPérez-Ballet-August24 |
| Juan Pérez | Contemporary Dance | August 2024 | $80 | August 3rd | JuanPérez-Contemporary-August24 |
Juan made two payments on different dates for the August 2024 period for his ballet classes.
Attendance Table:
Student Class Date Present Period Unique Key Amount Paid
| Juan Pérez | Ballet Mondays and Wednesdays | August 3rd, 2024 | Yes | August 2024 | JuanPérez-Ballet-August24 | $150 |
| Juan Pérez | Ballet Mondays and Wednesdays | August 5th, 2024 | Yes | August 2024 | JuanPérez-Ballet-August24 | $150 |
| Juan Pérez | Contemporary Dance | August 4th, 2024 | No | August 2024 | JuanPérez-Contemporary-August24 | $80 |
| Juan Pérez | Contemporary Dance | August 7th, 2024 | Yes | August 2024 | JuanPérez-Contemporary-August24 | $80 |
Note: In the "Amount Paid" column of the Attendance table, the sum of payments made for the specific class and period should appear. This is the column I’m struggling to create easily without using Linked Records between attendance and payments.
Difficulty:
In Google Sheets, this was solved with a simple formula like SUMIFS. For example:
=SUMIFS(Amount column in 'payments', Column of student names who paid, Reference to the student according to the row in the attendance table, Column of class names, Reference to the row with the class in the attendance table, Column of periods in the payments table, Reference to the period in the attendance table)
Conceptually, Google Sheets sums all payments that match "student," "class," and "period." You can add as many conditions as you want.
But in Airtable, I find myself having to create unique keys that combine Student, Class, and Period for both the Attendance and Payments tables, and then create automations to link payments with attendance. This has proven to be complicated and prone to errors, especially if attendance records are created after the payment is made, or if the automation failed for some reason and associated a payment with too many attendance records.
(Without linking payments and attendance using a unique key for "student-class-period," Lookups and Rollups aren’t helpful because they don’t allow me to add conditions relative to the row in the attendance table). For example, it’s easy to sum all payments for a student using a rollup, but it’s not easy to sum all payments for a student for the month I’m viewing in the attendance table and for the class I’m viewing in the attendance table. That’s not easy! (Or it might be, but I’d have to constantly change the field’s filter, and that’s not practical).
I believe that Student, Class, and Period effectively define the relationship between attendance and payments. Payments and attendance are linked because they share Student, Class, and Period.
Questions:
- Is my design correct, or could I modify and improve the design to make calculations easier?
- Is there no way to request a Rollup of the “amount paid” for those payments that match Student, Class, Period with the row in question in the attendance table? I can do a Rollup of the student’s payments or filter them by month, but they’re filtered by a single month configured in the field, and they can’t be filtered by the month corresponding to the row of attendance we’re observing.
- I can do a Rollup if I link all the attendance records (with their different dates for the month—attendance on 1/8/2024, on 3/8/2024, on 8/8/2024) with the payment, but this complicates management.
Of course, if each payment is manually linked to each attendance date for a student in a class, the level of linkage needed to summarize the information is achieved. But this is very labor-intensive manually, and I want a solution that requires less manual work. The school has hundreds of students and dozens of classes. We can’t do it manually—it would be less productive than the current system.
I managed to create an automation that links each payment with attendance. But if all the attendance records for a month aren’t created in advance, the payment becomes unlinked from the attendance, and this doesn’t seem like a robust and elegant method. I could try re-linking them or always creating attendance records before the payment. But the reality of the business’s daily operation makes this difficult to accomplish. I would like something elegant and robust. For example, with Google Sheets, it’s truly robust. The summing always works regardless of when they pay or when the attendance is recorded. And since Airtable is a real database (in Google Sheets, I have to use a lot of IMPORTRANGE and XLOOKUP to achieve something resembling related data), it should be easier.
What I want is an efficient and robust way to show in the Attendance table if the student has paid for that specific class and period without having to rely so much on complicated automations or unique keys. I imagine there must be a solution that doesn’t require automations. After all, the “Students,” “Classes,” “Payments,” and “Attendance” entities are linked together.
Thanks for your help!
