Advanced Options for Importing Data from PowerSchool SIS

[#1]

Looking for a better way to integrate with PowerSchool?  Check out this article!

ATTENTION: Currently the Custom SQL Reports tab (sqlReports 4 tab) will not work for Powerschool version 7.8.0. (possible solution)

This article describes advanced options for data export as described in the article Importing Data to PowerSchool Learning from PowerSchool.  Make sure you are familiar with that article first.

 

Question

 Can I use different data for my teachers and students in my export from PowerSchool?  For example, I want to use my teachers' PowerSchool LoginID as their PowerSchool Learning login, but if I change the query it doesn't give me any results.

Answer

The Users Custom SQL Query for PowerSchool combines data for your teachers and your students into a single Users file for import into PowerSchool Learning, which works well as long as you are using the same data for both types of users. This, however, is not always the case. If you need to export different data for your teachers and students there are two ways that it can be done.

1) You can break the query up into two queries and see if it works.

Teachers

This query selects the teacher info from the teachers table and the schools table, but only for teachers with a status of 1.

We use the id field from the schools table as the PowerSchool Learning org id. To link the teachers table to the schools table you use the schoolid from teachers and the school_number from schools.

The Email address is repeated for Google Apps users. It can be left off for non Google Apps schools.

users.csv

<ReportName>Users</ReportName>
<ReportTitle>Users</ReportTitle>
<ReportGroup>Haiku Export</ReportGroup>
<ReportDescription></ReportDescription>
<SQLQuery>select t.id, t.first_name, '', t.last_name, '', 
'', t.LoginID, '', t.email_addr, 't', s.id, '1', t.id, t.email_addr from 
teachers t join schools s on t.schoolid = s.school_number where status = 1; </SQLQuery>
<ReportHeader><th>import_id</th><th>first_name</th><th>middle_name</th><th>last_name</th><th>suffix</th><th>nickname</th><th>login</th><th>password</th><th>email</th><th>user_type</th><th>organization_id</th><th>enabled</th><th>display_id</th><th>google_email_address</th></ReportHeader>
<CreateStudentSelection>0</CreateStudentSelection>
<StudentSelectionQuery></StudentSelectionQuery>
<IncludeRowNumber>0</IncludeRowNumber>
<OpeninNewWindow>0</OpeninNewWindow>

Students

The student info is basically the same as the teacher info.  You access the the schools.id field the same way.

However, the status is flipped for students.  In Powerschool student enroll_status of 0 means active or currently enrolled. 

users.csv

<ReportName>Users</ReportName>
<ReportTitle>Users</ReportTitle>
<ReportGroup>Haiku Export</ReportGroup>
<ReportDescription></ReportDescription>
<SQLQuery>select st.student_number, st.first_name, '', st.last_name, '', '', st.student_number, '', '', 's',sc.id, '1', st.student_number, '' from students st join schools sc on st.schoolid = sc.school_number where st.enroll_status = 0; </SQLQuery>
<ReportHeader><th>import_id</th><th>first_name</th><th>middle_name</th><th>last_name</th><th>suffix</th><th>nickname</th><th>login</th><th>password</th><th>email</th><th>user_type</th><th>organization_id</th><th>enabled</th><th>display_id</th></ReportHeader>
<CreateStudentSelection>0</CreateStudentSelection>
<StudentSelectionQuery></StudentSelectionQuery>
<IncludeRowNumber>0</IncludeRowNumber>
<OpeninNewWindow>0</OpeninNewWindow>

2) Or a single query. The Cast(...) statement does the conversion from one data type to the other.

users.csv

<ReportName>Users</ReportName>
<ReportTitle>Users</ReportTitle>
<ReportGroup>Haiku Export</ReportGroup>
<ReportDescription></ReportDescription>
<SQLQuery>SELECT t.id, t.first_name, '', t.last_name, '', '', Cast(t.LoginID AS VARCHAR(100)), '', t.email_addr, 't', s.id, '1', t.id, t.email_addr FROM teachers t JOIN schools s ON t.schoolid = s.school_number WHERE status = 1 UNION SELECT st.student_number, st.first_name, '', st.last_name, '', '', Cast(st.student_number AS VARCHAR(100)), '', '', 's', sc.id, '1', st.student_number, '' FROM students st JOIN schools sc ON st.schoolid = sc.school_number WHERE st.enroll_status = 0; </SQLQuery>
<ReportHeader><th>import_id</th><th>first_name</th><th>middle_name</th><th>last_name</th><th>suffix</th><th>nickname</th><th>login</th><th>password</th><th>email</th><th>user_type</th><th>organization_id</th><th>enabled</th><th>display_id</th><th>google_email_address</th></ReportHeader>
<CreateStudentSelection>0</CreateStudentSelection>
<StudentSelectionQuery></StudentSelectionQuery>
<IncludeRowNumber>0</IncludeRowNumber>
<OpeninNewWindow>0</OpeninNewWindow>

Fields 

The user type is always a static "s" or "t," depending on the query, and the enabled flag is always "1." Also, students don't have an email field in Powerschool, so it's blank.

Students	Teachers	PowerSchool Learning
st.student_number	t.id	import_id
st.first_name	t.first_name	first_name
[blank]	[blank]	middle_name
st.last_name	t.last_name	last_name
[blank]	[blank]	suffix
[blank]	[blank}	nickname
st.student_number	t.loginid	login
[blank]	[blank]	password
[blank}	t.email_addr	email
"s"	"t"	user_type
sc.id	s.id	organization_id
"1"	"1"	enabled
st.student_number	t.id	disaply_id
[blank]	t.email_addr	google_email__address (optional)

For student email you can use ps_customfields.getStudentscf(st.id,'google_email_s') where 'google_email_s' is the name of the custom field. If the the google_id is included within a custome field for teachers you would use ps_customfields.getTeacherscf(t.id,'google_email_t').