Follow up on Powerschool DECODE statements

I would like to thank Roger Sprik at the PowerSchool User Group (http://tech.groups.yahoo.com/group/PSUG/)for helping me solve my problem. He did it by thinking smarter not harder.

I was on the right track with the decode statement. Here was Roger's solution:

You can use decode a little more creatively, taking advantage that you can have an "else" statement... since you only seem to need to do school 100 differently, just make the others the "else". Also take into account that you can apply decode only to the part where there is a difference, which will take the semicolons outside of the decode...

<meta equiv="Refresh"
content="1;URL=/guardian/~(decode;~(schoolid);100;schedule;home2).html">

Thanks Again

PowerTeacher Terms not showing the same grade

I had a teacher come to me with a problem. In PowerTeacher it was showing the correct grade in the S1 column, but the end of year grade (Y1) was showing as slightly lower. After doing some searching I found that the teacher had not set up the year for term weights but left it at total points. Once switched to term weights the semester grade and year grade were the same.

Powerschool and Decode Statements

Her is my latest puzzle. I have been try to nest IF statements to no avail (I don't think it can be done). In the process I discovered the DECODE statement. An example of the decode statement would be:

~(decode;~(schoolid);100;ES;200;MS;300HS)

It roughly translates to a DECODE statement in Oracle or a CASE statement in C. The equivilant IF statements would be:

IF schoolid==100
print "ES"
ELSEIF schoolid==200
print "MS"
ELSEIF schoolid==300
print "HS"

Here is my dilemma. We are preparing to open the parent portion of Powerschool at the end of the month. Currently when a parent logs on the parent portal the first time it takes them to a page where they must agree to the terms of service before they are allowed onto the parent portal. After they do this it redirects them to the Grades and Attendance page with the following HTML redirect:

<meta equiv="Refresh" content="1;URL=/guardian/home2.html">

The elementary school uses standards based report cards and in turn does not use the PowerTeacher Gradebook. There is no information there in the grade portion and I don't want parents of elementary children to even see this page. I decided to use a DECODE statement to redirect the initial page based on which school their child was in. The statement I tried was:

~(decode;~(schoolid);100;<meta equiv="Refresh" content="1;URL=/guardian/schedule.html">;200;<meta equiv="Refresh" content="1;URL=/guardian/home2.html">;300;<meta equiv="Refresh" content="1;URL=/guardian/home2.html">)

The problem was it didn't work. Powerschool keeps interpreting the semicolons in the HTML tags as the end of the block. Haven't been able to figure out how to escape the semicolons; I am going to post the problem on the PSUG. If I get an answer I will post it here.

Extracting Ethicity Fields

Spent an hour on extracting the ethnicity fields (which in our case hold passport issuance information i.e. two digit country code [CA] followed by the country name [Canada]). I was trying to find out how Powerschool populates the drop down menu for a custom page that has that has teacher passport country. In the custom HTML for the page there was a tag for the drop down:

<select name="[05]Ethnicity" special="lists.ethnicity">

The Select is pulling from the TEACHERS table [05], but I couldn't figure out how it was populating the list choices from. It turns out that it is coming from the GEN table. The code is selecting form GEN and row where CAT is equal to 'Ethnicity'.

Knowing where that data was kept, I then opened excel up and connected to the GEN table through an ODBC connection to the Powerschool database. I filtered the data where CAT = 'Ethnicity and selected the NAME and VALUE columns.. the SQL is:

SELECT GEN.NAME, GEN.VALUE, GEN.CAT

FROM PS_MGMT.GEN GEN

WHERE (GEN.CAT='ethnicity')

I was then able to dump it right into Excel

My powerschool Introduction

I guess the purpose of this is to share my experiences with my transition from Rediker's Admin + to Powerschool.

Last year I was working at Seoul Foreign School and Using Rediker's Admin + (and was not a big fan). This school year I took a new job as the network administrator at the American School of Doha. The school is in their second year of implementation, and I replaced the previous Powerschool administrator.

So far I have been very happy with using Powerschool (note to Rich Rediker: Relational databases are the way to go. People have been using them for a while. It's time to move to this "new" technology.).

I went to the Powerschool University this summer and it is a great program It probably would have been better if I had had a little experience with Powerschool before I went. I plan on attending again this summer.

I jumped in with both feet when I arrived at school, rolled over the new school year, and spent many hours on with Powerschool tech support.