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...

Leave a Reply

Your email address will not be published. Required fields are marked *