Resource Overview
-
Ed-Fi endpoint:
/data/v3/studentSchoolAssociations -
Published view/entity: School Enrollment
-
Purpose: Students and the schools where they are enrolled
-
Source query:
StudentSchoolAssociation -
Dependencies:
calendars,students
This association represents the school in which a student is enrolled.
Primary PowerSchool Source Data
-
[Students]
-
[Schools]
-
[S_TN_STU_X]
-
[S_TN_REN_X]
-
[ReEnrollment]
Selection Criteria
Records are published if the following criteria are met:
-
The student was published in the Student category.
-
StudentUniqueId must be populated in PowerSchool.
-
The student must not be excluded from state reporting.
-
The school must not be excluded from state reporting.
-
The student must have at least one school enrollment with an Entry Date that falls on or within the date range defined by [Terms]FirstDay and [Terms]LastDay of the year for the Term selected in the PowerSchool UI.
-
The enrollment ExitDate is greater than the EntryDate.
Resource Output
-
A separate studentSchoolAssociation record is published for each student and school enrollment reflected in the [Students] and [Reenrollments] tables based on the year for the Term selected in the PowerSchool UI.
-
If the student is or was enrolled in a single school, one record is published.
-
If the student was enrolled in a single school, withdrew, and later re-enrolled in the same school, two records are published.
-
If the student was enrolled in multiple schools, or has a concurrent enrollment, a record is published for each enrollment.
-
Resource Output
|
Data Element (Ed-Fi target) |
Data View Column Name |
Submission |
[Table]Field (PowerSchool Source) |
Business Rules |
Comments |
|---|---|---|---|---|---|
|
entryDate |
EntryDate |
Y |
Publishing framework or supporting query Query alias: EntryDate |
Required by the production resource definition when the resource is published. |
JSON type: datetime. Query alias: EntryDate. |
|
calendarReference.calendarCode |
CalendarCode |
Y |
[Students]Track; [Schools]DCID; Transformation: case when s.track is not null then (select lpad(min(vt.instructionalprogramnbr),2,'0') from s_tn_sch_instrProgram_c vt where vt.schoolsdcid = sch.dcid and vt.yearid = p_yearid and vt.powerschooltrack = s.track) else (select lpad(min(vt.instructionalprogramnbr),2,'0') from s_tn_sch_instrProgram_c vt where vt.schoolsdcid = sch.dcid and vt.yearid = p_yearid and vt.powerschooltrack is null) end |
Required by the production resource definition when the resource is published. |
JSON type: string. Query alias: CalendarCode. |
|
calendarReference.schoolId |
SchoolOrgID |
Y |
Publishing framework or supporting query Query alias: SchoolOrgID |
Required by the production resource definition when the resource is published. |
JSON type: string. Query alias: SchoolOrgID. |
|
calendarReference.schoolYear |
SchoolYear |
Y |
Staging rule: studentSchoolAssociations p_yearid + 1991 |
Required by the production resource definition when the resource is published. |
JSON type: string. Query alias: SchoolYear. |
|
classOfSchoolYearTypeReference.schoolYear |
ClassOfSchoolYear |
Y |
[Students]ClassOf |
Required by the production resource definition when the resource is published. |
JSON type: number. Query alias: ClassOfSchoolYear. |
|
schoolReference.schoolId |
SchoolOrgID |
Y |
Publishing framework or supporting query Query alias: SchoolOrgID |
Required by the production resource definition when the resource is published. |
JSON type: string. Query alias: SchoolOrgID. |
|
schoolYearTypeReference.schoolYear |
SchoolYear |
Y |
Staging rule: studentSchoolAssociations p_yearid + 1991 |
Required by the production resource definition when the resource is published. |
JSON type: number. Query alias: SchoolYear. |
|
studentReference.studentUniqueId |
StudentUID |
Y |
[Students]DCID; Transformation: EDFI_TN_LIB.getStudentUniqueId(p_profileid, s.dcid) |
Required by the production resource definition when the resource is published. |
JSON type: string. Query alias: StudentUID. |
|
employedWhileEnrolled |
EmployedWhileEnrolled |
Conditional |
Publishing framework or supporting query Query alias: EmployedWhileEnrolled |
Submit when the source alias is populated and the parent structure is applicable. |
JSON type: boolean. Query alias: EmployedWhileEnrolled. |
|
entryGradeLevelDescriptor |
EntryGradeLevel_DX |
Y |
[Students]Grade_Level; [S_TN_STU_X]TransitionalGrade; transformation: EDFI_TN_LIB.getGradeLevelDescriptor(p_profileid, s.Grade_Level, xts.TransitionalGrade) |
Required by the production resource definition when the resource is published. |
JSON type: string. Query alias: EntryGradeLevel_DX. |
|
entryGradeLevelReasonDescriptor |
EntryGradeLevelReason_DX |
Conditional |
[S_TN_STU_X]Endofserviceaction; Transformation: CST_PUBWORKFLOW_LIB.getCodeSetValue(p_profileid, 'entryGradeLevelReasonDescriptors', xts.endofserviceaction) |
Submit when the source alias is populated and the parent structure is applicable. |
JSON type: string. Query alias: EntryGradeLevelReason_DX. |
|
entryTypeDescriptor |
EntryType_DX |
Conditional |
[Students]EntryCode; transformation: CST_PUBWORKFLOW_LIB.getCodeSetValue(p_profileid, 'entryTypeDescriptors', s.EntryCode) |
Submit when the source alias is populated and the parent structure is applicable. |
JSON type: string. Query alias: EntryType_DX. |
|
exitWithdrawDate |
ExitEndDate |
Conditional |
[Students]Exitcode; [Students]EXITDATE; [Students]ENTRYDATE; Transformation: case when s.exitcode is not null then CASE WHEN s.EXITDATE > s.ENTRYDATE THEN to_char(s.EXITDATE, 'YYYY-MM-DD') ELSE to_char(s.ENTRYDATE, 'YYYY-MM-DD') END end |
Submit when the source alias is populated and the parent structure is applicable. |
JSON type: datetime. Query alias: ExitEndDate. |
|
exitWithdrawTypeDescriptor |
ExitType_DX |
Conditional |
[Students]ExitCode; Transformation: CST_PUBWORKFLOW_LIB.getCodeSetValue(p_profileid, 'exitWithdrawTypeDescriptors', case when s.ExitCode in ('00','0') then s.ExitCode when length(s.ExitCode) > 1 then ltrim(s.ExitCode,'0') else s.ExitCode end ) |
Submit when the source alias is populated and the parent structure is applicable. |
JSON type: string. Query alias: ExitType_DX. |
|
primarySchool |
PrimarySchool |
Conditional |
[CST_PUBPUBLISHED_TMP]Compositekeys; [Reenrollments]ID; Transformation: case regexp_substr(pt.compositekeys, '[^\|]+', 1, 1) when 'S' then case when upper(ps_customfields.getcf('students', 'TN_ISPToS')) = 'S' then '0' else '1' end when 'R' then case when upper(ps_customfields.getcf('reenrollments', r1.ID, 'TN_ISPToS')) = 'S' then '0' else '1' end else '0' end |
Submit when the source alias is populated and the parent structure is applicable. |
JSON type: boolean. Query alias: PrimarySchool. |
|
repeatGradeIndicator |
RepeatGradeIndicator |
Conditional |
[CST_PUBPUBLISHED_TMP]Compositekeys; Transformation: case when regexp_substr(pt.compositekeys, '[^\|]+', 1, 1) = 'S' then case when upper(ps_customfields.getcf('students', 'TN_EndofServiceAction')) in ('R', 'D') then '1' else '0' end else null end |
Submit when the source alias is populated and the parent structure is applicable. |
JSON type: boolean. Query alias: RepeatGradeIndicator. |
|
residencyStatusDescriptor |
ResidencyStatus_DX |
Conditional |
Publishing framework or supporting query Query alias: ResidencyStatus_DX |
Submit when the source alias is populated and the parent structure is applicable. |
JSON type: string. Query alias: ResidencyStatus_DX. |
|
schoolChoiceTransfer |
SchoolChoiceTransfer |
Conditional |
[S_TN_STU_X]Schoolchoiceapplied; Transformation: case when xts.schoolchoiceapplied = 'Y' then 1 else 0 end |
Submit when the source alias is populated and the parent structure is applicable. |
JSON type: boolean. Query alias: SchoolChoiceTransfer. |
|
_ext.studentStandardDays[].effectiveDate |
EffectiveDate |
Y |
[Students]Exitdate; [Students]Entrydate; [Students]EntryDate; Transformation: case when s.exitdate IS NULL OR s.exitdate > s.entrydate then to_char(s.EntryDate, 'YYYY-MM-DD') end |
Required by the production resource definition when the resource is published. |
JSON type: datetime. Query alias: EffectiveDate. |
|
_ext.studentStandardDays[].studentStandardDayDuration |
TotalInstructionalMinutes |
Y |
[Students]Exitdate; [Students]entrydate; [S_TN_STU_X]studentstandardday; Transformation: CASE WHEN s.exitdate IS NULL OR s.exitdate > s.entrydate THEN COALESCE(xts.studentstandardday, instr.studentdaylength) END |
Required by the production resource definition when the resource is published. |
JSON type: number. Query alias: TotalInstructionalMinutes. |