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.dcid=~(curstudid) ORDER BY t.Name, st.Test_Date, sts.studenttestid, ts.SortOrder;]

Here, ~(curstudid) is the dcid 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 dcid.

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…

Comments

2 responses to “Using text variables in tlist_sql queries in PowerSchool”

Renee

April 19, 2018

Just a correction:

frn=0019999

001 is the table number (Students)
9999 is the DCID from the Students table

There can be a DCID and ID mismatch, and using the ID vs the DCID can cause data errors.

You can find the student’s ID value on the students’ Teacher Comments screen below the box.

Hope this helps to clarify.

1. Alan Siu
  
  April 19, 2018
  
  Thanks for the clarification. I found that out the hard way but just didn’t get around to updating this post.