Tlist SQL
There are three parts to a tlist_sql statement
- Tlist_sql Tags
- Query
- Row Template
The structure looks like this:
~[tlist_sql;
SQL Query goes here;]
Row Template goes here
[/tlist_sql]
The code in the Row Template will be repeated for each record returned by the query.
Columns in each record are referenced using ~(column_name).
The columns in the Row Template must be in the same order as the query.
Here's an example. The following code displays school information in a table using tlist_sql.
<table class="linkDescList">
<thead>
<tr>
<th>Name</th>
<th>School Number</th>
<th>Principal</th>
<th>Address</th>
</tr>
</thead>
<tbody>
~[tlist_sql;
SELECT name, school_number, principal, address
FROM schools
ORDER BY sortorder
;]
<tr>
<td>~(name)</td>
<td>~(school_number)</td>
<td>~(principal)</td>
<td>~(address)</td>
</tr>
[/tlist_sql]
</tbody>
</table>
Name |
School Number |
Principal |
Address |
~[tlist_sql;
SELECT name, school_number, principal, address
FROM schools
ORDER BY sortorder
;]
~(name) |
~(school_number) |
~(principal) |
~(address) |
[/tlist_sql]
Tips for developing tlist_sql
-
Develop and test queries in a querying tool such as SQL Developer. Then paste the query into your tlist_sql tags.
PowerSchool will not respond with an error message if the query has an error; there simply won't be any data rendered.
However, tlist_sql errors will appear in the PowerSchool log (System > System Logs > System Log > View Current System Log File).
-
Tlist_sql is not limited to generating table rows. It can also be used to create options for <select> inputs,
or any solution that would require repeated code for each result from a query.
-
When working with the Students table, you can query the current selection of students by adding the following WHERE clause:
WHERE dcid IN (SELECT dcid FROM ~[temp.table.current.selection:students])
Important - tlist_sql can result in security issues if not implemented properly. Field-level security is
not enforced, so care should be taken to restrict page permissions if secure data is displayed.
PowerSchool recommends using PowerQueries in lieu of tlist_sql.
See the
Field Level Security Usage and Customizations
article on the PowerSchool Community site for more information on securing data in tlist_sql.
Conditional Logic (IF tag)
~[if{#identifier}.{expression}]
{true_render}
[else]
{false_render}
[/if]
-
{#identifier} - Optional. This portion of the tag is used when nesting multiple IF tags. The identifier must be
prefixed with a number sign(#).
-
{expression} - Required. A comparison expression to test for a true/false result.
-
{true_render} - Optional. Rendered by PowerSchool if the if statement expression returns true.
-
{false_render} - Optional. Rendered by PowerSchool if the if statement expression returns false.
~[if] tags are often used in conjunction with ~(gpv.xx) tags. ~(gpv.xx) tags are used to retrieve the value
of parameters passed to the page. For example, a custom report might have a parameter passed called gender. This
parameter could be accessed using the following tag: ~(gpv.gender).
~[if.~(gpv.gender)=M]
Male
[else]
Female
[/if]
The following special if statements are available
- ~[if.district.office]
- ~[if.is.a.school]
- ~[if.mac]
- ~[if.win]
- ~[if.isstudent]
- ~[if.isguardian]
Example
~[if#distCheck.district.office]
~[if#winCheck.win]
District office on Windows
[else#winCheck]
District office on Mac
[/if#winCheck]
[else#distCheck]
~[if#winCheck2.win]
~(schoolname) on Windows
[else#winCheck2]
~(schoolname) on Mac
[/if#winCheck2]
[/if#distCheck]
Result of conditional logic shown above:
~[if#distCheck.district.office]
~[if#winCheck.win]
District office on Windows
[else#winCheck]
District office on Mac
[/if#winCheck]
[else#distCheck]
~[if#winCheck2.win]
~(schoolname) on Windows
[else#winCheck2]
~(schoolname) on Mac
[/if#winCheck2]
[/if#distCheck]