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

[dondon]

Hi everyone,

I'm managing class attendance for a dance school using Airtable. Currently, I have an attendance table where I track which students attended each class on specific dates. Each class has a fixed set of students that remain constant throughout the month, and I need to automatically replicate that list of students across each class date in the month without manually copying and pasting.

Example Table Structure:

Date Class Student Present

05/08/2024	Ballet Intermediate Adults - Mon & Wed PM	Alex Johnson	[ ]
05/08/2024	Ballet Intermediate Adults - Mon & Wed PM	Sarah Brown	[ ]
05/08/2024	Ballet Intermediate Adults - Mon & Wed PM	Maria Gonzalez	[ ]
07/08/2024	Ballet Intermediate Adults - Mon & Wed PM	(Want to replicate all students here)	[ ]
12/08/2024	Ballet Intermediate Adults - Mon & Wed PM	(Want to replicate all students here)	[ ]
14/08/2024	Ballet Intermediate Adults - Mon & Wed PM	(Want to replicate all students here)	[ ]
19/08/2024	Ballet Intermediate Adults - Mon & Wed PM	(Want to replicate all students here)	[ ]
21/08/2024	Ballet Intermediate Adults - Mon & Wed PM	(Want to replicate all students here)	[ ]
26/08/2024	Ballet Intermediate Adults - Mon & Wed PM	(Want to replicate all students here)	[ ]
28/08/2024	Ballet Intermediate Adults - Mon & Wed PM	(Want to replicate all students here)	[ ]

Note: The three students listed here are just an example. In reality, I may have 20 students attending in August, and I want to avoid having to copy and paste the names of all 20 students across the 8 classes scheduled for the month.

Context:

• I have an attendance table with the following fields: "Class" (linked record), "Student" (linked record), "Date," and "Present" (checkbox).
• "Class" is linked to a table where the course name and schedule are defined (e.g., "Ballet Intermediate Adults - Mon & Wed 8:00 PM - 9:30 PM").
• "Student" is a linked record containing all the students enrolled in that class.
• The class dates for the entire month are already set up in the attendance table, but I don’t want to manually duplicate the student list for each date.

What I’m Trying to Achieve:

• At the start of the month, I enter the correct students for the first class (e.g., the class on August 5th).
• I want a solution that, either by pressing a button or through automation, can automatically copy that student list to all subsequent dates in the month for the same course/class.

Issues Encountered:

• We’ve tried implementing a script to achieve this, but it only ends up duplicating a single student instead of the entire list.
• I would like to know if there is a simpler or native Airtable method to accomplish this, whether through automations, linked records, templates, or any other technique that allows me to keep the data normalized without making the process cumbersome for the administrative team.

I would greatly appreciate any guidance or suggestions on how to approach this problem.

Thanks!

[Pascal_Gallais-]

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

[TheTimeSavingCo]

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

[dondon]

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

[dondon]

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