Escaping an apostrophe on a PowerSchool custom page variable

Acknowledgements: Thanks to Roger Sprik on the PowerSchool forums for this tip.

I have a custom page that uses the last name field:

~([05]Last_Name)
The only problem with that is if the last name has an apostrophe in it, that can lead to unexpected stuff when used in jQuery.

Apparently, you can strip out the variable to show only the letters and numbers:

~([05]Last_Name;keep_ascii=48-57,65-90,97-122)

Using GET input to search in a PowerSchool custom page

As far as I can tell based on what scant documentation there is for PowerSchool custom pages, on trial and error, and on PowerSchool forum responses, there doesn't appear to be a way to use a not-equal-to comparison operator. Any PowerSchool power users who want to correct me—I'd love to be wrong about this.

If you want to use GET input in your PowerSchool custom page, you can, of course, create an HTML form. But how do you reference that input and then use it in a ~[tlist_sql] query only if the input exists?

First of all, to reference a GET variable, you use gpv. For example, let's say your custom page with the GET input is something like https://yourschool.powerschool.com/admin/reports/yourcustompage.html?parentlastname=wong

In that case, to reference parentlastname, you would use ~(gpv.parentlastname)

If you wanted to run a ~[tlist_sql] query only if the input exists—and, again, this is only because I can't find anything on how to use a not-equal-to comparison operator for custom pages—you would essentially use the logic of "If it's equal to blank, do nothing; otherwise, do this query."

Here's an example (you would put in your own SQL query, of course):

~[if.~(gpv.parentlastname)=]
[else]
~[tlist_sql; SELECT firstfield, secondfield, thirdfield
FROM tableyoureusing
WHERE somefield='~(gpv.parentlastname)']
~(firstfield), ~(secondfield), ~(thirdfield)
[/tlist_sql]
[/if]
So when you load the page the first time, it will see that ~(gpv.parentlastname) doesn't exist (or that it is equal to blank) and not run the ~[tlist_sql] query. After the user submits the search and the search term comes back as GET input, the page will run the query.

PowerSchool tlist_sql fails but SQL Developer query is fine

Full props to Romy from the PowerSource forums for helping me sleuth a problem with a query working just fine in SQL Developer but then failing (with a blank-loaded page) in tlist_sql on a PowerSchool custom page.

Basically, a colon, even within quotation marks, messes up the query within tlist_sql, so I had to use the HTML ASCII code for it instead.

Worked a charm.

For sqlReports4, you should use these ASCII codes without quotation marks around them.

Converting seconds to time in PowerSchool custom pages

I know someone's going to jump in here and tell me that PowerSchool's custom pages have no problems with SQL statements but are just less forgiving of syntactical errors than SQL Developer is, but I couldn't get any of the convert-seconds-to-time standard SQL approaches to work in a PowerSchool custom page using tlist_sql, even when those solutions worked fine in SQL Developer. Here are some examples.

Eventually, the solution that did end up working best was taking a trunc / mod approach in combination with lpad to get some times together. Here is what it looks like as part of the select statement:

SELECT TO_CHAR(l.logindate, 'MM/DD/YYYY') logindate,
lpad(trunc(l.logintime/3600), 2, '00') loginhour,
lpad(trunc(mod(l.logintime, 3600)/60), 2, '00') loginminutes,
lpad(mod(l.logintime, 60), 2, '00') loginseconds
FROM logins l
WHERE l.logindate > sysdate -21
ORDER BY l.logindate, l.logintime
It takes a raw seconds field and then converts it to corresponding hours, minutes, and seconds.

PowerSchool Custom Pages Exporting Tables to .csv Using jQuery

Someone on Stack Overflow made a jsfiddle to export existing HTML tables to .csv using jQuery.

It does not, however, translate directly to PowerSchool custom pages, because PowerSchool has its own implementation of jQuery that's a little funky.

I've done a little playing around with it, and this tweaked code seems to work.

For the table, make sure it's enclosed with a div with an id (for this example, we're calling it dvData):

<div id="dvData">
<table class="tableToGrid">
</table>
</div>

Include an actual clickable link to do the download:

<p><a href="#" class="export">Export table data into Excel</a> </p>

Put the table-to-CSV code into your jQuery document ready section (or create the section if it doesn't exist yet:

<script>
$j(document).ready(function(){

// Adapted from http://jsfiddle.net/terryyounghk/KPEGU/ but tweaked for PowerSchool's jQuery implementation
function exportTableToCSV($table, filename) {

// Original code
//var $rows = $table.find('tr:has(td)'),
// My tweak so we can get the header row
var $rows = $table.find('tr'),

// Temporary delimiter characters unlikely to be typed by keyboard
// This is to avoid accidentally splitting the actual contents
tmpColDelim = String.fromCharCode(11), // vertical tab character
tmpRowDelim = String.fromCharCode(0), // null character

// actual delimiter characters for CSV format
colDelim = '","',
rowDelim = '"\r\n"',

// Grab text from table into CSV formatted string
csv = '"' + $rows.map(function (i, row) {
var $row = $j(row),
$cols = $row.find('td');

// This one line from https://gist.github.com/adilapapaya/9787842
if(!$cols.length) $cols = $row.find('th');

return $cols.map(function (j, col) {
var $col = $j(col),
text = $col.text();

return text.replace(/"/g, '""'); // escape double quotes

}).get().join(tmpColDelim);

}).get().join(tmpRowDelim)
.split(tmpRowDelim).join(rowDelim)
.split(tmpColDelim).join(colDelim) + '"',

// Data URI
csvData = 'data:application/csv;charset=utf-8,' + encodeURIComponent(csv);

$j(this)
.attr({
'download': filename,
'href': csvData,
'target': '_blank'
});
}

// This must be a hyperlink
$j(".export").on('click', function (event) {
// CSV
exportTableToCSV.apply(this, [$j('#dvData'), 'export.csv']);

// IF CSV, don't do event.preventDefault() or return false
// We actually need this to be a typical hyperlink
});

// End document ready
});
</script>

You should then have a link at the bottom of the report that allows users to download the existing table to .csv.

I have a full example on GitHub that just pulls all enrolled students, but you can tweak the SQL and headers, obviously.

th tags?

I made a couple of tweaks after extensive trial and error and managed a messy hack that gets the headers in but also may provide some encoding issues (not sure where those are coming from—the HTML generated by tlist_sql should be the same as the regular HTML), as well as a random extra empty row after the header row

custompageencodingissue01 custompageencodingissue02 As you can see, there's a random "space" generated in front of each header title (the space isn't there on the HTML page), and then in Excel that translates to Â.

Granted, this happened to only one of my custom pages, so your mileage may vary. Even for that page, it's more useful to end users than no header row at all, and it's far easier for them to delete an empty row in Excel than to highlight and copy the header row separately.

Unfortunately, the code as is does not export any <th> tags. I haven't had time to fully experiment with how to tweak it to do so.

I found this fork that includes table headers, and it works great in a regular HTML file, but, for some reason, it totally breaks on a PowerSchool custom page, even if you change the $ jQuery references to $j, which is weird, because it seems to be almost the exact same method, except it extracts out some of the other pieces to be functions, which get called twice.

DataTables?

Also, using DataTables seems to work fine in regular HTML, but I haven't found a way to properly implement it in PowerSchool custom pages. Here are the problems I've found:

  • No matter what you do with the width of the datatables element in CSS, if it's within the box-round div on the custom page, it will be pushed down (lots of extra white space at the top) if the sidebar is expanded. I can't figure out how to stop that from happening.
  • You can use jQuery.noConflict(true) to separate the DataTables jQuery from the PowerSchool jQuery, but then no other jQuery commands will run. It's very strange.
  • Even then, you have to put the CDN from DataTables above the ~[wc:commonscripts], or even the noConflict() won't work at all.

sqlReports4

Someone on PowerSource forums mentioned that sqlReports4 would probably be the best solution for making reports that can then be exported as .csv. I tried it out, and it looks promising, assuming you can get the SQL statement just right. My SQL skills are beginner-to-intermediate and not advanced, so I sometimes use custom pages with normal SQL (some joins, and some if/else) and then futz with the results using JavaScript to push them to the appropriate places. I may do a separate blog post on sqlreports4...

PowerSchool custom pages and REGEXP_LIKE

In general, if you test a SQL query in SQL Developer against your PowerSchool database, that same SQL query should work in a PowerSchool custom page using ~[tlist_sql][/tlist_sql]. It does not, however—I discovered just now—work if you include a REGEXP_LIKE as part of your query. Will work fine in SQL Developer... just not in a PowerSchool custom page.

Unfortunately, the only workaround I had was to fetch more results than I wanted server side and then use jQuery on the client side to filter out the extraneous results.

Update: Apparently, REGEXP_LIKE does work with ~[tlist_sql], or so I hear from someone with a lot more PowerSchool experience than I have. I thought I was being careful with my syntax, but it's possible I just screwed up, and SQL Developer is more forgiving of punctuation mistakes than ~[tlist_sql] is.

PowerSchool custom pages tlist sql queries for specific courses

Usually if you're doing a tlist_sql query on a PowerSchool custom page, any SQL query that actually works in SQL Developer will work on the custom page.

I found an odd one today, though—if you have a SQL query that actually works in SQL Developer that uses course names (e.g., ((course_name='Name of One Course') OR (course_name='Name of Another Course'))), that same query will just die silently on a PowerSchool custom page.

On the PowerSchool custom page, you have to use the course number instead. You cannot explicitly call the course name.

Update: Someone brought to my attention that the course name search should work in tlist_sql, but there may just be a character encoded weirdly. Very true, very possible. I'd still probably go with just searching by course number, since that's less likely to change than the course name.

Using “current selection” of students in PowerSchool custom pages

I didn't come up with this one on my own (big shout-out to Roger from the PowerSource forums), but I wanted to document how you use a "current selection" of students in the PowerSchool custom page:

~[tlist_sql; SELECT st.dcid, st.last_name, st.first_name, st.grade_level
FROM Students st
INNER JOIN ~[temp.table.current.selection:students] stusel
ON stusel.dcid = st.dcid ]<div class="photo_box">
<a href="../stp/~(dcid)ph.jpeg" target="_blank">
<img src="../stp/~(dcid)ph.jpeg" width="100" border="0" /></a>
<br clear="both" />
<strong>~(last_name), ~(first_name)</strong>: ~(grade_level)</div>[/tlist_sql]
The current selection (in bold) can be joined in the SQL query to the students table.

Random P.S. I'm referencing here the student photo by exact location. There is a way to reference it indirectly as a variable (instead of directly by filename), but then the photo is reduced in size. Since the raw photos aren't that large, I prefer to load the entire raw photo. Here's how you reference it indirectly:

<span class="account-photo">~[studenttitlephoto]</span>

Getting GPA methods into a PowerSchool custom page that uses tlist_sql

Acknowledgements: the actual genius of this workaround approach (including the important snippets of code) is from Adam L. on the PowerSource forums (you can search the forums for a thread entitled "tlist_sql and GPA methods").

I find ~[tlist_sql] handy for doing reports out of PowerSchool, because it's transparent (someone can look directly at the page code and see what is being selected and how) and because it's semi-universal (PowerSchool has its own quirks, but the queries are fairly standard Oracle SQL, so someone coming in knowing only SQL or MySQL can easily adapt to this approach to reporting out of PowerSchool). One of my users asked for a report with three different kinds of GPA calculations. For the sake of this example, we'll just call them Unweighted, WeightedOneWay, and WeightedAnotherWay.

On a custom page looking at only one student, you can reference the GPA calculation this way:

~(*gpa method="Unweighted")
But if you're reporting out listing several students, that won't work, and PowerSchool doesn't offer a built-in way to do so.

As I had to do with test score reporting, GPAs for multiple students involves some JavaScript gymnastics.

The basic idea is that you put in a placeholder for the GPA that has the dcid as a parameter, and then you use jQuery to substitute in a separate .html page with that individual's GPA, and then just go down the line substituting in that page for every result.

This is a version of what I ended up with (I left out some extra tweaks specific to our school):

Referenced HTML file called student_gpas.html

~(*gpa method="~[gpv:method]")

Report HTML file (call it whatever you want) that calls the referenced file

<!DOCTYPE html>
<html>
<head>
<title>Report with student's GPAs in it</title>
<!-- required scripts -->
~[wc:commonscripts]
<!-- Required style sheets: screen.css, and print.css -->
<link href="/images/css/screen.css" rel="stylesheet" media="screen">
<link href="/images/css/print.css" rel="stylesheet" media="print">
<script>
$j(document).ready(function()
{
$j(".gpa").each(function()
{
$j(this).load("student_gpas.html",
{
"frn": "001" + $j(this).attr("dcid"),
"method": $j(this).attr("method")
})
});
})
</script>

</head>
<body>
~[wc:admin_header_css]
<!-- breadcrumb start --><!-- breadcrumb end -->
<!-- start of main menu and content -->
~[wc:admin_navigation_css]
<!-- Start of Page -->
<h1>Report with student's GPAs in it</h1>
<!-- start of content area -->
<div class="box-round">
<table class="tableToGrid">
<thead>
<tr>
<th data-sorttype="customText">Student</th>
<th data-sorttype="integer">Gr.</th>
<th data-sorttype="attrDec">Unweighted</th>
<th data-sorttype="attrDec">Weighted One Way</th>
<th data-sorttype="attrDec">Weighted Another Way</th></tr></thead><tbody>
~[tlist_sql;
SELECT dcid student_dcid, last_name, first_name, grade_level, dcid student_dcid, dcid student_dcid, dcid student_dcid
FROM students
WHERE enroll_status=0
ORDER BY grade_level DESC, lastfirst ASC; alternatecolor]
<tr>
<td>~(last_name), ~(first_name)</td>
<td>~(grade_level)</td>
<td class="gpa" dcid="~(student_dcid)" method="Unweighted"></td>
<td class="gpa" dcid="~(student_dcid)" method="WeightedOneWay"></td>
<td class="gpa" dcid="~(student_dcid)" method="WeightedAnotherWay"></td>
</tr>[/tlist_sql]
</tbody></table> </div>
<!-- end of content area -->
~[wc:admin_footer_css]
</body>
</html>

What does all this code mean?

The bolded bits are the parts doing all the heavy lifting. So the first thing that happens is the page renders with a bunch of empty cells that have a class of "gpa" and a dcid of the student's dcid (one student per row). Then, once the page is loaded, the JavaScript at the top of the page looks through each item that has the class "gpa" and then substitutes in the content from student_gpas.html?frn=001#### where #### is the actual student's dcid number, with the method being the GPA method, so the first time it gets called, it will look for the unweighted GPA, then weighted one way, then the other. Then, it'll go to the next line and keep looking for those three for the next student.

Keep in mind, depending on how many results you have (how many students come back from your SQL query), it could take the page a long time to load. I ran a report (the select criteria was more specific than just any enrolled student) that had about 100 students with three GPA calculations per student, and it took almost a full minute for the page to load.

Using text variables in tlist_sql queries in PowerSchool

On custom pages in PowerSchool, you have the option to use something called tlist_sql to run SQL queries on your PowerSchool database. Sometimes, you can insert variables in your query. I've had good luck with SQL queries using built-in variables that are essentially a number:

~[tlist_sql; SELECT t.Name, to_char(st.Test_Date, 'MM/DD/YYYY'), st.Grade_Level, ts.Name, ts.Description, sts.NumScore, sts.studenttestid FROM StudentTest st
INNER JOIN StudentTestScore sts ON sts.StudentTestID=st.ID AND sts.StudentID=st.StudentID
INNER JOIN TestScore ts ON sts.TestScoreID=ts.ID
INNER JOIN Test t ON ts.TestID=t.ID
WHERE st.StudentID=~(curstudid) ORDER BY t.Name, st.Test_Date, sts.studenttestid, ts.SortOrder;]
Here, ~(curstudid) is the ID of the student we're currently looking at, usually referenced by something like nameofpage.html?frn=0019999, where 001 is the students table and 9999 is the student's ID number.

I recently came across an issue trying to use built-in text in a query. (Confession time: we are still using custom fields and haven't migrated them all over to database extensions.)

I tried using ~([05]Last_Name) to get the counselor's name, which actually works if you put it in the straight HTML file just to see that the name is fetched. However, if you try to create a query using that variable, the query fails:

"Student Number","Last Name","First Name","Grade Level","Counselor"~[tlist_sql;
SELECT student_number, last_name, first_name, grade_level, ps_customfields.getcf('students', id, 'student_counselor') student_counselor
FROM students
WHERE ps_customfields.getcf('students', id, 'student_counselor')
= '~([05]Last_Name)' AND enroll_status=0
ORDER BY grade_level, lastfirst]
"~(student_number)","~(email)","~(last_name)","~(first_name)","~(grade_level)","~(student_counselor)"[/tlist_sql]
I tried all sorts of variations. I tried using LIKE (instead of =), in case there was extra space. I tried using double quotation marks (which fail even in SQL Developer). No variation whatsoever of ~([05]Last_Name) worked (or [05]Last_Name).

Eventually, I came to a rather odd workaround, but it works. Instead of using the frn passed as a GET variable in the URL, I used the actual last name instead (so the address is more like nameofpage.html?lastname=actuallastname.

With that other approach in place, I could use the GET variable in the query:

"Student Number","Last Name","First Name","Grade Level","Counselor"~[tlist_sql;
SELECT student_number, last_name, first_name, grade_level, ps_customfields.getcf('students', id, 'student_counselor') student_counselor
FROM students
WHERE ps_customfields.getcf('students', id, 'student_counselor')
= '~(gpv.lastname)' AND enroll_status=0
ORDER BY grade_level, lastfirst]
"~(student_number)","~(email)","~(last_name)","~(first_name)","~(grade_level)","~(student_counselor)"[/tlist_sql]
I couldn't find any PowerSchool documentation around this, so I hope this blog post is helpful to some PowerSchool custom pages folks out there...