How to Automatically Replicate Student Lists Across Multiple Dates in an Attendance Table?

Hello,

One way to approach this would be to create an intermediate table "Monthly course":

This table's objective is to set the list of students who will attend a course for a given month.

Field "Month" is a date type field with value set on the first day of the considered month.

Whe then add field to link this table with tables "Course" and "Student".

Primary key is a formula:

Then, attendance table is linked to this intermediate table:

Field "Student" in this table is a lookup field to the intermediate table "Monthly course" and hence displays list of students who applied for the course on a given month.

Of course you still need, for each new date, to select the "Monthly course" manually.

It is possible to take it a step further with an automation, so that you just have to set a date and a course, automation would then find the correct "monthly course" record to link with.

Regards,

Pascal

Hmm in order to automate this easily I think you'll need the following tables

1. Classes
  - Each record represents one class, linked to "Classes <> Dates" and "Students"
2. Classes <> Dates
  - Each record represents one class with one date, i.e. 5/8/24, 7/8/24 etc like in your screenshot, and is linked to "Classes" and "Students"
3. Students
4. Attendance
  - Each record represents a single Student for a single Classes <> Dates record

Once we have that, we link all the appropriate Student records to a single "Class" record, and we'd create a lookup field in "Classes <> Dates" to display the students that are going for this class. 

We'd then use an automation that:
1. Triggers from the "Classes <> Dates" table, perhaps from a checkbox field
2. Uses a "Repeating group", and uses the lookup field for the Students going for this class as a list: https://support.airtable.com/docs/repeating-groups-of-automation-actions
  - Creates one record per linked student in the "Attendance" table, linked to the triggering "Classes <> Dates" record

Hi Pascal,

Thank you for your detailed suggestion! I appreciate the idea of creating an intermediate "Monthly Course" table to manage the list of students for each course per month. It seems like this approach could help maintain a consistent student list across all the class dates.

However, I have a concern about this method:

Data Normalization and Attendance Tracking: A key priority for us is to maintain a structure where there is a separate row for each student per class date. This is essential to ensure easy and accurate tracking of attendance with checkboxes for each student. The method you suggested seems to consolidate the students under a single "Monthly Course" record, which may complicate individual attendance records and make it challenging to perform detailed statistics or reporting later on.

Since my initial post, I managed to create a script that allows me to copy the student names to all subsequent dates of the month, which has been quite helpful in maintaining the desired structure. If anyone is interested, I’d be happy to share the script!

I appreciate your input and would love to hear any further suggestions you might have!

Best regards,
Federico

Hi Adam,

Thank you for your suggestion! I appreciate the approach you've outlined with the intermediate "Classes <> Dates" table to handle student attendance automation.

If the same students always attended every class, your solution would work well. However, issues arise when a new student joins or someone comes to make up a missed class. By requiring the administrative staff to create a record in the "Classes <> Dates" table every time a new student arrives, and then triggering the automation to generate the attendance records, the process could become cumbersome. This might not be ideal in situations where quick registration is needed, such as when a student shows up unexpectedly or when a new student joins a course (who wasn't accounted for at the beginning of the month when the associations were planned).

I think it might be more straightforward to handle everything directly in the "Attendance" table, as it already represents a combination of Classes, Dates, Students, and a checkbox for attendance. This approach could reduce the number of tables needed and simplify the workflow for the administrative team by allowing them to create or edit attendance records directly without needing to go through an intermediate table.

Meanwhile, the "Classes <> Dates" table contains similar information (Classes, Dates, and Students) but without the checkbox for attendance, making it somewhat redundant for our purposes.

Additionally, it's important to note that Airtable only supports strictly binary relationships, and these relationships are not transitive. If Airtable had transitive relationships, I would love your solution because it would automatically pull in the students from "Classes <> Dates" into the "Attendance" table (which effectively combines "Classes, Dates, Students," and a checkbox for attendance) without requiring any automation.

I also wanted to share that I managed to set up the automation using a script. The basic idea is that I enter the students for the first date of the month, and then press a button called "Duplicate All," which loops through the student list and adds them one by one to the subsequent dates for the month. I initially had some trouble getting it to work, but I finally succeeded.

Additionally, I created another script and a button labeled "Duplicate a Student" for cases when a new student joins mid-month. This allows me to add the student to the first class of the month and automatically duplicate their attendance record for the subsequent classes.

If anyone is interested in either of these scripts, I'd be happy to share them!

Looking forward to your thoughts!

Best regards,
Federico