~[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
DCID | 2
Last_Name | Adair
First_Name | Brandon
Storedgrades
ID (Primary Key) | 2587
StudentId (Foreign Key) | 5
Grade | A
Storecode | S1
Course_Number | MAT1000

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_Name | Adair
First_Name | Brandon
DCID | 2
ID | 5
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_Name | Adair
First_Name | Brandon
DCID | 2
ID | 5

U_Def_Ext_Students
StudentsDCID | 2
Contact1_Email | contact1@email.com
Contact2_Email | contact2@email.com
Contact1_Employer | Wal Mart
Conatct2_Employer | Lanham Law Offices
Medical_Notes | Allergic 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.

- One-to-One tables hold data that can be defined only once for a student. These are the tables that will hold new or migrated custom fields.
- One-to-Many tables can have multiple records for a single student. Examples of these types of tables in core PowerSchool include CC, StoredGrades, and Log

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

- Once a field has been created, it cannot be modified. Take time to consider names, data-types, and lengths carefully before submission.
- Leave the 'Migrate Data From' dropdown empty to create a new field.
- If migrating a new extended field will be created and all data from the custom field will be migrated into the newly-defined extended field.
- Fields can be renamed when migrating. PowerSchool maps previous names to the new extended names to ensure that no custom pages or queries will break.

## 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.

- U_Lib_Book - Records for all library books.
- U_Lib_Checkout - Records of students checkout and return of library books.

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]`

- tableName - Name of the extended table.
- pkColumn - Primary key column of the extended table.
- pkValue - Primary key value of record being updated in extended table (-1 to create new record)

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]