Help

Course Scheduling

1095 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Laura_Sargent
4 - Data Explorer
4 - Data Explorer

I’m looking for a way to create a calendar for our courses in airtable. I have our courses loaded in to the database with the start and end date but I need to be able to tell it certain things like, this course meets every Monday AND Wednesday. Is there an easy way to do this while also telling Airtable that it will occur every week until the given end date.

4 Replies 4

Hi @Laura_Sargent – welcome to the AT community!

I wouldn’t say there is an easy way, no. The way I have handled this in the past is through a Scripting Block App Extension. Which means you’ll need to write (or have somebody write) some JavaScript to make this happen. There may be a simpler way using some other Extension, like the Batch Update extension or something… but I have not experimented with that.

I can provide you with an example that is similar to what you are doing to at least give you a starting point to work from, and to give you some idea of the kind of thing you are looking at needing to build. The example script I am providing aims to create lesson records that are plotted out evenly across the school year, though, not class meeting days. This means you won’t be able to just copy-paste my script. You’ll have to write your own that implements a similar idea, but in the way you need it done.

I have a table of student-courses with these key fields:
CleanShot 2022-09-13 at 11.59.52
(In my example here, the Courses are in a separate table, and in this table they are joined with a Student, which is why you see the Course as a linked record here)

So this defines an instance of each course for a particular school year (2022-2023), and the days this instance of the course meets on. My script doesn’t use the meeting days field – instead, my script is using the “Total Assignments” field to create assignment due dates for the classes that are spaced out across the school year.

In the School Years table, we have defined some key dates for our Script to work with when plotting out the schedule, and each year when we create the new school year record, we provide the values for those key dates.
CleanShot 2022-09-13 at 11.55.22

The script below will take those dates into account when creating lesson records that are spread evenly across the school year (from start date to stop date), but never on school break weeks. You can do something similar to create course meeting records on specific days (using a similar “Meeting Days” field). Also of note, this script is pulling in an external JavaScript library called moment.js to make working with dates easier… so a lot of the date-oriented code here relies on the presence of that library).

// @ts-nocheck
const jsSnippets = await base.getTable("js").selectRecordsAsync({ fields: ["Code"]})

// import moment.js
const momentJSCode = jsSnippets.records[0].getCellValue("Code")
eval(momentJSCode)

// get student-class resources
const studentClassesTable = base.getTable("Student-Classes")
const classesCurrentYearView = studentClassesTable.getView("Current Year No Assignments Created")
const currentYearQuery = await classesCurrentYearView.selectRecordsAsync()
const currentYearClasses = currentYearQuery.records
const currentYearSubject = studentClassesTable.getField("Subject")
const assignmentsTable = base.getTable("Assignments")

// get school year resources
const schoolYearCurrentYearView = base.getTable("School Years").getView("Current Year")
const schoolYearCurrentYearQuery = await schoolYearCurrentYearView.selectRecordsAsync()
const schoolYearCurrentYearRecord = schoolYearCurrentYearQuery.records[0]

const startDate = moment(schoolYearCurrentYearRecord.getCellValue("School Year Start Date"))
const endDate = moment(schoolYearCurrentYearRecord.getCellValue("School Year End Date"))
const thanksgivingStart = moment(schoolYearCurrentYearRecord.getCellValue("Thanksgiving Break Start")).subtract(1, 'day')
const thanksgivingEnd = moment(schoolYearCurrentYearRecord.getCellValue("Thankgsgiving Break End")).add(1, 'day')
const xmasStart = moment(schoolYearCurrentYearRecord.getCellValue("Xmas Break Start")).subtract(1, 'day')
const xmasEnd = moment(schoolYearCurrentYearRecord.getCellValue("Xmas Break End")).add(1, 'day')
const holyWeekStart = moment(schoolYearCurrentYearRecord.getCellValue("Holy Week Start")).subtract(1, 'day')
const holyWeekEnd = moment(schoolYearCurrentYearRecord.getCellValue("Holy Week End")).add(1, 'day')


let lessons = []
let academicYearCalendar = []
let iterationDate = moment(startDate)
do {
    if (!isWeekend(iterationDate) && !isBreak(iterationDate)) {
        academicYearCalendar.push(moment(iterationDate))
    } 
    iterationDate.add(1, 'day')
} while (iterationDate.isSameOrBefore(endDate))

currentYearClasses.forEach(studentClass => {
    const numberOfLessons = studentClass.getCellValue("Total Lessons")
    const lessonsPerWeek = Math.ceil(numberOfLessons / 33)
    let i=1
    switch (lessonsPerWeek) {
        case 1:
            createOnePerWeekAssignments(numberOfLessons, studentClass)
            break;
        case 2: 
            createTwoPerWeekAssignments(numberOfLessons, studentClass)
            break;
        case 3:
            createThreePerWeekAssignments(numberOfLessons, studentClass) 
            break;
        case 4:
            createFourPerWeekAssignments(numberOfLessons, studentClass)
            break;
        case 5:
            createFivePerWeekAssignments(numberOfLessons, studentClass)
            break;
        case 6:
            createSixPerWeekAssignements(numberOfLessons, studentClass)
            break;
    }
})


output.inspect(lessons)

const lessonsCreated = await batchAnd("Create", assignmentsTable, lessons)




if (lessonsCreated.length == lessons.length) {
    const currentYearClassesToUpdate = currentYearClasses.filter(cls => 
            cls.getCellValue("Total Lessons") > 0
        ).map(cls => {
        return {
            id: cls.id,
            fields: {
                "Assignments Created": true
            }
        }
    })
    batchAnd("Update", studentClassesTable, currentYearClassesToUpdate)
}





//*****************************************************
//******************* functions ***********************
//*****************************************************



function isWeekend(date) {
    let res = false 
    if (date.day() == 0 || date.day() == 6) {
        res = true
    }
    return res;
}


function isBreak(date) {
    let res = false
    if (date.isBetween(thanksgivingStart, thanksgivingEnd) ||
        date.isBetween(xmasStart, xmasEnd) ||
        date.isBetween(holyWeekStart, holyWeekEnd)
    ) {
        res = true
    }
    return res;
} 


function createOnePerWeekAssignments(numberOfLessons, studentClass) {
    const fridays = academicYearCalendar.filter(day => day.day() == 5)
    let lessonNumber = 1
    fridays.forEach(friday => {
        pushLessons(lessonNumber, studentClass, friday, numberOfLessons)
        lessonNumber++
        numberOfLessons--
    }) 

}


function createTwoPerWeekAssignments(numberOfLessons, studentClass) {
    const tuesdaysThursdays = academicYearCalendar.filter(day => day.day() == 2 || day.day() == 4)
    let lessonNumber = 1
    tuesdaysThursdays.forEach(tuesdayOrThursday => {
        pushLessons(lessonNumber, studentClass, tuesdayOrThursday, numberOfLessons)
        lessonNumber++
        numberOfLessons--
    })

}


function createThreePerWeekAssignments(numberOfLessons, studentClass) {
    const monWedFri = academicYearCalendar.filter(day => day.day() == 1 ||
    day.day() == 3 || day.day() == 5)
    let lessonNumber = 1
    monWedFri.forEach(mWF => {
        pushLessons(lessonNumber, studentClass, mWF, numberOfLessons)
        lessonNumber++
        numberOfLessons--
    })
}


function createFourPerWeekAssignments(numberOfLessons, studentClass) {
    const monTuesWedThurs = academicYearCalendar.filter(day => day.day() == 1 ||
    day.day() == 2 || day.day() == 3 || day.day() == 4)
    let lessonNumber = 1
    monTuesWedThurs.forEach(mTWR => {
        pushLessons(lessonNumber, studentClass, mTWR, numberOfLessons)
        lessonNumber++
        numberOfLessons--
    })
}


function createFivePerWeekAssignments(numberOfLessons, studentClass) {
    const allWeekdays = academicYearCalendar
    let lessonNumber = 1
    allWeekdays.forEach(weekday => {
        pushLessons(lessonNumber, studentClass, weekday, numberOfLessons)
        lessonNumber++
        numberOfLessons--
    })

}


function createSixPerWeekAssignements(numberOfLessons, studentClass) {
    const fridays = academicYearCalendar.filter(day => day.day() == 5)
    const calWithDoubleFridays = academicYearCalendar.concat(fridays)
    const sortedCalendar  = calWithDoubleFridays.sort((a,b) => a.format('YYYYMMDD') - b.format('YYYYMMDD'))
    let lessonNumber = 1
    sortedCalendar.forEach(weekday => {
        pushLessons(lessonNumber, studentClass, weekday, numberOfLessons)
        lessonNumber++
        numberOfLessons--
    })
}


function pushLessons(lessonNumber, studentClass, weekday, numberOfLessons) {
    if (numberOfLessons > 0) {
        lessons.push({
            fields: {
                "Name": `L ${lessonNumber} - ${studentClass.name}`,
                "Student Class": [studentClass],
                "Due Date": weekday.format()
            }
        })
    }
}



/*
    Use this function to perform 'Update', 'Create', or 'Delete'
    async actions on batches of records that could potentially 
    more than 50 records.

    ::PARAMETERS::
    action = string; one of 3 values:
           - 'Update' to call table.updateRecordsAsync()
           - 'Create' to call table.createRecordsAsync()
           - 'Delete' to call table.deleteRecordsAsync()

    table = Table; the table the action will be performed in

    records = Array; the records to perform the action on
            - Ensure the record objects inside the array are
            formatted properly for the action you wish to
            perform

    ::RETURNS::
    recordsActedOn = integer, array of recordId's, or null; 
                   - Update Success: integer; the number of records processed by the function
                   - Delete Success: integer; the number of records processed by the function
                   - Create Success: array; the id strings of records created by the function
                   - Failure: null;
*/
async function batchAnd(action, table, records) {
    let recordsActedOn;

    switch (action) {
        case 'Update':
            recordsActedOn = records.length;
            while (records.length > 0) {
                await table.updateRecordsAsync(records.slice(0, 50));
                records = records.slice(50);
            };
            break;
        
        case 'Create':
            recordsActedOn = [];
            while (records.length > 0) {
                let recordIds = await table.createRecordsAsync(records.slice(0, 50));
                recordsActedOn.push(...recordIds)
                records = records.slice(50);
            };
            break;

        case 'Delete':
            recordsActedOn = records.length;
            while (records.length > 0) {
                await table.deleteRecordsAsync(records.slice(0, 50));
                records = records.slice(50);
            }
            break;

        default:
            output.markdown(`**Please use either 'Update', 'Create', or 'Delete' as the "action" parameter for the "batchAnd()" function.**`);
            recordsActedOn = null;
    }
    return recordsActedOn;
}

(And for those programmers who will read this and inevitably critique and pick apart my code, yes, I know there is repetitive code in here that could by DRY’d up – I don’t care. It works. And it was written with my wife, a non-programmer, at the wheel, while I helped her with the logic.)

Laura_Sargent
4 - Data Explorer
4 - Data Explorer

Thanks Jeremy! I was hoping to use an extension or app to help speed up the process. Funny how something that seems SO simple could be so hard to come across! Before I dive in to the programmer realm I’ll hold off and see if someone knows of an easier fix.

For the record, speaking as a programmer… nothing that involves dates is ever simple :grinning_face_with_big_eyes:

Welcome to the community, @Laura_Sargent!

As @Jeremy_Oglesby mentioned, there’s no easy way to do this on the Airtable side of things. Calendaring is a challenge across all database apps.

Jeremy outlined a great solution above — including his addition of a holidays table to skip over holidays.

If you’re looking for an incredibly simple solution, I would recommend just creating all of your repeating events in Google Calendar, and then use Airtable’s Google Calendar Sync to do a one-way sync of your events from Google Calendar into Airtable.

In other words, outsource the heavy lifting of your calendar logic to Google Calendar, and then do the rest of what you need to do in Airtable:

p.s. I should also mention that your initial request could be done without Javascript programming by using Make.com, but it would be extremely time-consuming to setup. It would take me way too long to outline all the steps here, but I am available for hire as an Airtable consultant to help you with this, if you want to go down this path. Although I would recommend going down the simpler Google Calendar path, to save time & energy & headaches.