Beta PowerSchool Plugin: Contingent Student Schedule

I’m not publishing this to the PowerSchool Exchange yet because it was a rush job and I’m not satisfied with the back-end management component of it. It’s in a “good enough” stage right now because of rapid-release requirement, so to speak.

This plugin replaces the default student schedule page in the public portal of PowerSchool and prevents viewing of schedules until the family has completed a back-to-school emergency contact update form. It seems like a common concern where schools beg for this information but parents just can’t be bothered to complete it, and there’s little for the school to do to enforce it.

Some schools have had success in making completion of that information a condition for receiving student schedules, but PowerSchool doesn’t have any built-in tools to accomplish this. So I created something instead.

There are two prerequisites:

  1. You must be using Ecollect Forms to present your emergency-contact update form. The plugin requires the form ID to work.
  2. In each school’s Years & Terms, “suppress student schedules” must be turned on. Otherwise, the entire purpose of this plugin is defeated.

PowerSchool users, feedback is welcome. I have a topic on the PSUG community forum where comments and discussion can be posted.

UPDATES: after release, a few bugs were discovered. This version is the latest that’s currently live on our server in our district.

Download the plugin here.

Student Contact Address Verification PowerSchool Plugin

PowerSchool’s student contacts permit some detailed information about contacts associated to a student, but lacks any meaningful automated validation with the “Lives With” flag. This is, of course, a natural constraint as a student can live part time with one parent and another, even if their designated home addresses don’t match (to avoid sounding like I’m criticizing PowerSchool here).

This plugin compares a student’s address to the addresses of contacts claimed to be living with the student. If the addresses don’t match within a large percentage, an alert icon appears to signal a need for review of both the student’s address in Addresses or General Demographics pages or the contacts themselves.

It could be a simple misspelling or order of the components of the address (123 Main Street East versus 123 E. Main). Or it could be a custodial arrangement, but not everyone may be aware of it.

Download from PowerSource.

Enhanced Guardian Alerts PowerSchool Plugin

Plugin #2 is out on PowerSource. Like the Medical Alerts visual enhancement, this adds some additional icons under the student header in the admin portal to visually indicate whether extra attention should be drawn to the guardian page.

This initial version will include blue icons for the existence of a restraining order and whether an adult should have limited contact with a student.

Download from PowerSource

Add Former English Learner from PowerSchool Into Illuminate

Our English Language Development department wants to track students who are considered “former English learners” in Illuminate.  The criterion is if they’ve been exited from the EL program within the last four years.  This requires a few preparations.

First, set the code within Illuminate to display the former EL status.  I just chose the next one available.  I’ll use this variable in my SQL script, which comes next.

From the cog, choose Code Management

Look for English Proficiency.  I just start typing “english,” and it appears:

Then add the new code.  In our case, plain number 6 was the next available one.  I have to remember this value:

Next, I need to edit studemo.sql on the server where the SQL extract scripts are installed.  These are the scripts that build queries from the PowerSchool database, assemble the files, and then SFTPs them to Illuminate’s server.

The column that provides English proficiency status is column 14.  For that column, I use a CASE statement:

case
   when (sc.flaglep = 1) then 3
   when (sc.lepexitdate > to_date('6/15/'||to_char(extract(year from sysdate)-4),'mm/dd/yyyy')) then 6
else null end

The prefix sc refers to an alias for our state reporting code.  PowerSchool users outside of Michigan may have different column names.  Walking through this CASE statement, here’s what I’m saying:

  • If the “is an English learner” checkbox is marked, the value is 1 in the database, so I want to return the value of 3 to send to Illuminate (note in the screen shot above of the code table, where 3 indicates “English Learner”).
  • If the EL exit date is greater than a date of June 15 of the year that’s 4 earlier than the current system date, then return a 6.
  • Otherwise, return nothing

So the possible output values will be 3, 6, or nothing.  This is proven when looking at studemo.txt in Excel:

Once uploaded to Illuminate, this value can then be used in filters for reports, student groups, and assessments: