~[wc:commonscripts]
~[wc:admin_header_css] ~[text:psx.html.admin_reports.runreports.start_page] > PowerSchool Customization ~[wc:admin_navigation_css] ~[SetPostValue:tab=codes]

# PowerSchool Customization

~[x:insertfile;tabs.html]

- PS HTML is PowerSchool's special brand of HTML which allows you to add special codes and commands to interact with your server and the data in your PowerSchool database.
- PS HTML elements begin with a tilde character (~) and contain commands enclosed in parenthesis or square brackets.
- Prior to delivering content to a browser, the PowerSchool server replaces PS HTML with the appropriate traditional HTML content.

Tip: Try modifying the address of a page in PowerSchool by changing the page's extension to 'htmlr' in the address bar. This will cause the server to deliver content to the browser without processing PS HTML contents.
Common PS HTML Tags

PS HTML | Description | Example
--- | --- | ---
~(curstudid) | Current Student ID |
~(studentname) | Student Name |
~(cursecid) | Current Section ID |
~(curschoolid) | Current School ID | ~(curschoolid)
~(curyearid) | Current Year ID | ~(curyearid)
~(curtermid) | Current Term ID | ~(curtermid)
~(schoolname) | School Name | ~(schoolname)
~(yearname) | Current Year Name | ~(yearname)
~[time] | Current Time | ~[time]
~[short.date] | Current Date | ~[short.date]
~[x:username] | Current User | ~[x:username]
~[x:userid] | Current User ID | ~[x:userid]
~[x:studsinset] | Count of Students in Current Selection | ~[x:studsinset]
~[displaypref:districtname] | Display a district value from the prefs table | ~[displaypref:districtname]
~[prefschool:curfgname] | Display a school value from the prefs table | ~[prefschool:curfgname]
~([table_name]field_name) | Get a field value of a currently selected record |
~([table_name.extension_group_name]field_name) | Get an extended field value of a currently selected record |

## FRN and RN

- FRNs are used to indicate to PowerSchool the current record which must be accessed from the database.
- FRNs are numeric and always consists of at least four digits.
- The first three digits of an FRN indicate the table number (i.e. 001 for the students table)
- The digits following the table number indicate the unique id (dcid) of a record in a given table.
- ~(frn) is the PS HTML tag used to retrieve or set the current record.
- ~(rn) is the PS HTML tag used to retrieve the current unique id (dcid).
FRNs are often found in HTML links. `<a href="/admin/students/generaldemographics.html?frn=001332">Demographics</a>` The above link will navigate the user to the demographics page and retrieve data for a student in the database with a dcid of 332.
Once on this page, the use of the ~(frn) tag would result in a return of the value 001332.
The use of the ~(rn) tag would result in a return of the value 332.

## GPV - Get Posted/Parameter Value

The ~(gpv) tag provides access to values which have been passed to a page via the URL or an POST operation.
GPV Syntax `~(gpv.parameter_name)` Parameters are available to a page when they have been defined as part of a URL or have been passed as part of a form submission.
Parameters passed via the URL are visible in a page's address. They are listed following the page name, preceded by a question mark, and separated by ampersands.
Sample URL `myPsServer/admin/somePage.html?gender=F&grade_level=12` If the above were the current address of a page in PowerSchool, the parameters could be accessed as follows.

PS HTML | Result
--- | ---
~(gpv.gender) | F
~(gpv.grade_level) | 12

Parameters passed via a POST operation from a form on a previous page are available for use with ~(gpv), but do not appear in the URL.
~(gpv) tags are often used in conjunction with tlist sql (covered below) to retrieve and display data based on user input.
IMPORTANT - GPV tags should be used in conjuction with escape modifiers to prevent code injection attacks.
Nefarious users may try to pass values in parameters that provide access to restricted data or execute JavaScript commands that are not intended by the developer.
To prevent code injection, GPV tags should always include an escap modifier.
~(gpv.paramName;escapeCommand)

Tag | Description
--- | ---
encodejsstring | JavaScript encoded result
encodejsonstring | JSON encoded result
urlencode | URL encoded result
encodehtml | HTML encoded result
num | Numeric value
sqlText | SQL encoded result
onlynumeric | Removes any characters that are not in the following list: +-.,0123456789e
onlyalpha | Removes any non-alpha characters
onlyalphanumeric | Removes any non-alphanumeric characters (punctiation, special characters, etc.)
onlydatecharacters | Removes any characters that are not in the following list: ,-./0123456789

## Wildcards

Wildcards contain reusable content that appears in the same format in several PS pages.
Wildcard content is stored in the wildcards folder in the PS web root.
Headers, footers, and navigation code is commonly kept in wildcard files.
Wildcard Syntax `~[wc:wildcard_file_name]` Wildcard files all have an extension of txt.
The wildcard file name specified in the tag must contain the name of the file without the extension name.

## Insert File

The insert file command is similar to the wildcard tag in that it allows for insertion of content from a separate file.
However, this tag inserts content from a file not located in the wildcards folder.
Insert File Syntax `~[x:insertfile;fileName.html]` Unlike the wildcard tag, the insert file command defines a file name with the file extension included.

## Text Tags

Text tags retrieve text from PowerSchool's localization definitions.
Localization allows users to change the language used in a PowerSchool implementation, or translate individual words and phrases.
Text Tag Syntax `~[text:string_key_name]` string_key_name - The name of the localization key for which a value will be retrieved.

## 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;onlyalpha)=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]

~[x:insertfile;footer.html] ~[wc:admin_footer_css]