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').
