The Ellevation Zap, available to users of eSD GURU, allows school districts to automatically send their data to Ellevation on a nightly basis. The Ellevation Zap includes a wealth of student data fields, including fields specifically related to ELL program participation. The Zap also includes student course schedules and staff roster information, allowing easy setup of Ellevation features such as Monitoring and Instructional Strategies for classroom teachers.
Business Rules
Student Demographics
File Name format: YYYYMMDD_Students.csv
Today's date will be used for the file name
Criteria:
- Only lists demographics for current students who were ever enrolled in 0231 (ELL Eligible) or 8239 (ELL Eligible but not in an ELL Program).
- Just to clarify, when we say ever enrolled, meaning if the student had or has an undeleted status record for these codes at any point in time during their enrollment in eSchoolData.
| Field Headers | Current eSD Business Rules |
|---|---|
| FirstName | First name of student |
| MiddleName | Middle name of student |
| LastName | Last name of student |
| ActiveStatus | Always "Yes" |
| SchoolCode | If GURU's Source Year is the current year, display the location code of the student's current school If GURU's Source Year is the next year, display the location code of the student's scheduling school |
| SchoolName | If GURU's Source Year is the current year, display the school name of the student's current school If GURU's Source Year is the next year, display the school name of the student's scheduling school |
| AlternateSchool | Blank |
| ESLTeacher | Blank |
| StudentID | Student's ID number |
| StudentStateID | Student's ID number |
| GradeLevel | If GURU's Source Year is the current year, check the student's current grade If GURU's Source Year is the next year, check the student's scheduling grade 1. If the grade starts with "P", display "Pre-K". 2. Else display the grade as is. |
| Gender | The gender code of the student. |
| BirthDate | The student's date of birth in MM/DD/YYYY format. |
| AddressLine1 | Display the student's household's mailing street number, street suffix, and street name together. |
| AddressLine2 | Display the student's household's mailing apartment with "Apt: " added at the start, and the student's household's mailing condo/community with "Unit: " added at the start. |
| City | Student's household's mailing city. |
| State | Student's household's mailing state abbreviation |
| ZipCode | Student's household's mailing zip |
| HomePhone | Student's household phone number |
| CellPhone | Student's cell phone number |
| BirthCity | Student's birth city |
| BirthCountry | Student's birth country name |
| BirthCountryCode | Student's birth country code (SIF codes) |
| NativeLanguage | Student's language name |
| NativeLanguageCode | Student's language code (SIF codes) |
| HomeLanguage | Student's household's language name |
| HomeLanguageCode | Student's household's language code (SIF codes) |
| Ethnicity | If the student's Hispanic Indicator flag is on, display "Hispanic/Latino", else display "Not Hispanic/Latino" |
| RaceAmericanIndian | Display "Yes" if the student's race is American Indian/Alaska Native or has a multiracial selection for American Indian/Alaska Native |
| RaceAsian | Display "Yes" if the student's race is Asian or has a multiracial selection for Asian |
| RaceBlack | Display "Yes" if the student's race is Black/African American or has a multiracial selection for Black/African American |
| RaceWhite | Display "Yes" if the student's race is White or has a multiracial selection for White |
| RacePacific | Display "Yes" if the student's race is Native Hawaiian/Other Pacific Islander or has a multiracial selection for Native Hawaiian/Other Pacific Islander |
| ELPDesignation | Display "ELL" |
| LEPStatus | 1. If the student has a current 0231 or 8239 student status, display "Current ELL" 2. If the student has a 0231 or 8239 status that has ended less then or at 730 days ago as of today, display "Monitored" 3. If the student has a 0231 or 8239 status that has ended more then 730 days ago as of today, display "Fully Exited" |
| CurrentTier | Blank |
| MonitoringStatus | 1. If the student has a 0231 or 8239 status that has ended less then 365 days ago as of today, display "Monitoring Year 1" 2. If the student has a 0231 or 8239 status that has ended more then 365 days ago and less than 730 days ago as of today, display "Monitoring Year 2" 3. Else display "Not Monitored" |
| ESLProgram | Blank |
| BilingualProgram | Blank |
| ReceivesESLServices | If the student has the following Program Service codes that are current or has ended, display "Yes", else display "No": 1. 5698 - (*Last used 2015-2016) LEP Other Program 2. 5709 - English as a New Language 3. 5687 - One Way or Two Way Dual Language Program 4. 5676 - Transitional Bilingual Education (TBE) Program |
| YearsInUSSchools | Display the number of years the student has been enrolled in eSD by their enrollment records, excluding enrollment records where the student has been in a grade level starting with P (PA, PK, PKA, PKF, PKH, PKP, PP, PS, PS2), plus the number of years set in the student's "Years in US Schools (Prior to this System)" |
| DateEnteredUS | Student's Date Enter USA in MM/DD/YYYY format |
| DateEnrolledInUS | Blank |
| DateEnrolledInDistrict | Student's Enrollment Date in MM/DD/YYYY format |
| HomeLanguageSurvey | Blank |
| ParentGrantedPermission | Blank |
| ParentDeniedPermission | Blank |
| DateWithdrawn | Blank |
| DateGraduated | Always blank since the output only sends out active students and a student cannot simultaneously be active and graduated |
| DateDroppedOut | Always blank since the output only sends out active students and a student cannot simultaneously be active and dropped out |
| DateEnteredLEP | Display the start date of the student's 0231 or 8239 status code in MM/DD/YYYY format |
| DateExitedLEP | Display the end date of the student's 0231 or 8239 status code in MM/DD/YYYY format if the status has ended |
| DateMonitoringStarted | Display the end date of the student's 0231 or 8239 status code in MM/DD/YYYY format if the status has ended |
| Homebound | If GURU's Source Year is the current year: 1. If the student has a current grade enrollment with the override flag on, display "Yes" if its location code override is 0777 and display "No" for any other location code. 2. If the student does not have the above and has a current school enrollment with the override flag on, display "Yes" if its location code override is 0777 and display "No" for any other location code. 3. If the student does not have the above two, display "Yes" if the student's current school has a location code of 0777 and display "No" for any other location code. If GURU's Source Year is the next year and the student has a scheduling school with the location code 0777, display "Yes", else display "No" |
| IsMigrant | If the student has a currently active Student Status record with the code 0330 (Migrant (NCLB)), display "Migrant", else display "Not Migrant" |
| Immigrant | If the student's Birth Country is United State of America or has no Birth Country assigned, display "No", else display "Yes" |
| NOM | Blank; field is National Origin Minority and is not captured in eSD |
| RefusedESL | Blank |
| DroppedOut | Always "No" since the output only sends out active students and a student cannot simultaneously be active and dropped out |
| Graduated | Always "No" since the output only sends out active students and a student cannot simultaneously be active and graduated |
| Withdrawn | Blank |
| WithdrawnOrGraduated | Blank |
| Deceased | Always "No" since the output only sends out active students and a student cannot simultaneously be active and deceased |
| Homeless | Display "Yes" if the student has a currently active Student Status record with the code HML (Homeless - inactive - use 8262), 8272 (Homeless Unaccompanied Youth Status) or 8262 (Homeless Student Status), else display "No" |
| GiftedAndTalented | Blank |
| Bilingual | Blank |
| DualLanguageProgram | Blank |
| HasIEP | If the student has any of the following Special Education status code that is currently active, display "IEP", else display "Not IEP": 1. 0352 - Autism 2. 0407 - Deaf 3. 0484 - Deaf-Blindness 4. 0418 - Hearing Impairment 5. 0385 - Learning Disability 6. 0396 - Intellectual Disability 7. 0473 - Multiple Disabilities 8. 0451 - Orthopedic Impairment 9. 0462 - Other Health Impairment 10. 5786 - Pre-School Student with a Disability 11. 0429 - Speech or Language Impairment 12. 0495 - Traumatic Brain Injury 13. 0440 - Visual Impairment 14. SE - Special Education |
| Is504 | If the student has any of the following Special Education status code that is currently active, display "504", else display "Not 504": 1. 0264 - 504 Plan in place 2. 5775 - Eligible for safety net in All Subjects 3. 0550 - 504 w/Safety Net [English] RETIRED 6-30-2018 4. 0583 - 504 w/Safety Net [Global H&G] RETIRED 6-30-2018 5. 0572 - 504 w/Safety Net [Math] RETIRED 6-30-2018 6. 0605 - 504 w/Safety Net [Science] RETIRED 6-30-2018 7. 0594 - 504 w/Safety Net [US H&G] RETIRED 6-30-2018 |
| SIFE | If the student has a currently active Student Status record with the code 1232 (Students with Inconsistent/Interrupted Formal Education (SIFE)), display "SIFE", else display "Not SIFE" |
| HomeLanguageSurveyOnRecord | Blank |
| DyslexiaProgram | Blank |
| ParentDeniedBilingualEd | Blank |
| AsyleeOrRefugee | Blank |
| FatherName | First name and last name of the student's contact with the "Father" relationship and either is a Guardian with the correspondence flag on or is an Emergency Contact. If there are multiple "Father" contacts, use the contact first entered in the eSD system. |
| FatherPhone | First entered phone number of the student's contact with the "Father" relationship and either is a Guardian with the correspondence flag on or is an Emergency Contact. If there are multiple "Father" contacts, use the contact first entered in the eSD system. |
| FatherEmail | First entered email of the student's contact with the "Father" relationship and either is a Guardian with the correspondence flag on or is an Emergency Contact. If there are multiple "Father" contacts, use the contact first entered in the eSD system. |
| FatherWorkplace | Employer name of the student's contact with the "Father" relationship and either is a Guardian with the correspondence flag on or is an Emergency Contact. If there are multiple "Father" contacts, use the contact first entered in the eSD system. |
| FatherNeedsInterpreting | If the student's contact with the "Father" relationship and either is a Guardian with the correspondence flag on or is an Emergency Contact is in the same household as the student and the household has the "Language Translation Needed" flag on, display "Yes", else display "No". If there are multiple "Father" contacts, use the contact first entered in the eSD system. |
| MotherName | First name and last name of the student's contact with the "Mother" relationship and either is a Guardian with the correspondence flag on or is an Emergency Contact. If there are multiple "Mother" contacts, use the contact first entered in the eSD system. |
| MotherPhone | First entered phone number of the student's contact with the "Mother" relationship and either is a Guardian with the correspondence flag on or is an Emergency Contact. If there are multiple "Mother" contacts, use the contact first entered in the eSD system. |
| MotherEmail | First entered email of the student's contact with the "Mother" relationship and either is a Guardian with the correspondence flag on or is an Emergency Contact. If there are multiple "Mother" contacts, use the contact first entered in the eSD system. |
| MotherWorkplace | Employer name of the student's contact with the "Mother" relationship and either is a Guardian with the correspondence flag on or is an Emergency Contact. If there are multiple "Mother" contacts, use the contact first entered in the eSD system. |
| MotherNeedsInterpreting | If the student's contact with the "Mother" relationship and either is a Guardian with the correspondence flag on or is an Emergency Contact is in the same household as the student and the household has the "Language Translation Needed" flag on, display "Yes", else display "No". If there are multiple "Mother" contacts, use the contact first entered in the eSD system. |
| EmergencyContactName | First name and last name of the student's Emergency Contact with the highest priority among the student's other Emergency Contacts |
| EmergencyContactPhone | Phone number of the student's Emergency Contact with the highest priority among the student's other Emergency Contacts |
| EmergencyContactEmail | Email of the student's Emergency Contact with the highest priority among the student's other Emergency Contacts |
| EmergencyContactWorkplace | Blank |
| EmergencyContactNeedsInterpreting | Blank |
| GuardianName | First name and last name of the student's Guardian with the Correspondence flag on and with the highest priority among the student's other Guardians |
| GuardianPhone | First entered phone number of the student's Guardian with the Correspondence flag on and with the highest priority among the student's other Guardians |
| GuardianEmail | First entered email of the student's Guardian with the Correspondence flag on and with the highest priority among the student's other Guardians |
| GuardianNeedsInterpreting | If the student's Guardian with the Correspondence flag on and with the highest priority among the student's other Guardians is in the same household as the student and the household has the "Language Translation Needed" flag on, display "Yes", else display "No". |
| SpecialEdAndECInfo | If the student has any of the following Special Education status code that is currently active, display one of those status codes, else leave blank 1. 0352 - Autism 2. 0407 - Deaf 3. 0484 - Deaf-Blindness 4. 0418 - Hearing Impairment 5. 0385 - Learning Disability 6. 0396 - Intellectual Disability 7. 0473 - Multiple Disabilities 8. 0451 - Orthopedic Impairment 9. 0462 - Other Health Impairment 10. 5786 - Pre-School Student with a Disability 11. 0429 - Speech or Language Impairment 12. 0495 - Traumatic Brain Injury 13. 0440 - Visual Impairment 14. SE - Special Education |
| StudentComment | Student's Comments as found in their registration page |
Student Demographics
File Name format: YYYYMMDD_Schedule.csv
Today's date will be used for the file name
Criteria:
- Current class enrollments for the source year will be pulled for students who are in the Student Demographics file and display information related to the class itself
- All the classes' meeting times will be pulled
- Courses with the subject "TBD" will be ignored
| Field Headers | eSD Business Rules |
|---|---|
| StudentID | Student's ID number |
| CourseName | Class's Course Name |
| CoursePeriod | Class meeting time's From Period |
| CourseCode | Class's course primary key in the back-end table |
| StaffId | The staff primary key of either the teacher directly assigned to the class meeting time, or of the teacher who is currently a co-teacher of the class. A record will be created for each meeting time teacher and co-teacher found. |
| TeacherName | First name and last name of the teacher pulled |
| CourseRange | Class meeting time's From Period and To Period in the "# #" format |
| LocalCourseCode | Class's Course Number |
| StateCourseCode | Class's State Course # |
| Subject | Class's Course Subject |
| StaffStateId | State ID Number of the teacher pulled |
| Role | If the teacher pulled is directly assigned to the class meeting time, display "Teacher of Record". If the teacher pulled is from a co-teacher record, display the co-teacher record's role |
| SectionID | Display information in the following format from the class meeting time: (Course Number)-(Meeting time primary key)-(From Period)-(To Period)-(Day Pattern without commas)-(Semester Pattern without commas) Example: E3000-29372-3-3-AB-1234 |
| Term | Display information in the following format from the class meeting time: (Last 2 digits of starting year)-(Last 2 digits of ending year) (School Code of the course's school) Semesters (Semester Pattern without commas) Example: 22-23 EHS Semesters 1234 |
| TermCourseStartDate | Start date of the class meeting time's beginning semester in the MM/DD/YYYY format |
| TermCourseEndDate | End date of the class meeting time's ending semester in the MM/DD/YYYY format |
Staff
File Name format: YYYYMMDD_Staff.csv
Today's date will be used for the file name
Every active building staff will be pulled regardless of the filters set in the Zap
| Field Headers | eSD Business Rules |
|---|---|
| LastName | Staff's last name |
| FirstName | Staff's first name |
| Id | Staff's primary key |
| eMailAddress | Staff's email address |
| LEACode | Staff's building's location code |
| SchoolAssignment | Staff's building name |
| StateId | Staff's state ID number |
NYSITELL Information
File Name format: YYYYMMDD_NYSITELLscores.csv
Today's date will be used for the file name
Criteria:
- NYSITELL assessment information from the GURU source year and the year before that will be displayed for students who are in the Student Demographics file. For example if the Source Year in GURU is set to be 2022 - 2023 then assessments related to 2022 - 2023 and 2021 - 2022 will be pulled (meaning the test dates (assessment dates) that fall within those two school years).
- NYSITELL assessments for the same student and date will have their scores displayed in the same row
| Field Headers | eSD Business Rules |
|---|---|
| StudentId | Student's ID Number |
| StudentStateId | Student's ID Number |
| TestDate | Student's assessment date |
| GradeLevel | Display the student's grade level as of the assessment date |
| Level | Derive the level (I, II, III, etc.) from the student's NYSITELL Total Score assessment with either the "Score" or "Level" scoring method |
| ListeningRawScore | Display the "Score" from the student's NYSITELL Listening assessment with matching assessment date |
| SpeakingRawScore | Display the "Score" from the student's NYSITELL Speaking assessment with matching assessment date |
| ReadingRawScore | Display the "Score" from the student's NYSITELL Reading assessment with matching assessment date |
| WritingRawScore | Display the "Score" from the student's NYSITELL Writing assessment with matching assessment date |
| TotalRawScore | Display the "Score" from the student's NYSITELL Total Score assessment with matching assessment date |
| PerformanceLevel | Display the "Level" from the student's NYSITELL Total Score assessment with matching assessment date. If the Level is one of the following, they will be converted: 1. "Entering", display 1 2. "Emerging", display 2 3. "Transition" or "Transitioning", display 3 4. "Expanding", display 4 5. "Commanding", display 5 |
NYSESLAT Information
File Name format: YYYYMMDD_NYSESLATscores.csv
Today's date will be used for the file name
Criteria:
- NYSESLAT assessment information from the GURU source year and the year before that will be displayed for students who are in the Student Demographics file. For example if the Source Year in GURU is set to be 2022 - 2023 then assessments related to 2022 - 2023 and 2021 - 2022 will be pulled (meaning the test dates (assessment dates) that fall within those two school years).
- The student's NYSESLAT Total Score assessments' Scaled Score will be used as the base for the output
- If the student does not have a NYSESLAT Total Score assessment with a Scaled Score for a specific assessment date, the student's matching Listening, Speaking, Reading and Writing scores for that date will not be included
| Field Headers | eSD Business Rules |
|---|---|
| StudentId | Student's ID Number |
| StudentStateId | Student's ID Number |
| TestDate | Student's assessment date |
| GradeLevel | Display the student's grade level as of the assessment date. If no such record exists, use the grade level of the first grade enrollment starting after the assessment date |
| GradeBand | Derive the grade band based on the NYSESLAT Total Score assessment used as the base for this record: NYSESLAT: K Total Score - K NYSESLAT: 1 Total Score - 1-2 NYSESLAT: 2 Total Score - 1-2 NYSESLAT: 3 Total Score - 3-4 NYSESLAT: 4 Total Score - 3-4 NYSESLAT: 5 Total Score - 5-6 NYSESLAT: 6 Total Score - 5-6 NYSESLAT: 7 Total Score - 7-8 NYSESLAT: 8 Total Score - 7-8 NYSESLAT: 9 Total Score - 9-12 NYSESLAT: 10 Total Score - 9-12 NYSESLAT: 11 Total Score - 9-12 NYSESLAT: 12 Total Score - 9-12 |
| ListeningRawScore | Display the "Score" from the student's NYSESLAT Listening assessment with matching assessment date and is in the same Grade Band as the NYSESLAT Total Score assessment base |
| SpeakingRawScore | Display the "Score" from the student's NYSESLAT Speaking assessment with matching assessment date and is in the same Grade Band as the NYSESLAT Total Score assessment base |
| ReadingRawScore | Display the "Score" from the student's NYSESLAT Reading assessment with matching assessment date and is in the same Grade Band as the NYSESLAT Total Score assessment base |
| WritingRawScore | Display the "Score" from the student's NYSESLAT Writing assessment with matching assessment date and is in the same Grade Band as the NYSESLAT Total Score assessment base |
| ListeningScaleScore | Display the "Scaled Score" from the student's NYSESLAT Listening assessment with matching assessment date and is in the same Grade Band as the NYSESLAT Total Score assessment base |
| SpeakingScaleScore | Display the "Scaled Score" from the student's NYSESLAT Speaking assessment with matching assessment date and is in the same Grade Band as the NYSESLAT Total Score assessment base |
| ReadingScaleScore | Display the "Scaled Score" from the student's NYSESLAT Reading assessment with matching assessment date and is in the same Grade Band as the NYSESLAT Total Score assessment base |
| WritingScaleScore | Display the "Scaled Score" from the student's NYSESLAT Writing assessment with matching assessment date and is in the same Grade Band as the NYSESLAT Total Score assessment base |
| TotalScaleScore | Display the "Scaled Score" from the student's NYSESLAT Total Score assessment that is used at the base for this record |
| PerformanceLevel | Display the "Level" from the student's NYSESLAT Total Score assessment with matching assessment date. If the Level is one of the following, they will be converted: 1. "Entering", display 1 2. "Emerging", display 2 3. "Transition" or "Transitioning", display 3 4. "Expanding", display 4 5. "Commanding", display 5 |