After two years of periodic discussion with the academic office, we are about to launch a new online course request form. Up to this point, a number of obstacles prevented us from moving forward:
- This is a critical school function that everyone uses. We had to get the user interface right.
- Only once we moved to Education Edge 7 (last spring) did it become possible to pull the current course list dynamically.
- Our registrar was concerned about losing valuable face-to-face discussions between students and advisors.
- The academic office didn’t want to be the guinea pig for student online registration (I am reading between the lines).
Two years ago, it would probably have taken a month to write this script. This time, I wrote about 80% of it in a day! Granted, I did work at home, almost completely free from interruptions that normally make development work nearly impossible during the school day. The result is a tidy little script whose user interface hides much of the complicated work going on behind the scenes.
Data is written to a mySQL database, from which the registrar can produce export files for import into a FileMaker database she uses to review requests before sending them to Blackbaud for scheduling. The MSSQL query that produces the course lists is especially cool. I reproduce it here in case you will find it useful:
$query = "SELECT DISTINCT
EA7COURSES.COURSEID,
EA7COURSES.COURSENAME,
EA7ATTRIBUTETYPES.DESCRIPTION AS ATTRIBUTE,
TABLEENTRIES_2.DESCRIPTION AS DEPARTMENT
FROM
EA7COURSERESTRICTIONS,
EA7ACADEMICYEARS,
EA7COURSERESTRICTIONSSTARTTERMS,
EA7TERMS,
TABLEENTRIES,
EA7COURSEGRADELEVELS,
TABLEENTRIES AS TABLEENTRIES_1,
EA7COURSEFILTERS,
FILTERVALUES7,
TABLEENTRIES AS TABLEENTRIES_2,
EA7COURSES
LEFT OUTER JOIN EA7COURSEATTRIBUTES ON EA7COURSES.EA7COURSESID = EA7COURSEATTRIBUTES.PARENTID
LEFT OUTER JOIN EA7ATTRIBUTETYPES ON EA7COURSEATTRIBUTES.ATTRIBUTETYPESID = EA7ATTRIBUTETYPES.ATTRIBUTETYPESID
WHERE
EA7COURSES.EA7COURSESID = EA7COURSERESTRICTIONS.EA7COURSESID AND
EA7COURSERESTRICTIONS.EA7ACADEMICYEARSID = EA7ACADEMICYEARS.EA7ACADEMICYEARSID AND
EA7ACADEMICYEARS.DESCRIPTION = '$currentacademicyear' AND
EA7COURSERESTRICTIONS.EA7COURSERESTRICTIONSID = EA7COURSERESTRICTIONSSTARTTERMS.EA7COURSERESTRICTIONSID AND
EA7COURSERESTRICTIONSSTARTTERMS.EA7TERMSID = EA7TERMS.EA7TERMSID AND
EA7TERMS.TERMID = TABLEENTRIES.TABLEENTRIESID AND
TABLEENTRIES.DESCRIPTION='$currentsemester' AND
EA7COURSES.EA7COURSESID = EA7COURSEGRADELEVELS.EA7COURSESID AND
EA7COURSEGRADELEVELS.GRADELEVEL = TABLEENTRIES_1.TABLEENTRIESID AND
TABLEENTRIES_1.DESCRIPTION='$student{'GRADELEVEL'}' AND
EA7COURSES.EA7COURSESID = EA7COURSEFILTERS.PARENTID AND
EA7COURSEFILTERS.FILTERSID = FILTERVALUES7.PARENTID AND
FILTERVALUES7.FILTERIDVALUE1 = TABLEENTRIES_2.TABLEENTRIESID AND
FILTERVALUES7.RECORDTYPE = '1098'";
The Departments designation for a course is stored in a strange place with a ton of other attributes, hence the “RECORDTYPE=’1098′ condition. We include COURSEATTRIBUTES as an optional join because we store the “signature required” course option there.
We will pilot this script with a small number of users next week and then hopefully roll it out to everyone in mid-April.
Richard Kassissieh is Assistant Head of School for Academics and Strategic Initiatives at