I am trying to find the right approach to calculate the number of consecutive unattended days at course by a same student. I currently have a "participation" table. It is a junction table with: The date (date field) - The Student (link record)- The Class (link record) - Attendance (Yes / No) Every single day the teacher has the ability to say wether a student attended or not his class.
01/March/2023 - John - English lesson - Yes ✅ 02/March/2023 - John - English lesson - No ❌ 03/March/2023 - John - English lesson - No ❌ 04/March/2023 - John - English lesson - Yes ✅ etc. etc.
I wish to calculate the total of NON attended consecutive days by a student every month for his course. Example above, the answer would be 2.
Airtable doesn't do well with the idea of "consecutive" records. This gets even more tricky when a student might be enrolled in multiple classes in the same month with different absences for each class. There might be a way to approach this with rollup fields and formulas that use regular expressions, especially if you break up your junction table into two tables--an enrollment table, and an attendance table.
Depending on how data entry is performed, it might also be possible to use a system of automations and helper fields to track the number of consecutive absences. An editable number field contains the current number of consecutive absences. This editable field is set by an automation. A formula field contains the number of consecutive absences if the next attendance entry is an absence: if the current record is an absence, this formula is the number of consecutive absences plus one, otherwise it is zero. Yet another checkbox helper field identifies if a record is the most recent attendance record for a class. When a new attendance entry comes in, the automation 1. "Find records" action to search for the most recent attendance record for the same student and class with the checkbox selected. 2. "Update record" action to clear the checkbox for the previous most recent attendance record. 3. "Update record" action to set the checkbox for the triggering record to say it is now the most recent attendance record. 4. Conditional "update record" action based on whether or not the triggering record was an absence. If the triggering record was an absence, copy the formula result from the found record. If the triggering record says the student was present, set the number of consecutive absences to zero. The logic sounds a bit complicated when it is written out, but it was fun to think through.
Another option is to go the custom scripting route.