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:
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;]
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:
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]
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:
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]
2 responses to “Using text variables in tlist_sql queries in PowerSchool”
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.
Thanks for the clarification. I found that out the hard way but just didn’t get around to updating this post.