Course Scheduling
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:
(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.
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.)
