Running into great difficulties trying to create a script for school administration

[Daniel_Jones]

I wonder if anyone can point me to the right direction. I’m tasked to create a base for simple school administration for a small NPO. The use-case is quite simple:

• a Program contains multiple classes
• a Class contains multiple students
• a Student may attend more than one programs

Based on the above processes, I’ve created a base with four tables: Student, Program, Class, and Attendance.

• the Student Table lists all of the students, and each student record would also link to multiple Program records, multiple Class records, and multiple Attendance records
• the Program Table lists all of the programs, and each program record would also link to multiple Class records and the students records
• the Class Table lists all of the classes, and each class record would also link to its parent program record and students
• and the Attendance Table is a junction table between Class Table and the Student Table

With the above set-up, I’m able to create all of the views needed for my colleagues, such as:

• programs and classes listing
• student roster
• an attendance view for teachers showing their students’ attendance records
• an attendance view for student showing the attendance records for classes they are attending

The base is working as expected. However, because all of the records are manually added, and we have over 80 programs and 800 students per year, so we are running into issues where it takes too much time to properly create all of the records (especially since we only have two FTE), so I’m tasked to create a script to automate some of the record creations.

Specifically, we have all of the programs and classes information filled in already. We want to use the script so that when we select a student record and a program record, then the script will:

• automatically add the student to all of the classes under the selected program
• automatically create the attendance records between the student and the classes

The problem is that I’m not a programmer, so I’m having difficulties understanding the examples provided by AirTable, and I’m also finding the AirScript.dev examples too hard for me to understand as well, so I’m running into all kinds of issues.

To begin the script, I assume that I have to fetch all of the tables since the data are spread across all four tables(?):

let programsTable = base.getTable('Programs');
let classesTable = base.getTable('Classes');
let studentsTable = base.getTable('Students');
let attendanceTable = base.getTable('Attendance');

Then I assume that the script will ask my colleague to select who the student is and which program to assign the student into(?):

let student = await input.recordAsync('Select the student', studentsTable);
let program = await input.recordAsync('Select the program', programsTable);

Since I have these two info, I assume that the next step is to fetch all of the classes (children) that are linked to the Program (parent). However, this is where I keep getting stuck at. AirScript has a post that talks about the Filter method, so I assume that I can fetch the Courses table, and filter it based on the “program”:

let classesQuery = await classesTable.selectRecordsAsync();
let filteredClassesRecords = classesQuery.records.filter(class => {
  return class.getCellValue('Program Name').includes(program.getCellValue(Program Name')
})

And then I assume that the above steps will give me all of the classes that belongs to the selected program, so I can start populating the Student table with (this student will be taking class A, class B, class C, etc.):

for (let record of filteredClassesRecords.records) {
  await classTable.createRecordAsync({
    "Class Name": record.getCellValue("Class Name"),
    "Program Name": record.getCellValue("Program Name"),
    "Student Name": student.getCellValue("ID")
})

By now, you’ve probably guessed it correctly that I don’t really know what I’m doing. A student record contains multiple programs, multiple classes, and multiple attendance, but the above for-loop (if it works at all) will create duplicate student records per class. So I very appreciate if anyone would point me to the right direction on how I should approach for the solution, or if there are any script examples that I may look at. Any help will be greatly appreciated!

[JonathanBowen]

Hi @Daniel_Jones - there might be some base design optimisations here that can simplify things for you. If I understand correctly:

• A student enrolls in one or more programs
• A program contains one or more classes

So the link between students and classes does not need to be made specifically, but is a link through programs:

So, on the classes table, the “enrolled” students are a lookup from the programs table. By virtue of being enrolled in the mathematics program, we know that Jim and Jenny should be in both the mathematics classes.

Now, to create the attendance records, we can script against the classes table. Something like this will do the trick:

let classesT = base.getTable('Classes');
let classes = await classesT.selectRecordsAsync();
let attendancesT = base.getTable('Attendances');

// for each record on the classes table
for (let eachClass of classes.records) {
    // for each student in the enrolled array
    for (let student of eachClass.getCellValue('Enrolled')) {
        // create a class attendance record
        await attendancesT.createRecordAsync({
            'Class': [{id: eachClass.id}],
            'Student': [{id: student.id}]
        })
    }
}

You would need to create an attendances table with linked records to the Class and Student like this:

This script is running against the full classes table, but you might only want to run it against a specific class using a script button. In this case, you can do await input.recordAsync as you have shown above to pick a specific class.

[Daniel_Jones]

Hi Jonathan,
OMG, you are a genius! I’m able to modify it so that it asks my colleagues to select the student record and the program record, and it will then create the attendance for the selected student in the particular program. Thank you so much!

let classesT = base.getTable('Classes');
let classes = await classesT.selectRecordsAsync();
let attendancesT = base.getTable('Attendances');

// I assume we need these two tables for the records selection below
let studentT = base.getTable('Students');
let programsT = base.getTable('Programs');

// The script will ask my colleagues to select which student and program to create the attendance records from
let studentName = await input.recordAsync('Select the student', studentT);
let programName = await input.recordAsync('Select the program', programsT);

// for each record on the classes table
for (let eachClass of classes.records) {
    // for each student in the enrolled array
    for (let student of eachClass.getCellValue('Enrolled')) {
        // I want to create the attendance record only if the above studentName record ID matches the student name record in the current loop), and the above programName should also match the Program Name in the current loop
        if ((student.id===studentName.id)&&(eachClass.getCellValueAsString('Programs')===programName.getCellValueAsString('Name'))) {
            await attendancesT.createRecordAsync({
                'Class': [{id: eachClass.id}],
                'Student': [{id: student.id}]
            })
        }
    }
}

I have a slightly different question. For example, if I want to create a “Create Attendance Records” button and have this field created inside the “Students” table. Then when my colleagues click this button, I want it to run the above script. How should I modify the code so that it automatically picks up the “current student record”?

Thank you so much for you help!