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

PowerSchool Customization

~[x:insertfile;tabs.html]

The Why of Database Extensions

Relational Database Structure

Definition of a Relational Database according to dictionary.reference.com
An electronic database comprising multiple files of related information, usually stored in tables of rows (records) and columns (fields), and allowing a link to be established between separate files that have a matching field so that the two files can be queried simultaneously by the user.
Oracle's Definition of a Primary Key
In Oracle, a primary key is a single field or combination of fields that uniquely defines a record. None of the fields that are part of the primary key can contain a null value.
Oracle's Definition of a Foreign Key
A foreign key is a way to enforce referential integrity within your Oracle database. A foreign key means that values in one table must also appear in another table. The referenced table is called the parent table while the table with the foreign key is called the child table. The foreign key in the child table will generally reference a primary key in the parent table.
Students
ID (Primary Key)5
DCID2
Last_NameAdair
First_NameBrandon
Storedgrades
ID (Primary Key)2587
StudentId (Foreign Key)5
GradeA
StorecodeS1
Course_NumberMAT1000
Note: The ID field in the Students table is not technically the primary key, but it is a unique identifier in the table that can be used to establish table relationships.
The Old Way - Custom Fields
Students
Last_NameAdair
First_NameBrandon
DCID2
ID5
CUSTOM Medical Notes; Allergic to peanuts, 546,Mr. B, 78489635, 555-743-5547, contact_1_eamil; contact1@email.com, contact_2_email; contact2@email.com, contact_1_employer;Wal Mart, contact_2_employer; Lanham Law Offices 7875 West Greenway Blvd. Middleton, WI 53562 parent_notes; Brandon is only allowed to be picked up from school on Wednesdays by his father.
With custom fields, all custom data is stored in a single field in the core table.
The New Way - Database Extensions
Students
Last_NameAdair
First_NameBrandon
DCID2
ID5
U_Def_Ext_Students
StudentsDCID2
Contact1_Emailcontact1@email.com
Contact2_Emailcontact2@email.com
Contact1_EmployerWal Mart
Conatct2_EmployerLanham Law Offices
Medical_NotesAllergic to Peanuts

With database extensions, users can define their own tables and fields in the relational database. This method of storage makes data retrieval exponentially faster.

Creating Database Extensions / Migrating Custom Fields

Managing Database Extensions
System > Page and Data Management > Manage Database Extensions

There are four steps to creating a database extension.

Step 1 - Choose the core table that will be extended (the table for which we are creating or migrating a field).
Note: Here, the user can also choose Basic or Advanced Extensions. Choosing the Basic option will automatically select default options for steps 2 and 3, bringing the user directly to step 4. Step 2 - Choose or add a database extension group. The database extension group is simply a name given to a group of database extended tables. Many users choose to use a single extension group for all custom fields. Others choose to create different groups to compartmentalize data (i.e. demographic data and contact data). Either approach is valid.

U_Students_Extension is the default extension group when extending the Students table. This is the option that will be used if the Basic Extension type is selected in step 1. Step 3 - Choose or add a database table. Each extension group can contain multiple tables. There are two types of tables.
U_Def_Ext_Students is the default extension table when extending the Students table. This is the option that will be used if the Basic Extension type is selected in step 1.

Step 4 - Create new field for the selected table. Here the user can create new fields. When the Add button is clicked, the Add Field dialog will appear.

Important notes about field creation

Data Migration Tool

System > Page and Data Management > Custom Field Data Migration

In addition to migrating field individually, users have the option to migrate all fields at once using PowerSchool's Custom Field Migration Tool. This process is naturally much faster than migrating fields individually, but the user loses the ability to rename fields and define data-types. When using the migration tool, all custom fields are copied to and extended field of the same name with type - String and length - 4000. In addition to migrating user-created custom fields, the migration tool can be used to migrate core custom fields. These are core PowerSchool fields which have been implemented using the custom field framework. Note: As indicated by the warning at the top of the migration screen, any custom fields that have over 4000 characters will be truncated. This situation is unlikely, but the Migration Report (Step 2) will indicate if this situation does exist for any field, allowing the user to make any necessary modifications prior to migration. If no data in excess of 4000 characters is found, step 3 can be skipped.

Advanced Extensions (One-to-Many Extensions)

Library System Example

Here we will explore advanced database extensions by creating tables and pages to track library books, checkouts, and returns. The solution includes two extended tables.


Both tables will be created in a dedicated Extension Group called U_Library

U_Lib_Book Table Setup

System > Manage Database Extensions
Step 1 - Choose other from the first selector and then click the "Add Independent Table" button. Step 2 - Add an extension group. Step 3 - Add the U_Lib_Book table. Step 4 - Create the needed fields, and submit the extension.

U_Lib_Checkout Table Setup

System > Manage Database Extensions
Here, we will create the table to store checkouts as a child table of the Students table.

Step 1 - Choose Students and Advanced Extension. Step 2 - Click the radio button to 'View all user created database extension groups'. Next select the extension group we created earlier (U_Library). Step 3 - Add the U_Lib_Checkout table. "Can have multiple records" must be checked so a one-to-many table is created. Step 4 - Create the needed fields, and submit the extension.

A Look at the Tables

Below are the tables that we have created.
In addition to the fields that we defined when creating the tables, PowerSchool creates an ID field as well as a foreign-key (StudentsDCID) for the checkout table. The WHO and WHEN fields are audit fields which are automatically created as well.

Accessing One-to-Many Extensions in Custom Pages

Direct Table Access Tag ~[DirectTable.Select:<tableName>;<pkColumn>:<pkValue>]

Example: ~[DirectTable.Select:U_Lib_Book;id:104]

Once the Direct Table Select tag has been included in a page, extended table fields can be referenced with the same syntax used to access core table fields. <input type="text" name="[tableName]fieldName"/>

Example: <input type="text" name="[U_Lib_Book]title"/>
Syntax to delete an extended table record. <input type="hidden" name="DD-<tableName>.<idColumn>:<idValue>" />

Example: <input type="hidden" name="DD-U_Lib_Book.id:104" />

Books Home Page


<!DOCTYPE html>
<html>
<head>
	<title>Library Management</title>
	~[wc:commonscripts] 
	<style>
		.grid{
			margin-top:20px;
		}
	</style>
	<link href="/images/css/screen.css" rel="stylesheet" media="screen">
	<link href="/images/css/print.css" rel="stylesheet" media="print">
</head> 
<body> 
	~[wc:admin_header_css] 
	<a href="/admin/home.html" target="_top">Start Page </a> <
	<a href="/admin/schoolsetup/home.html" target="_top">School Setup </a> <
	Library Book Management
	~[wc:admin_navigation_css] 

	<h1>Library Book Management</h1> 

	<ul class="tabs">
		<li class="selected"><a href="books.html">Book Management</a></li>
		<li><a href="checkout.html">Checkout</a></li>
		<li><a href="return.html">Return</a></li>
	</ul>

	
 <div class="center">
 	<a class="button" href="editBook.html?bookid=-1">New Book</a>
 </div>

<table class="grid">
	<tr>
		<th>Title</th>
		<th>Copy #</th>
		<th>Author</th>
		<th>ISBN</th>
		<th>Publish Date</th>
	</tr>
	~[tlist_sql;
		SELECT
			id,
			title,
			copy_number,
			author,
			isbn,
			to_char(publish_date,'MM/DD/YYYY')
		FROM u_lib_book
		WHERE schoolid = ~(curschoolid)
		ORDER BY title, copy_number
	;]
		<tr>
			<td><a href="editBook.html?bookid=~(id)">~(title)</a></td>
			<td>~(copy)</td>
			<td>~(author)</td>
			<td>~(isbn)</td>
			<td>~(publish_date)</td>
		</tr>
	[/tlist_sql]
</table>


	~[wc:admin_footer_css] 
</body> 
</html>

			

New/Edit Books Page


<!DOCTYPE html>
<html>
<head>
	<title>Library Management</title>

	~[wc:commonscripts] 

<script type="text/javascript">

$j(document).ready(function(){
	$j('#deleteButton').click(function(e){
		e.preventDefault();
		$j('#deleteForm').submit();
	});
});
</script>

	<link href="/images/css/screen.css" rel="stylesheet" media="screen">
	<link href="/images/css/print.css" rel="stylesheet" media="print">
</head> 
<body> 
	~[wc:admin_header_css] 
	<a href="/admin/home.html" target="_top">Start Page </a> >
	<a href="/admin/schoolsetup/home.html" target="_top">School Setup </a> >
	Library Book Management
	~[wc:admin_navigation_css] 

<h1>~[if.~(gpv.bookid)=-1]New[else]Edit[/if] Book</h1> 

<ul class="tabs">
	<li class="selected"><a href="books.html">Book Management</a></li>
	<li><a href="checkout.html">Checkout</a></li>
	<li><a href="return.html">Return</a></li>
</ul>

<form action="books.html" method="POST">
~[DirectTable.Select:u_lib_book;id:~(gpv.bookid)]
	<div class="box-round"> 
		<table class="grid">
			<tr>
				<td class="bold">Title</td>
				<td>
					<input type="text" name="[u_lib_book]title" value="" />
				</td>
			</tr>
			<tr>
				<td class="bold">Copy #</td>
				<td>
					<input type="text" name="[u_lib_book]copy_number" class="psNumWidget" value="" />
				</td>
			</tr>
			<tr>
				<td class="bold">Author</td>
				<td>
					<input type="text" name="[u_lib_book]author" value="" />
				</td>
			</tr>
			<tr>
				<td class="bold">ISBN</td>
				<td>
					<input type="text" name="[u_lib_book]ISBN" value="" />
				</td>
			</tr>
			<tr>
				<td class="bold">Publish Date</td>
				<td>
					<input type="text" name="[u_lib_book]publish_date" value="" />
				</td>
			</tr>
		</table>
		<div class="button-row">
			<input type="hidden" name="[u_lib_book]schoolid" value="~(curschoolid)" />
			<input type="hidden" name="ac" value="prim" />
			~[if.~(gpv.bookid)>0]
				<button id="deleteButton">Delete</button>
			[/if]
			<a href="books.html" class="button">Cancel</a>
			~[submitbutton]
		</div>
	</div> 
</form>

<form id="deleteForm" action="books.html">
	<input type="hidden" name="DD-u_lib_book.id:~(gpv.bookid)" value="1">
	<input type="hidden" name="ac" value="prim" />
</form>

	~[wc:admin_footer_css] 
</body> 
</html>

		

Checkout Page


<!DOCTYPE html>
<html>
<head>
	<title>Library Management</title>

	~[wc:commonscripts] 

<script type="text/javascript">
	$j(document).ready(function(){
		~[tlist_sql;
			SELECT to_char(sysdate + 14,'MM/DD/YYYY')
			FROM dual
		;]
			$j('#returnDate').val('~(returnDate)');
		[/tlist_sql]
	});
</script>

	<link href="/images/css/screen.css" rel="stylesheet" media="screen">
	<link href="/images/css/print.css" rel="stylesheet" media="print">
</head> 
<body> 
	~[wc:admin_header_css] 
	<a href="/admin/home.html" target="_top">~[text:psx.html.admin_attendance_codes.home.start_page]</a> >
	<a href="/admin/schoolsetup/home.html" target="_top">~[text:psx.html.admin_attendance_codes.home.school_setup]</a> >
	Library Book Management
	~[wc:admin_navigation_css] 

	<h1>Check Out</h1> 

	<ul class="tabs">
		<li><a href="books.html">Book Management</a></li>
		<li class="selected"><a href="checkout.html">Checkout</a></li>
		<li><a href="return.html">Return</a></li>
	</ul>

<form method="POST">
~[ DirectTable.Select:u_lib_checkout;id:-1]
	<div class="box-round"> 
		<table class="grid">
			<tr>
				<td class="bold">Student</td>
				<td>
					<select name="students.dcid">
						~[tlist_sql;
							SELECT dcid, lastfirst
							FROM students
							WHERE schoolid = ~(curschoolid)
							AND enroll_status = 0
							ORDER BY lastfirst
						;]
							<option value="~(dcid)">~(name)</option>
						[/tlist_sql]
					</select>
				</td>
			</tr>
			<tr>
				<td class="bold">Book</td>
				<td> 
					<select name="[u_lib_checkout]book_id">
						~[tlist_sql;
							SELECT id, title, copy_number
							FROM u_lib_book
							WHERE schoolid = ~(curschoolid)
							AND NOT EXISTS(
								SELECT *
								FROM u_lib_checkout
								WHERE book_id = u_lib_book.id
								AND return_date IS NULL
							)
							ORDER BY title, copy_number
						;]
							<option value="~(id)">~(author) (~(copy))</option>
						[/tlist_sql]
					</select>
				</td>
			</tr>
			<tr>
				<td class="bold">Checkout Date</td>
				<td>
					<input type="text" class="psDateWidget required" value="~[short.date]" 
						name="[u_lib_checkout]checkout_date" />
				</td>
			</tr>
			<tr>
				<td class="bold">Due Date</td>
				<td>
					<input type="text" class="psDateWidget required" 
						name="[u_lib_checkout]due_date" id="returnDate" />
				</td>
			</tr>
		</table>
		<div class="button-row">
			<input type="hidden" name="ac" value="prim" />
			<button>Complete Checkout</button>
		</div>
	</div> 
</form>

	~[wc:admin_footer_css] 
</body> 
</html>

		

Return Page


<!DOCTYPE html>
<html>
<head>
	<title>Library Management</title>

	~[wc:commonscripts] 
	<link href="/images/css/screen.css" rel="stylesheet" media="screen">
	<link href="/images/css/print.css" rel="stylesheet" media="print">
</head> 
<body> 
	~[wc:admin_header_css] 
	<a href="/admin/home.html" target="_top">Start Page</a> >
	<a href="/admin/schoolsetup/home.html" target="_top">School Setup</a> >
	Library Book Management
	~[wc:admin_navigation_css] 

	<h1>Book Return</h1> 

	<ul class="tabs">
		<li><a href="books.html">Book Management</a></li>
		<li><a href="checkout.html">Checkout</a></li>
		<li class="selected"><a href="return.html">Return</a></li>
	</ul>

<table class="grid" data-pstablefilter="">
	<tr>
		<th>Student</th>
		<th>Book</th>
		<th>Copy</th>
		<th>Checkout Date</th>
		<th>Due Date</th>
	</tr>
	~[tlist_sql;
		SELECT
		    u_lib_checkout.id,
		    students.lastfirst,
		    u_lib_book.title,
		    u_lib_book.copy_number,
		    to_char(u_lib_checkout.checkout_date,'MM/DD/YYYY'),
		    to_char(u_lib_checkout.due_date,'MM/DD/YYYY')
		FROM u_lib_checkout 
		JOIN u_lib_book ON u_lib_book.id = u_lib_checkout.book_id
		JOIN students ON students.dcid = u_lib_checkout.studentsdcid
		WHERE u_lib_book.schoolid = ~(curschoolid)
		AND u_lib_checkout.return_date IS NULL
		ORDER BY students.lastfirst, u_lib_book.title
	;]
		<tr>
			<td><a href="completeReturn.html?recId=~(id)">~(student)</a></td>
			<td>~(book)</td>
			<td>~(copy)</td>
			<td>~(checkout)</td>
			<td>~(due)</td>
		</tr>
	[/tlist_sql]
</table>

	~[wc:admin_footer_css] 
</body> 
</html>

		

Complete Return Page


<!DOCTYPE html>
<html>
<head>
	<title>Library Management</title>

	~[wc:commonscripts] 

<script type="text/javascript">
	$j(document).ready(function(){
		$j('#returnDate').val('~[short.date]');
	});
</script>

	<link href="/images/css/screen.css" rel="stylesheet" media="screen">
	<link href="/images/css/print.css" rel="stylesheet" media="print">
</head> 
<body> 
	~[wc:admin_header_css] 
	<a href="/admin/home.html" target="_top">Start Page</a> >
	<a href="/admin/schoolsetup/home.html" target="_top">School Setup</a> >
	Library Book Management
	~[wc:admin_navigation_css] 

	<h1>Book Return</h1> 

	<ul class="tabs">
		<li><a href="books.html">Book Management</a></li>
		<li><a href="checkout.html">Checkout</a></li>
		<li class="selected"><a href="return.html">Return</a></li>
	</ul>

<table class="grid">
	<tr>
		<th>Student</th>
		<th>Book</th>
		<th>Copy</th>
		<th>Checkout Date</th>
		<th>Due Date</th>
	</tr>
	~[tlist_sql;
		SELECT
			students.lastfirst,
			u_lib_book.title,
			u_lib_book.copy_number,
			to_char(u_lib_checkout.checkout_date,'MM/DD/YYYY'),
			to_char(u_lib_checkout.due_date,'MM/DD/YYYY')
		FROM u_lib_checkout
		JOIN students ON students.dcid = u_lib_checkout.studentsdcid
		JOIN u_lib_book ON u_lib_book.id = u_lib_checkout.book_id
		WHERE u_lib_checkout.id = ~(gpv.recId)
	;]
		<tr>
			<td>~(student)</td>
			<td>~(book)</td>
			<td>~(copy)</td>
			<td>~(checkout)</td>
			<td>~(due)</td>
		</tr>
	[/tlist_sql]
</table>

<form action="return.html" method="POST">
~[ DirectTable.Select:u_lib_checkout;id:~(gpv.recId)]
	<div class="box-round"> 
		<table class="grid">
			<tr>
				<td class="bold">Return Date</td>
				<td>
					<input type="text" name="[u_lib_checkout]return_date" id="returnDate" />
				</td>
			</tr>
			<tr>
				<td class="bold">Fine</td>
				<td>
					<input type="text" name="[u_lib_checkout]fine" class="psNumWidget" value="" />
				</td>
			</tr>
			<tr>
				<td class="bold">Fine Reason</td>
				<td>
					<input type="text" name="[u_lib_checkout]fine_reason" value="" />
				</td>
			</tr>
		</table>
		<div class="button-row">
			<input type="hidden" name="ac" value="prim" />
			<a href="return.html" class="button">Cancel</a>
			~[submitbutton]
		</div>
	</div> 
</form>

	~[wc:admin_footer_css] 
</body> 
</html>

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