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

For the Greater Good

I get asked every so often by those in enterprise IT why I spend my nights and weekends answering e-mails, doing custom coding, and building analytical reports for my teacher and administrator colleagues.  I get the impression from those counterparts that they’re only in IT for the 30% of the time they get to play with new technology and that the remaining 70% of the time when they have to actually do their jobs and help their colleagues out is a burden and a chore to them.

“Stupid users….” is a frequent utterance.  Maybe it’s the benefit of having been in my position and industry for over 20 years but I don’t see it that way.

I work nights and weekends because my teachers and administrators do.  If they’re on-line, that means they’re trying to finish something up for their customers: their students and their families.

If it wasn’t important to take care of some business for the kids so that the kids can continue to have the best experience in school that they can, they wouldn’t be on their laptops and tablets at home doing this work at 8, 9, 10 o’clock at night.

So when they run into problems, they come to me.

When they do, I have a choice.  I can resort to “I’ll look at it tomorrow when I’m on the clock and in the office.”  Or I can take a quick look to see what will be involved and make a judgment call over whether I can take care of it right away or lay the groundwork for “tomorrow.”

I tend to go for the latter because of that indirect but cascading effect that ultimately ends on the kids’ shoulders.  Because in my work in supporting teachers, it’s more than just grades and assignments that are affected.

Maybe a student is having a really rough time in class because of things happening at home. Without knowing it, he needs just that one little shining hope of success, like knowing he earned a perfect mark on a tough assignment, to make him believe that he’s not a complete waste of time and space like an older relative makes him think he is.

Maybe a student is struggling to comprehend the English language because her family moved here from another country just before the school year began.  She knew no one before walking into the cold, fluorescent brick building full of incomprehensible noises and colors.  And the one piece of familiarity that she can latch onto is a math equation.

Maybe a student who has autism has been in and out of disciplinary measures all year because he has difficulty expressing his frustrations and sensory overload in class, and lashes out violently as a coping mechanism.  And the one thing that can help him, his teachers, and his classmates is for his teacher to finally have access to his IEP and his accommodations list that had additional, helpful details, like “frequent breaks.”  “No sudden movements and low voice.”  “Minimal physical contact.”

None of this seems like it has direct connection with technology, but they do.  Whether it’s technology (my previous life) or data (my current life), they all touch students in some way.

If teachers are working right now at 9:30pm on a Tuesday, there’s a reason.  And if I can support that with answers or suggestions or even a game-plan when they run into problems or have questions, there’s a small chance that I can positively affect a student’s life in the long term, because that’s what we in schools are here for: the students.

This is why I do what I do, when I can. 

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:

   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:

Handy Honorware

Honorware seems to be a newer term to describe freeware that asks for donations if you like the product.  It’s otherwise not limited in use, like most major companies’ “DOWNLODE FER FREEEE” or “PHREE TRYLE!” marketing gimmicks that permit them to slap the word “free” over everything as long as one small component of use is free.

(let’s be real, though.  This “free” really isn’t)

I’ve been using a few of them for quite a long time, and I finally got up the gumption to donate some funds to the developers.  Most of you probably know of at least one of these three.  The other two may not be as familiar.

Disclaimer: I am linking to these products because I like them.  I am not receiving any kind of stipend or kickback from these product developers.  Contrary to common blog themes of “MONETIZE UR CONTENT!!!!!” it actually is possible to like a product for its own sake and promote it as such.

  • AdBlock.  While we get that many (most) Web sites now depend on ad revenue as one of their income sources, some are just ridiculously intrusive.  I’m not an advertising or marketing major, so I have neither the background nor interest in researching the effectiveness of advertisements through history, but my anecdotal observation indicates that it’s a really lousy source to rely upon for income.  As a consumer, it’s irritating so I ignore them anyway.
  • Greenshot.  Possibly the best screen-capture software ever.  Lightweight, and allows you to edit a screen-cap in its own built-in image editor.  More features than I ever expected, and more than I can adequately describe on this post.
  • Social Fixer.  My goodness, this thing is a blessing.  It’s a plug-in to Facebook that lets you tweak so many aspects of your news feed.  Annoyed by politics?  Get rid of ’em!  Sick of the Trending Stories box on the side?  GET RID OF IT.  Don’t care about where your friends Check In?  GET RID OF THOSE CHECK-INS.  It’s much more robust than expected, so non-tech folks who aren’t accustomed to spending the time to tweak settings may feel a bit intimidated by all the check boxes and options, but if you spend the initial time investment, it’s so much cleaner and less stress-inducing.

Conditional Formatting Not Applying Unless I Modify Cell Content with F2

I have a big spreadsheet fed by a data model in Excel 2016.  Several columns are numeric values and the end user wanted conditional formatting to apply colors to certain value ranges (green for scores 51-100, yellow for 41-50, and red for scores 40 and below).

When I created my first conditional formatting rule for the scores 51 and above, it highlighted everything.  Only when I updated a cell with F2 did the correct condition apply.  Naturally, I didn’t want to modify over 300 cells in at least 4 columns, especially if my model updated with new data.

After a couple of hours of searching the online world using a multitude of combinations of the words in the subject above, I discovered the problem.

Even though the numbers appeared visually as numbers and even though I set my cell formatting for those columns to numbers, the underlying model had stored them as text.

I returned to my data model and sure enough, the data type for my numeric columns were set to text.  I changed them to whole numbers, refreshed my query, and the condition applied properly.


Use Assessments to Build Student Groups

Illuminate as two main ways to build student groups: choose a common characteristic of those students as the basis or use the results of an assessment to separate out a group of kids.  But what if you need to provide an assessment to a group of students who have no common characteristic?

Or what if that common characteristic doesn’t exist in the Illuminate dataset?

For example, a question I was asked was “do kindergarteners who receive a waiver perform differently academically in later years versus those who do not receive a waiver (and therefore, start kindergarten later)?”  Another request I received was to be able to generate Illuminate answer sheets for math test-outs being administered to various students of various grades from different buildings, simply because they applied for the test to skip certain math classes.

In both of these cases, Illuminate stored no common characteristic of these students.  For the kindergarten waiver question, I could have used an unused field in student demographics to build that common feature (which I’ll discuss in a future post), but because this was an ad hoc report intended to prove a point, it wasn’t worth the extra effort to build automation around it.

The students applying to test out of certain math classes certainly had no common characteristic.

So I used an assessment itself as the tool to establish that commonality to create the student group.  Here’s how.

Create a Dummy Assessment

The dummy assessment simply consists of one question.  The answer doesn’t matter, as all we’re looking for is a way to capture the specific students we need.

The students we need are identified in our SIS, so the import file can be built out of there.  I narrow down the students who had kindergarten waivers, or applied to test out of Math 8 or pre-calculus.

Upload the Students

I reduced this CSV to two columns: StudentID and Q1.  Q1 (the “answer” to the question) simply needed to contain the same character for each record.  This was then uploaded to the dummy assessment, which produced the familiar overview:

Now we know that 403 students in the district received a waiver for kindergarten at some point.  The rest of their demographic details are stored in Illuminate already.

Create the Student Group

Creating a student group from an assessment requires generating a report first.  So let’s do that.  Clicking on either the donut or the number of students in the summary section will display the list of students:

Click Create Custom Report to create the report consisting of only these students.

From inside that new report, click Advanced then Create Student Group.

And now that student group can be used as any normal student group.  That group can be used to generate pre-filled answer sheets for particular assessments, it can be used as the basis of a trend report, or it can even be used as a focus group or intervention group.

What otherwise started as no other commonly bound group of students are now together through a few extra steps.

I Had the Best Music Class in Middle School

I realized just now that the only person I’ve told about this is my wife.  So let’s fix that.

In 7th grade, I, naturally, had to take several classes that are called “specials” now.  These are the arts, outside of general ed curriculum, like music and art.  My music class was unlike any other, and in my 18 years working K-12, I’ve never seen it again.

7th grade, 12 years old.  I learned the history of rock’n’roll.

Our music class wasn’t a bunch of bored pre-teens sitting around singing boring songs out of tune and off-key.  We weren’t playing instruments badly.  No, we were learning about the impact and influence of early jazz and big band and swing and gospel on what would ultimately become “rock and roll.”

We listened to the Beatles’ “Revolution #9” and because it was the ’80s and record players still existed, we learned about the backwards playing.  And we actually listened to it, and examined its impact on society at the time.  I learned about Led Zeppelin, The Who, The Yardbirds, Cream, Deep Purple, and Jefferson Airplane….and their evolution into Starship, since “We Built This City” was the new hit song on the radio at the time anyway.

It was not just a history of rock and roll, but a history of the ’60s and ’70s at the same time.  If we weren’t listening to records and tapes, we were watching documentaries about Woodstock (sanitized and censored to be appropriate for 12-year-olds, of course).

At the end of class, our teacher would let us play a cassette of our choice.  He encouraged each of us to bring something in to share with the class.  If nobody did, then he would play his own mix-tape of classic rock.  I, of course, would bring in my “Dare to Be Stupid” tape, and not only would my teacher laugh at Al’s parodies but he would dive deep into Al’s musicianship and songwriting skills.

He literally dissected Weird Al Yankovic.

It was the best music class I’d ever had.  And it’s something I’ve never seen since.

When Field Calculations Identify Typos in Sources

The project: aggregate Fountas & Pinnell performance results for the past three school years.

The challenge: put together the assessments from 6 grade levels over 3 years in 2 test periods per year into one table (so a total of 36 separate sources into one).

Result: something isn’t right.

I created the source table in Illuminate and downloaded it to prepare to use JasperSoft Studio Pro to create a BI analysis of performance over time.  That’s when I noticed something weird:

At the same time, I loaded the file into Excel to start a pivot chart, and saw the same thing:

Where is the 2016 academic year for 1st grade?  And what the hell is that 201 doing there?  I clearly had bad data in the source file.

So I went back to Illuminate to look at the custom report that created that data file.  I sorted by academic year to see where that 201 was coming from.

The reason this jumped out at me is because of the use of calculations and translations in the Advanced menu of the report builder.  I needed to somehow grab the grade level of the student and the academic year in order to get some kind of separation in my BI report, but that information isn’t stored within the assessment itself because of how Illuminate manages student visibility to users, depending on permissions and filters.

To get around that, I created the Grade column as a calculation, using the formula SUBSTRING(G FROM 19 FOR 1), which means “take Column G (the one titled Assessment Grade/Administration) and starting at position 19, grab 1 character.”  Because all of the assessments are named according to the same convention, that position would be the grade value (K, 1, 2, etc.).

Then, I created a second calculation column for Academic Year.  That formula was SUBSTRING (G FROM 1 FOR 4), so taking the first four characters of that same column, resulting in values of 2014, 2015, and 2016.

I wasn’t done yet, because those don’t really represent the academic year the way we need it to.  Those values are the start of the school year, when I want the end.  In comes the translation feature in the advanced menu.

I added translations for all three values, making 2014 = 2015, 2015 = 2016, and 2016 = 2017 so that at the very least, when I download the data, it’ll look right and I can do additional manipulation after that.

So when I saw the blank in Grade and 201 in Academic Year, there was something goofy going on with my calculated fields and translation.

Then I looked to the right and saw that they all shared the same assessment source.  2015-16 F&P Grade 1 Spring.  I stared at it for a moment.  Every other assessment grabbed those substring values correctly except for this one.

Which meant that, since the substring calculation for academic year was supposed to grab the first four characters, and the grade field grab the 19th character, but I was only getting the first three of the year and a blank for grade…..

Was there a space in the name of this particular assessment that I can’t see right off the bat?

I went into that assessment and opened up the information window for it, and lo and behold….

The calculations and translations were working correctly.  The calculation for the year was grabbing the first four characters, as designed.  The problem is that the source had an extra character where it shouldn’t have been.  Which caused the 19th character to be the space before the 1.

And since I didn’t build a translation rule for values of ” 201,” it didn’t translate anything for me.

Take out the space, go back to my custom report and…


So even though we don’t want bad data, bad data can still tell a story.


Cliches to Make Your IT Life Better

Two simple cliches that have improved my life over the years:

There’s gotta be an easier way.  This means that no matter what problem you’re trying to solve, you’ll probably find yourself deep in the weeds and overgrowth at some point.  That’s when you have to stop and say “there’s gotta be an easier way to do this,” because there is. You just have to find it, and finding it means reevaluating what you’re doing and what you’ve done.  What you’ve been doing is clearly not the easy way if you’re at this point.  Which leads to the next one.

Make the computer do the work.  That’s what they’re built for!  Automating repetitive tasks, which they’ll do much faster and more accurately anyway.  As OSes and software grow and advance, more automation is built in.  Learn them and take advantage of them.  If you’re doing things manually all the time, you’re doing it wrong.