New Script: Course Requests

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:

  1. This is a critical school function that everyone uses. We had to get the user interface right.
  2. Only once we moved to Education Edge 7 (last spring) did it become possible to pull the current course list dynamically.
  3. Our registrar was concerned about losing valuable face-to-face discussions between students and advisors.
  4. 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.

course requests image

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.

Comments are closed.