PowerSchool – Schoolzilla Pro

How We Connect

PowerSchool data are pulled nightly via a secured Open Database Connection (ODBC). Traffic is encrypted in transit and at rest and a successful connection requires an SSH server or VPN. For more information about ODBC and VPN, please review our Connecting your SIS to Schoolzilla via ODBC overview.

For more information about getting connected, please review our setup article here.

Mappings Overview

If a field can be further configured by a Schoolzilla admin or relates to metric configurations, then there will be a link to the relevant part of Data Configuration Settings below.

Students

Student demographic data is sourced from STUDENTS table. The documentation below represents the default mappings in place. Some of these mappings can be customized and are typically updated during initial setup. Customizable fields have an asterisk(*).

Key Field(s)	Mapping(s)
System Student ID	Values are populated using the STUDENT_NUMBER field in the STUDENTS table.
Full Name	Values are populated using the LAST_NAME and FIRST_NAME fields in the STUDENTS table.
Current Grade Level	Values are populated using the GRADE_LEVEL field in the STUDENTS table.
EL Status*	Values are not populated by default.
FRL Status*	Values are populated using the LUNCHSTATUS field in the STUDENTS table.
Foster Care Status*	Values are not populated by default.
Gender*	Values are populated using the GENDER field in the STUDENTS table.
Grade Level at the Time	Values are populated using the GRADE_LEVEL field in the STUDENTS table and the GRADE_LEVEL field in the REENROLLMENTS table.
Homeless Status*	Values are not populated by default.
Is SPED*	Values are not populated by default.
Migrant Status*	Values are not populated by default.
Military Family Status*	Values are not populated by default.
Primary Ethnicity*	Values are populated using the first valid option: "Multi" if a student has multiple records in the STUDENTRACE table. The VALUE_T field in the GEN table for the RACECD field in the STUDENTRACE table. The VALUE_T field in the GEN table for the ETHNICITY field in the STUDENTS table.
SPED Status*	Values are not populated by default.

Staff

A comprehensive list of teacher users is sourced from the TEACHERS table.

Key Field(s)	Mapping(s)
Full Name	Values are populated using the LAST_NAME and FIRST_NAME fields in the TEACHERS table.
Email	Values are populated using the EMAIL_ADDR field in the TEACHERS table.

Calendars

School calendar data is sourced from the CALENDAR_DAY and TERMS tables.

Key Field(s)	Mapping(s)
School Year	Calendars are linked to school years using the full year terms from the TERMS table. Full year terms are identified using the ID column where the ID ends in "00". Values are populated using the YEARID field in the TERMS table + 1991.
In Session	Days are considered "In Session" if the INSESSION field in the CALENDAR_DAY table is 1 for that school and day.

School Enrollment & Retention

Student school enrollment history is populated using the STUDENTS and REENROLLMENTS tables.

The student's ENTRYDATE must be less than the EXITDATE – 1 to be considered a valid enrollment record from either table. Current enrollments (e.g. enrollment records where today's date is between the ENTRYDATE and EXITDATE – 1) are excluded if present in the REENROLLMENTS table.

Key Field(s)	Mapping(s)
School Year	Enrollments are linked to school years using the first valid option: The ENTRYDATE field from the STUDENTS or REENROLLMENTS tables is between the FIRSTDAY and LASTDAY of a school year term (ISYEARREC = 1) in the TERMS table. For this option, values are populated using the YEARID field from the TERMS table + 1991. The ENTRYDATE field from the STUDENTS or REENROLLMENTS tables. If the month of the ENTRYDATE is July or later, the year of the ENTRYDATE + 1 is used. If the month of the ENTRYDATE is before July, the year of the ENTRYDATE is used.
Entry Date	Values are populated using the ENTRYDATE field in the STUDENTS or REENROLLMENTS table.
Exit Date	Values are populated using the EXITDATE field in the STUDENTS or REENROLLMENTS table. Because PowerSchool stores the day after the last day the student was enrolled as their EXITDATE, Schoolzilla subtracts 1 day from the EXITDATE.
Exit Code	Values are populated using the EXITCODE field in the STUDENTS or REENROLLMENTS tables.
No Show	Enrollments are considered No Show records if either of the following criteria are met: The EXITCODE column in the STUDENTS or REENROLLMENTS tables is 420, N420, 470, N470, or NS. The EXITCOMMENT contains "NoShow".
School	Enrollments are linked to schools using the SCHOOLID field in the STUDENTS or REENROLLMENTS tables.

Student Rosters (Class & Teacher Associations)

Students are rostered to classes and teachers using the CC, SECTIONS, and SECTIONTEACHER tables.

Key Field(s)	Mapping(s)
School Year	Class enrollments are linked to school years using the first valid option: The DATEENROLLED field from the CC table is between the FIRSTDAY and LASTDAY of a school year term (ISYEARREC = 1) in the TERMS table. For this option, values are populated using the YEARID field from the TERMS table + 1991. The DATEENROLLED field from the CC table. If the month of the DATEENROLLED is July or later, the year of the DATEENROLLED + 1 is used. If the month of the DATEENROLLED is before July, the year of the DATEENROLLED is used.
Class Entry Date	Values are populated using the DATEENROLLED field in the CC table.
Class Exit Date	Values are populated using the DATELEFT field in the CC table.
Staff	Class enrollments are linked to staff using the TEACHERID field in the SECTIONTEACHER table or the TEACHER field in the SECTION table.

Attendance

Daily attendance is calculated using the PSSIS_MEMBERSHIP_DEFAULTS view, ATTENDANCE and the ATTENDANCE_CONVERSION_ITEMS tables.

Key Field(s)

Mapping(s)

Attendance Value

Values are populated using the ATTENDANCE_VALUE field in the ATTENDANCE_CONVERSION_ITEMS table. The appropriate ATTENDANCE_VALUE to use is determined using the following logic:

Attendance Mode	Conversion Mode	Calculation Type	Attendance Value Input Value Used
Meeting	Period Day	Absent	PERIODS_ABSENT
Meeting	Period Day	Present	POTENTIAL_PERIODS_PRESENT – PERIODS_ABSENT
Daily	Code Day	N/A	ID from ATTENDANCE_CODE
Meeting	Time Day	N/A	POTENTIAL_MINUTES_PRESENT – MINUTES_ABSENT (set to 0 if < 0)
Meeting	Percent Time to Day	Present	POTENTIAL_MINUTES_PRESENT - MINUTES_ABSENT / POTENTIAL_MINUTES_PRESENT If POTENTIAL_MINUTES_PRESENT is 0, then NULL.
Daily	Time Day	N/A	TOTAL_MINUTES where CALCULATE_ADA_YN is "1"
Daily	Time Day	Enable ADA And Pass Time Deducts	TOTAL_MINUTES where CALCULATE_ADA_YN is "1". If NULL, POTENTIAL MINUTES_PRESENT.
Time	Time Day	N/A	TOTAL_MINUTES where PRESENT_STATUS_CD is "Present" and CALCULATE_ADA_YN is "1"
Interval	Time Day	N/A	TOTAL_MINUTES where PRESENT_STATUS_CD is "Present" and CALCULATE_ADA_YN is "1"

Populating this field accurately requires:

Students have valid FTE IDs for their enrolled School and Year.
Valid attendance conversions for all combinations of inputs above

Absence Code

Values are populated using the PRESENCE_STATUS_CD field in the ATTENDANCE_CODE table.

School

Attendance is linked to schools using the SCHOOLID field in the PSSIS_MEMBERSHIP_DEFAULTS view.

Behavior

Behavior data is loaded from both the Incident Manager and Logs.

For Incident Manager, only behavior records for the "offender" role type are loaded.

Key Field(s)	Mapping(s)
School Year	Behavior incidents are linked to a school year using the Incident Date (see mapping below) and School (see mapping below).
School	Behavior incidents are linked to a school using the SCHOOL_NUMBER field in the INCIDENT table.
Incident Date	Values are populated using the INCIDENT_TS field in the INCIDENT table.
Incident Time of Day	Values are populated using the INCIDENT_TS field in the INCIDENT table.
Behavior Category	Values are populated using the BEHAVIORCODE field in the INCIDENT_DETAIL table.
Behavior Location	Values are populated using the LOCATIONCODE field in the INCIDENT_DETAIL table.
Behavior Response	Values are populated using the INCIDENT_CATEGORY field in the INCIDENT_LU_CODE table.

For Logs, only the primary behavior associated with a behavior incident (LOGTYPEID = -100000) is loaded.

Key Field(s)	Mapping(s)
School Year	Behavior incidents are linked to school years using the Incident Date (see mapping below) and School (see mapping below).
School	Behavior incidents are linked to school using the SCHOOLID field in the LOGS table.
Incident Date	Values are populated using the first valid option: DISCIPLINE_INCIDENTDATE field in the LOGS table. ENTRY_DATE field in the LOGS table.
Incident Time of Day	Not currently supported.
Behavior Category	Values are populated using the SUBTYPE field in the LOGS table. All code values are translated using the GEN table.
Behavior Location	Not currently supported.
Behavior Response	Values are populated using the first valid option: The CONSEQUENCE field in the LOGS table. The DISCIPLINE_ACTIONTAKEN field in the LOGS table. All code values are translated into descriptions using the GEN table.

GPA

No pre-calculated GPA data is available in PowerSchool. Schoolzilla-Calculated Cumulative and Term GPA is available based on loaded final (and in-progress for projected cumulative GPA) grades data below. Read more about the Schoolzilla GPA calculations here.

Key Field(s)

Mapping(s)

Exclude from GPA

All final grades in STOREDGRADES with an EXCLUDEFROMGPA value other than 0 are excluded from Schoolzilla GPA calculations.

All in-progress grades in PGFINALGRADES linked to courses in the COURSES table with an EXCLUDEFROMGPA value other than 0 are excluded from Schoolzilla Projected Cumulative GPA calculations. Courses are linked to grades in the gradebook based on the SECTIONID.

Grades

Grades data is sourced from both Stored Grades (STOREDGRADES) and PowerTeacher Gradebook and PowerTeacher Pro Grades (PGFINALGRADES). Preference is given to Stored Grades, so if a grade for the same course, student, section, etc. exists in both the STORED and PGFINALGRADES tables, only the Stored Grade will be loaded. The documentation below represents the default mappings in place. Some of these mappings can be customized and are typically updated during initial setup. Customizable fields have an asterisk(*).

Key Field(s)	Mapping(s)
School Year	Grades are linked to a school year using the YEARID field in the TERMS table + 1991.
School	Stored grades are linked to schools using the first valid option: SCHOOLID field in the SECTIONS table for the grade record. SCHOOLID field in the STOREDGRADES table for the grade record. Gradebook grades are linked to schools using the SCHOOLID field in the SECTIONS table for the grade record.
Course Subject*	Values are populated using the CREDITTYPE field in the COURSES table. For Stored Grades linked to courses without sections the CREDIT_TYPE field in STOREDGRADES is used.
Class Period	Values are populated using the PERIOD_NUMBER in the SECTION_MEETING table. The PERIOD_NUMBER for the first in-session day when the section meets is used as the PERIOD_NUMBER for the class. For courses that only appear on a student’s transcript (course is not CRS table), class period is not available.
Grade Name	Values are populated using the GRADE fields in the STOREDGRADES and PGFINALGRADES tables.
Grading Period	Stored Grades are linked to grading periods using the TERMID, SCHOOLID, and STORECODE fields. Gradebook Grades are linked to grading periods using the TERMID, SCHOOLID, and FINALGRADENAME fields. Values are populated using the STORECODE field in the TERMBINS table or the NAME field in the TERMS table.
Term	Grades are linked to terms using the TERMID and SCHOOLID fields. Values are populated using the ABBREVIATION field in the TERMS table.
Grade Type	Values are populated using the GRADESCALE_NAME field from the STOREDGRADES table for Stored Grades. Values are populated using the NAME field from GRADESCALEITEM for in-progress.
Grade Status	All Stored Grades loaded from STOREDGRADES will have a status of "Final". All Gradebook Grades loaded from PGFINALGRADES will have a status of "In Progress".

Known Limitations

Behavior Category – PowerSchool's behavior sub-codes cannot be used to populate the Behavior Category. Only the BEHAVIORCODE is supported at this time.

Behavior Location – Not currently supported for LOGS data. This is available if Incident Manager is used.

Behavior Response – Response codes rather than descriptions of codes are loaded from the Behavior LOGS table. If you are unsure of the meaning of these code values, please contact support.

Behavior Time of Day – Not currently supported for LOGS data. This is available if Incident Manager is used.

Frequent Data Quality Issues

For the issues below, PowerSchool support and help documentation is the best resource for helping to answer and resolve configuration questions/issues. Schoolzilla has, with the assistance of customers like you, documented these issues as they have arisen but has limited knowledge and expertise of how to resolve errors within PowerSchool.

The following issue is likely to impact the accuracy of your enrollment totals. If you are seeing enrollment numbers that are higher or lower than expected, please be sure the following issue is not present in PowerSchool.

Improper Handling of "No Show" enrollments – No Show enrollment must be exited with a standard no show exit code (e.g. 420, N420, 470, N470, or NS0) OR include "NoShow" in the comment to be identified as a "No Show" enrollment.
If no-show records from > 90 days ago are modified, please contact support to request a full refresh of your data.

The following issues are likely to impact the accuracy of your attendance and chronic absence metrics. If you are missing attendance data at one or more schools, please be sure none of the following issues are present in PowerSchool.

Improper Calendar Setup with Insufficient Membership Days – Attendance is only calculated for days in a school specified as "membership days".
Accurate calendars that reflect appropriate membership days are required to calculate daily attendance.

If calendar days > 90 days ago are modified, please contact support to request a full refresh of your attendance data.
Missing Attendance Conversion Items – Attendance Conversion Items determine what constitutes Attendance Value for a student on a day. They are, therefore, required to calculate a daily attendance value based on your attendance calculation configurations (e.g. period day, time, etc.). Attendance Conversion Items can often be missed in the following situations:
- New attendance codes are added for the school year but no attendance conversions are added for those new codes for relevant schools and FTEs.
- Scheduled time of day or periods change and attendance conversions are not added for that new possible time present/absent.
To fix, attendance conversion items for each school, year and FTE should be reviewed for comprehensive coverage. Complete Attendance Conversion Items are required to calculate daily attendance.

If attendance conversions that were used >90 days ago are modified, please contact support to request a full refresh of your attendance data.
Invalid Enrollment Dates - A student can be enrolled at a school before the year for their school starts. A common scenario is when families intend to enroll their student in the district next year, so they "pre-enroll" the student. The pre-enrolled date rather than the first day of the upcoming school year is used.

A valid enrollment with an entry date on or after the first day of the school year for the student's school is required to calculate daily attendance.

If enrollment dates from >90 days ago are modified, please contact support to request a full refresh of your attendance data.
Invalid FTE ID – Students are associated with FTE IDs in PowerSchool. These FTE IDs are linked to Schools and Years. Students may be associated with an invalid FTE ID if they transferred Schools and their FTE ID was not updated or if their enrollment record remains associated with an FTE ID for a prior year. A valid FTE ID is required to calculate daily attendance. If FTE IDs are updated that have been associated with students for >90 days, please contact support to request a full refresh of your attendance data.
PowerSchool Attendance View Calculating during data Refresh – Schoolzilla leverages the PSSIS_MEMBERSHIP_DEFAULTS_VIEW to calculate daily attendance. This view is calculated nightly in PowerSchool. Schoolzilla’s nightly refresh must be scheduled outside of the time period the view is refreshing to ensure the data pulled is complete and accurate.

The PSSIS_MEMBERSHIP_DEFAULTS_VIEW must have finished calculating before Schoolzilla’s nightly refresh starts to calculate daily attendance.

If attendance data is missing for periods of time (e.g. the last month), please determine the start and end time (including timezone) that your attendance calculations are running in PowerSchool and then contact support to request your nightly job be scheduled outside that window.

The following issue is likely to impact the accuracy of your attendance and chronic absence metrics. If you are seeing attendance results that are higher or lower than expected at one or more schools, please be sure the following issue is not present in PowerSchool.

Invalid Attendance Conversion Items (or Flipped Calculation Count) – For Meeting attendance with period day conversions, attendance values can be calculated based on the students' "presence" or "absence" from school in a day. If calculated using "presence", the attendance conversions should represent a translation of periods present to attendance value (e.g. 0 periods present should NOT have an attendance value > 1). If calculated using "absence", the attendance conversions should represent a translation of periods absent to attendance value (e.g. 0 periods absent should NOT have an attendance value = 0). Complete and valid attendance conversion items are required to calculate daily attendance. If attendance conversions that were used >90 days ago are modified, please contact support to request a full refresh of your attendance data.

Frequently Asked Questions

Please see Common PowerSchool issues and questions article.