PowerSchool export plugin

PowerSchool's built-in export is limited. For the fastest integration with Catapult, use the CatapultEMS plugin for PowerSchool.

CatapultEMS:

  • For the StudentExport.csv you only need the first 12 columns (through the column labeled "contactEmailSecondary").
  • Omit AttendanceExport.csv. 

CatapultCONNECT:

  • For StudentExport.csv, the language of a contact (parent/guardian) is not a default field in PowerSchool. If you have a custom field for this, you can adjust the SQL in the plugin zip file under queries_root\Catapult.Students.named_queries.xml
  • Include the AttendanceExport.csv if it is in your contract.

Contents

Release history

  • Version 2.1 (2023-07-24): Updated StudentExport.csv to include four guardian contacts per student instead of two. Filter to active guardian contacts only. Important: After upgrading to 2.1 from an older version of the plugin, you may need to recreate the Student template and reschedule the uploads.
  • Version 2.0 (2023-03-10): Updated StaffToStudent.csv to include rosters for co-teachers (in addition to lead teachers) and exclude rosters for inactive teachers. Updated query name from "Students" to "Student" to avoid a common typo when creating the file name ("StudentExport.csv"). Important: After upgrading to 2.0 from an older version of the plugin, you need to recreate the Student template and reschedule the uploads.
  • Version 1.6 (not released publicly): Updated StaffToStudent.csv to further limit current courses. This affects districts who do not have the same number of terms across all schools. (It now joins the CC and Terms tables on both TermID and SchoolID. Previously SchoolID was omitted from the join.) The update also removes an unnecessary year query (no need to limit to the current school year, when we are already limited to the current terms).
  • Version 1.5 (2022-09-02): StaffExport.csv now includes all schools a staff member is active at (SchoolID from the SchoolStaff table). Previous versions used just one "main" school per staff member (HomeSchoolId from the Users table). SchoolExport now includes the pluginVersion.
  • Version 1.4 (2022-06-06): Limit StaffToStudent.csv to current courses based on the term's start and end dates. This fixes courses for districts who have multiple terms per year.
  • Version 1.3 (2022-04-27): Support CatapultCONNECT: additional columns in StudentExport.csv (zipCode, grade), plus AttendanceExport.csv.
  • Version 1.2 (2021-12-13): Add student and parent/guardian contact info to StudentExport.csv.
  • Version 1.1 (2021-12-02): Updated column header names (Removed dots and underscores to simplify installation. Fixed typo.)
  • Version 1.0 (2021-11-19): Initial release.

Customize the export plugin

  1. Download the PowerSchool Export Plugin (see top of page)
  2. Extract the zip file
  3. Edit the file you want to change (see below for some common changes).
    1. Staff data is under queries_root > Catapult.Staff.named_queries.xml
    2. Student and parent/guardian data is under queries_root > Catapult.Student.named_queries.xml
    3. Rosters are under queries_root > Catapult.StaffToStudent.named_queries.xml
  4. Save your changes
  5. Zip the files back together
  6. Upload to PowerSchool.

Staff phone numbers

Since PowerSchool doesn’t have a standard field for staff mobile numbers, the plugin exports a blank mobile number. A couple options:

  1. Collect the phone numbers in CatapultEMS directly (see Send Confirmation Emails).
  2. Customize the export plugin to pull from the field your district uses in PowerSchool (see examples below). The disadvantage of this method is if you ever update to a more recent version of the plugin, you'll need to reapply your custom changes.

Add staff mobile number

Within the plugin, open queries_root > Catapult.Staff.named_queries.xml.

If your district stores personal numbers in the home phone field, you can replace null with users.Home_Phone (see bold text below for context).

select
    users.TeacherNumber as a_id,
    users.homeschoolid as b_school,
    users.first_name as c_namefirst,
    users.last_name as d_namelast,
    users.email_addr as e_email,
    users.Home_Phone as f_mobilenumber,
    users.title as g_title

from users
left join schoolstaff on users.dcid = schoolstaff.users_dcid
where schoolstaff.status = 1

Alternatively, if your district uses a custom field for staff mobile number, you need to find the exact table and field name in the export screens of PowerSchool. Once you have that, update as follows:

  • Replace null with U_DEF_EXT_SCHOOLSTAFF.mobile_phone (but replace the exact table and field names with the ones used by your district. The table name is before the dot, field name after the dot.)
  • Insert a line just before "where" with left join U_DEF_EXT_SCHOOLSTAFF on SCHOOLSTAFF.DCID = U_DEF_EXT_SCHOOLSTAFF.SchoolStaffDCID (but replace the exact table and field names with the ones used by your district. The table name is before the dot, field name after the dot.)
select
    users.TeacherNumber as a_id,
    users.homeschoolid as b_school,
    users.first_name as c_namefirst,
    users.last_name as d_namelast,
    users.email_addr as e_email,
    U_DEF_EXT_SCHOOLSTAFF.mobile_phone as f_mobilenumber,
    users.title as g_title

from users
left join schoolstaff on users.dcid = schoolstaff.users_dcid
left join U_DEF_EXT_SCHOOLSTAFF on SCHOOLSTAFF.DCID = U_DEF_EXT_SCHOOLSTAFF.SchoolStaffDCID
where schoolstaff.status = 1

Show rosters beyond the current term

Within the plugin, open queries_root > Catapult.StaffToStudent.named_queries.xml.

The plugin uploads course rosters one day before the current term. To upload courses 7 days before the current term, change firstday-1 to firsday-7.

Likewise, to increase the number of days after the term ends, change lastday+1 to a higher number.

where
    students.enroll_status = 0
    and teachers.status = 1
    and (sysdate between terms.firstday-1 and terms.lastday+1)

To avoid showing classes from two different terms at once, limit the total number of days to less than your district's shortest break between terms.