Foster Focus Data Link Specifications Revised: February 28, 2014 - - PDF document

SLIDE 1

 Foster Youth Services  10474 Mather Blvd., P.O. Box 269003, Sacramento, 95826-9003  (916) 228-2419

Foster Focus Data Link Specifications

Revised: February 28, 2014 Version: 1.62

SLIDE 2

P a g e | 2

Introduction

School Districts are encouraged to link their systems to Foster Focus via automated, nightly, secure uploads of delimited text data in 9 subject areas:  Demographic information  Enrollment History  Attendance Periods  Attendance Exceptions  Test Scores  Special Education  GPA  Behavior  Transcripts The District Link project grew out of efforts to link data between two specific student‐information systems (SIS) and Foster Focus. Current extract designs reflect the ways these two systems store data.

Basic File Formatting Rules

 Extracts must contain a header line to explain the configuration of columns.  System IDs are crucial to data linking, and records missing a systemID will never be loaded. System IDs always consist of a combination of the county code and the district code with no spaces to separate them.  Extracts must be tab‐delimited.  Extracts should have a reasonable end‐of‐line delimiter, such as ASCII code 10 or 13, or a combination of those characters.  Data should not be surrounded by quotes.  File names and formats should remain consistent from day to day. Changes to filenames can result in a failure to load data. Changes in file formats may result in incorrect data loading or a failure to load data at all.

Which Students to Include in the Extract

We require that only students identified as foster youth be included in the extracts. Many SIS include a field for this purpose. Starting July 1, 2014, districts will have to identify their foster youth for state reporting purposes. It is likely that companies that create SIS will include a standard field for identifying foster youth in future versions. The state program code has been identified as 190. It is important that you look for students flagged as foster youth using EITHER state standard reporting fields OR any custom fields that may have been created before July 1, 2014.

SLIDE 3

P a g e | 3

Security

Data extracts are confidential, and care must be taken to send extracts only in a secured manner. For this reason, our FTP server will only accept connections via the SFTP protocol. When setting up an account for your district on the Foster Focus FTP server, a team member will send the domain name and username to you via email, but the password will be transmitted over the phone.

Why Isn’t A Student Linked?

After linking, you may run across students that you feel should be linked but are not. Reasons a student might not be linked include:  A match cannot be made between your demographic records for that student and Foster Focus. If this is the case, then the student should appear on the child match report generated when your data is imported. Possible reasons for these problems are included in the report.  Student is not contained in the data load sent by CPS.  Student‘s status has been inactive for more than 6 months.  Student has graduated or is older than 19.*  Student is not flagged as a foster youth in your SIS and so is not in the demographic extract. *Age threshold will change over time in accordance with AB 12.

The Importance of Sending Complete Data

The required column in the data tables below indicates the importance of a field in an extract.  Required: Record will not be loaded without a value in this field.  Recommended: Field data is critically important in presenting a complete and accurate picture

• f student information

 Optional: Field data is helpful but not required.

Unique IDs

The import script attempts to match incoming data to existing data in order to decide whether incoming data is new and should be inserted or whether it already exists and should update the data that is already in the system. Existing data can be matched to incoming data in one of two ways.  If your district provides a unique ID in each extract (often corresponding to primary keys in your student information system), the import script can be configured to use a combination of your system ID and the unique ID provided in the extract(s) in order match the data.  If permanent unique IDs do not exist in your SIS, each extract will be compared using a combination of columns within the extract.

SLIDE 4

P a g e | 4 For example, the following student shows enrollment at Dewolf Continuation High for 12th grade in Foster Focus. The incoming extract data shows a correction. The student was actually enrolled in Bullard High and not Dewolf: If your extract contains a UniqueID value, we can configure Foster Focus to match records based on that UniqueID value. With that information, the student data will be matched and corrected properly: Without a UniqueID value, other columns are used to determine whether incoming data matches existing data. In this case, a key column used for matching has changed (the school), and the incoming data will not match to existing data. Without a unique ID, a new entry will be inserted, leaving the old incorrect entry.

SLIDE 5

P a g e | 5 For that reason, uniqueIDs are preferred as a method of matching incoming data to existing data. Without them, data will need to be monitored by your district for the types of problems listed above.

Other Steps in Data Linking

When linking is being established between your district and Foster Focus, there are some additional steps that need to be taken so that your data is interpreted correctly during import. School Lookup Table Mapping: Because multiple school sites or programs often use the same CDS code, we use your local school codes to map to specific school sites stored in Foster Focus. Depending on the way your district uses CDS codes, our data staff may need to coordinate with you during the mapping process. Grade Point Averages: The gpaTypeDescript field in the GPA extract contains a short description of GPA

• Types. We will need more specific information on how those GPAs are calculated so we can normalize

the data. Foster Focus staff will contact you to gather that information.

Field Descriptions

Some of the field descriptions in this document reference fields in two other commonly‐used data specifications:  CSIS Data Dictionary (v13 5/30/12) Available at: http://csis.fcmat.org/Documents/ReportingRequirements/FY1213/Data‐Dictionary‐ FY1213‐v13‐0‐20120530.xls

 CALPADS Data Guide (v4.1 8/29/12)

Available at: http://www.cde.ca.gov/ds/sp/cl/documents/dataguide082912.doc

SLIDE 6

P a g e | 6

Demographics

The demographics extract is the foundational piece of data linking. Data in this extract is used to match children from Foster Focus to your district system. Data from your extracts can only be uploaded when a match is made. Records that can’t be matched to Foster Focus data will be sent to your district in a daily report once the data link is live.

Columns

Column Name Definition Required? Data Type SystemID

Your district’s county code and district code as one continuous string (example: 3476447)

Required

String (50)

UniqueID

A permanent, unique ID in your SIS that matches this record.

Recommended

String(50)

SSID

Statewide student identifier.

Recommended

String (50)

Lastname

Student’s last name

Required

String (50)

Firstname

Student’s first name

Required

String (50)

Gender

Student’s gender (F or M)

Required

String (1)

Birthdate

Student’s birth date.

Required

Date

homeAddress

Street address of student’s residence.

Required

String (100)

EntryDate

Student’s first day of enrollment at current school for current school year.

Required

Date

ExitDate

Student’s last day of enrollment in school district.

Required

Date

SISLocalID

Student’s unique ID within your district’s

• SIS. Social Security Numbers are not

permitted.

Required

String (10)

HomeLanguageCode

CSIS Code for Primary Language spoken in student’s home (02.07.01).

Optional

String (10)

PrimaryLanguage

CSIS Code Description for child’s primary language

Optional

String (10)

Verify Data Meets: No. Demographics File Rule

1. File includes currently enrolled students. 2. File includes students who were enrolled earlier in the current school year but are no longer enrolled. 3. Students with no current school year enrollment are not present in the data.

SLIDE 7

P a g e | 7

Enrollment History

Information in this extract can be viewed on the Foster Focus front end under the Education section by clicking the School tab. This extract should include a separate row for each school year. Extracts should contain many rows of enrollment history per student. When constructing this extract, gather as much enrollment history as possible to provide the most complete picture of educational history for students. NOTE: Some SIS do not populate close dates automatically at the end of a school year, and enrollment end dates may not be hand‐entered by your district. If this is the case, please give that information to your Foster Focus technical contact. School year close dates are important to the functioning of the system, and we can automatically set closing dates for your open records.

Columns

Column Name Definition Required? Data Type SystemID

Your district’s county code and district code as one continuous string (example: 3476447)

Required

String (50)

UniqueID

A permanent, unique ID in your SIS that matches this record.

Recommended

String(50)

SSID

Statewide student identifier.

Recommended

String (50)

EntryDate

Student’s first day of enrollment in school for that school year.

Required

Date

ExitDate

Student’s last day of enrollment in particular school or term. Same as CSIS field Withdrawal Date (05.06)

Recommended

Date

EntryReason

Reason student is attending school.

Recommended

String (250) Example: Returning Student

ExitReason

Reason student left school. Should be CSIS Exit/Withdrawal Reason (05.07).

Required

String (250)

StatusCode

Student’s status in school district using local district status codes.

Optional

String (10)

Grade

Student’s grade level in school.

Required

String (10) String (ex. ‘K’) or numeric value denoting grade

SISSchoolCode

Unique identifier for school as used by district SIS.

Required

String (20)

SchoolName

Name of school.

Recommended

String (125)

Lastname

Student’s last name

Required

String (50)

Firstname

Student’s first name

Required

String (50)

SchoolCDSCode

Full CDS code for school (county code, district code, and schoolID)

Required

String (20)

SISLocalID

Student’s unique ID within your district’s SIS. Social Security Numbers are not permitted.

Required

String (10)

CompletionStatus

School completion status (graduated, GED, etc.) from CALPADS field 1.28. Import will accept alphanumeric code

• r text description of code as stated in

the CALPADS Data Guide.

Recommended

String(50)

SLIDE 8

P a g e | 8

Verify Data Meets: No. Enrollment File Rule

1. All records with an entry date in a prior school year also have an exit date. 2. All students have either one or zero open enrollments. 3. All prior year enrollment records are included for students who attended in the district in a previous school year. 4. All records contain a CDS code. 5. All records with an exit date and the grade level is 12, also includes a completion status. 6. Every child represented in the demographics file is also represented in this file. 7. If a child attended more than one school in the district in the current school year, both enrollments appear in this file with the appropriate CDS code for each school.

SLIDE 9

P a g e | 9

Attendance Periods

The Attendance Periods abstract contains information about a student’s rates of attendance. Each record represents a day and contains information on the school, the number of periods a student is enrolled (1 for all‐day attendance) and the number of periods a student officially attended.

Columns

Column Name Definition Required? Data Type SystemID

Your district’s county code and district code as one continuous string (example: 3476447)

Required

String (50)

UniqueID

A permanent, unique ID in your SIS that matches this record.

Recommended

String(50)

SSID

Statewide student identifier.

Required

String (50)

SchoolDate

Date of attendance information.

Required

Date

PeriodsEnrolled

Number of periods enrolled on SchoolDate.

Required

Integer > 0

PeriodsAttended

Number of periods attended on SchoolDate

Required

Integer

SchoolCDSCode

CDE full CDS code for school (county code, district code, and schoolID)

Required

String (20)

SISSchoolCode

Unique identifier for school as used by district SIS.

Required

String (20)

SchoolYear

School year (e.g. 2012 for the 2011‐ 2012 school year)

Optional

Numeric four‐digit year

SISLocalID

Student’s unique ID within your district’s SIS. Social Security Numbers are not permitted.

Required

String (10)

LastName

Student’s last name

Required

String (50)

FirstName

Student’s first name

Required

String (50)

Verify Data Meets: No. Attendance Periods File Rule

1. The SchoolDate field contains one entry for each child for each school day. 2. Every child represented in the demographics file is also represented in this file. 3. The PeriodsEnrolled field displays “1” for elementary school students and students in a self‐contained classroom. 4. PeriodAttended displays a digit equal to or less than the digit displayed in PeriodsEnrolled for each child. 5. If the student is enrolled in more than one school in the current school year, the CDS codes reflect this.

SLIDE 10

P a g e | 10

Attendance Exceptions

Attendance exceptions are records of attendance‐related events, such as tardies and absences.

Columns

Column Name Definition Required? Data Type SystemID

Your district’s county code and district code as one continuous string (example: 3476447)

Required

String (50)

UniqueID

A permanent, unique ID in your SIS that matches this record.

Recommended

String(50)

SSID

Statewide student identifier.

Required

String (50)

AttendDate

Date of attendance exception.

Required

Date

AttendCode

Code for the attendance exception description

Required

String (20) Example: S, U

AttendDescript

Description of the attendance exception

Required

String (250) Example: Suspended, Unverified Absence

Period

School period to which the attendance exception applies

Required

String (10) Example: 1, Period 1

CourseDescript

Course description. Same as CBEDS subject area of CSIS Course Title (08.05.07)

Required

String (250)

SchoolCDSCode

CDE full CDS code for school (county code, district code, and schoolID)

Required

String (20)

SISLocalID

Student’s unique ID within your district’s

• SIS. Social Security Numbers are not

permitted.

Required

String (10)

SISSchoolCode

Unique identifier for school as used by district SIS.

Required

String (20)

Lastname

Student’s last name

Required

String (50)

Firstname

Student’s first name

Required

String (50)

Verify Data Meets: No. Attendance Exception File Rule

1. Every student with an exception (one instance of periods attended is less than periods enrolled) also has a record in this extract on that date. 2.

The AttendDate field contains one entry for each period (day for elementary)

3.

Period is consistently defined for all rows

SLIDE 11

P a g e | 11

Test Scores

Every SIS has different ways of storing test scores. Zangle, for example, stores multiple rows for test scores, each representing different domains of the score (for example, one row will contain the scaled score, and another row will contain the proficiency level). Multiple rows per test are encouraged, but you will have to let your Foster Focus contact know what the testScoreType codes will be so that test scores can be interpreted correctly when they are loaded. Additionally, test score data is normalized to a standard set of tests in a Foster Focus lookup table. The test descriptions that appear at the front end may not exactly match the string sent in the extract, but they should describe the same test.

Columns

Column Name Definition Required? Data Type SystemID

Your district’s county code and district code as one continuous string (example: 3476447)

Required

String (50)

UniqueID

A permanent, unique ID in your SIS that matches this record.

Recommended

String(50)

SSID

Statewide student identifier.

Required

String (50)

TestDate

Date test was taken.

Required

Date

Grade

Student’s grade level in school when test was taken.

Recommended

String (10) String (ex. ‘K’) or numeric value denoting grade

TestScoreType

Code indicating type of scoring

Required

String (10) Example (S, P)

TestScore

Value representing test score.

Required

String (80) Example: 380, Basic

IsPassing

Code indicating whether passed or failed.

Required when CAHSEE

Bit (1/0) 1: Pass 0: Fail

TestType

Name of test taken.

Required

String (15) Example: STAR

TestSubType

Subtype of test taken

Required

String (50) Example: CST ELA

TestDescription

Longer test description

Recommended

String (100) Example: CST Math – Algebra I Proficiency Level

SISLocalID

Student’s unique ID within your district’s

• SIS. Social Security Numbers are not

permitted.

Required

String (10)

Lastname

Student’s last name

Required

String (40)

FirstName

Student’s first name

Required

String (40)

SisSchoolCode

Unique identifier for school attended when test occurred as used by district SIS.

Recommended

String (20)

SchoolCDSCode

CDE full CDS code for school attended when test occurred (county code, district code, and schoolID)

Recommended

String (20)

SLIDE 12

P a g e | 12

Verify Data Meets: No. Test File Rule

1. All records include a test date. 2. All records include the grade in which the child was enrolled at on the date the test was taken. 3. All records include the CDS code for the school where the child was enrolled on the date the test was taken. 4. Instances of CMA, CAPA, CST, CAHSEE and CELDT exist in the file if the tests exist in the SIS.

SLIDE 13

P a g e | 13

Special Education

Please do not send multiple rows per student in this extract. Send the row representing the student’s primary disability.

Columns

Column Name Definition Required? Data Type SystemID

Your district’s county code and district code as one continuous string (example: 3476447)

Required

String (50)

UniqueID

A permanent, unique ID in your SIS that matches this record.

Recommended

String(50)

SSID

Statewide student identifier.

Required

String (50)

SpecProgEDate

The date of the student’s most recent IEP meeting. Same as CSIS field Last IEP Date (07.18.01)

Recommended

String (50)

SpecProgXDate

Date student exited from Special Ed. Same as CSIS field Program Participation End Date (07.06)

Recommended

String (50)

SEDescript

Definition of primary disability category

• f student. Same as CSIS field Special

Education Disability Category definition.

Optional

String (100) Example: Hard of Hearing, Autism

SEServiceCode

Numeric code of primary disability category of student. Same CSIS field Special Education Disability Category (07.20).

Required

Numeric Example: 220, 260

SISLocalID

Student’s unique ID within your district’s

• SIS. Social Security Numbers are not

permitted.

Required

String (10)

LastName

Student’s last name

Required

String (50)

FirstName

Student’s first name

Required

String (50)

SISSchoolCode

Unique identifier for student’s current school as used by district SIS.

Recommended

String (20)

SchoolCDSCode

CDE full CDS code for current school (county code, district code, and schoolID)

Recommended

String (20)

Verify Data Meets: No. Special Education File Rule

1. The file contains only one row for each student. 2. Only students who appear in the demographics file appear in this file. 3. Only students who receive Special Ed. Services appear in this file.

SLIDE 14

P a g e | 14

GPA

Holds calculated GPA statistics for students. Multiple types of GPAs can be sent for the same student and calculation date, as long as the GPATypeDescript differentiates between calculation methods.

Columns

Column Name Definition Required? Data Type SystemID

Your district’s county code and district code as one continuous string (example: 3476447)

Required

String (50)

UniqueID

A permanent, unique ID in your SIS that matches this record.

Recommended

String(50)

SSID

Statewide student identifier.

Required

String (50)

TermDescript

Name/description of the term

Required

String (50) Example: Semester 1, Summer School

CalculationDate

Date the grade point average was calculated

Required

Date

GPATypeDescript

Description of the GPA Type

Required

String (32) Example: Middle School, High School Academic GPA

SchoolYear

School year (e.g. 2012 for the 2011‐2012 school year)

Required

Numeric four‐digit school year

Grade

Student’s grade level in school when GPA was earned.

Required

String (15) String (ex. ‘K’) or numeric value denoting grade

TermGPA

Grade point average for the term

Required

Float

SchoolCDSCode

CDE full CDS code for school (county code, district code, and schoolID)

Required

String (20)

SISLocalID

Student’s unique ID within your district’s

• SIS. Social Security Numbers are not

permitted.

Required

String (10)

SISSchoolCode

Unique identifier for school as used by district SIS.

Required

String (20)

Lastname

Student’s last name

Required

String (50)

Firstname

Student’s first name

Required

String (50)

SLIDE 15

P a g e | 15

Verify Data Meets: No. GPA File Rule

1. All records include a calculation date. 2. All records include TermDescript and this field corresponds to the grade level in which the child was enrolled during that term. 3. All records include a grade which is the grade level at the time that the student earned the GPA. 4. All entries have a SISSchoolCode and it is the code for the school the student attended when the GPA was earned. 5. All records include a CDS code and it is the code for the school that the child attended when the GPA was earned.

SLIDE 16

P a g e | 16

Behavior

Before loading into the system, an algorithm removes any records that mention police involvement.

Columns

Column Name Definition Required? Data Type SystemID

Your district’s county code and district code as one continuous string (example: 3476447)

Required

String (50)

UniqueID

A permanent, unique ID in your SIS that matches this record.

Recommended

String(50)

SSID

Statewide student identifier.

Required

String (50)

PenaltyTypeCode

Code for PenaltyTypeDesc. If multiple, can be comma‐delimited list. String (50)

PenaltyTypeDesc

Brief description of Penalty. If multiple, can be comma‐delimited list.

Recommended

Example: Principal Suspension, Tardy

BegServeDate

Date penalty began

Recommended

Date

NumServDays

Number of days of suspension. Same as CSIS Discipline Days (11.04)

Recommended

integer

PenEventDate

Date of incident. Same as CSIS Date of Incident (11.01)

Required

Date

EventSchoolCDSCode

CDS Code for school where event

• ccurred. CDE full CDS code for current

school (county code, district code, and schoolID)

Required

String (20)

SISLocalID

Student’s unique ID within your district’s

• SIS. Social Security Numbers are not

permitted.

Required

String (10)

LastName

Student’s last name

Required

String (50)

FirstName

Student’s first name

Required

String (50)

SISSchoolCode

Unique identifier for school where event

• ccurred as used by district SIS.

Recommended

String (20)

Verify Data Meets: No. Behavior File Rule

1. All records include an eventDate. 2.

All suspensions include a BegServeDate and a NumServDays

3. All records include a CDS code and the code corresponds to the school where the event

• ccurred not the current school where the student attends.

4. There may be multiple PenaltyTypes for the same PenEventDate. 5. Each PenaltyTypeDesc for a student, on a given day must appear in a single row. 6. The file includes all historical events for students.

SLIDE 17

P a g e | 17

Transcripts

Transcripts data links marks and credits earned to a school and a date. Partial credits are accepted.

Columns

Column Name Definition Required? Data Type SystemID

Your district’s county code and district code as one continuous string (example: 3476447)

Required

String (50)

UniqueID

A permanent, unique ID in your SIS that matches this record.

Recommended

String(50)

SSID

Statewide student identifier.

Required

String (50)

Grade

Student’s grade level in school.

Required

String (ex. ‘K’) or numeric value denoting grade

trDate

Date when transcript was processed

Required

Date

SchYear

School year (e.g. 2012 for the 2011‐2012 school year)

Required

Numeric four‐digit school year

TermDescript

The description for the term

Required

Example: Sem 2, Q3

SubjectDescript

Course subject description

Required

String (50)

CourseDescript

Course description. Same as CBEDS subject area of CSIS Course Title (08.05.07)

Recommended

String (50)

CourseNumber

Course code within local SIS System (CALPADS CRS‐Local Course ID 9.08)

Required

String(14)

StateCourseNumber

State course code (CALPADS CRS‐State Course Code 9.07)

Recommended

String(10)

GradCratt

Number of credits student attempted to earn for course. Same as CSIS Credits Attempted (08.05.22) Numeric

GradCrawd

The number of credits student earned for course. Same as CSIS Credits Earned (08.05.23) Numeric

GPACratt

Graduation credits attempted Numeric

GPACrawd

Graduation credits earned

Required

Numeric

markAwd1

The final mark earned by the student for a particular course. Same as CSIS Course Mark (Grade) 08.05.20.02

Required

String(15)

markDesc1

Description of markawd1 from SIS Example: Letter Marks, Pass/Fail)

SISSchoolCode

Unique identifier for credit awarding school as used by district SIS.

Required

String (20)

SchoolCDSCode

CDE full CDS code for credit awarding school (county code, district code, and schoolID)

Required

String (20)

Lastname

Student’s last name

Required

String (50)

FirstName

Student’s first name

Required

String (50)

SISLocalID

Student’s unique ID within your district’s

• SIS. Social Security Numbers are not

permitted.

Required

String (10)

UCCSUApprovedCourse

Code indicating that the course section

Required

String(10)

SLIDE 18

P a g e | 18

meets UC and CSU requirements for

• admission. Same as CSIS 08.05.18.

i.e. A‐G, GA‐GO

Verify Data Meets: No. Transcripts File Rule

1.

All entries have a School Year and the year represents the year for spring of that year

2.

Grade column reflects grade level when grade was earned. Grades earned in previous school district display the CDS code or name of the previous school.

3. Each record includes data in all fields except the UCCSUApprovedCourse. 4. All students in grades 9 – 12 included in the demographics extract have a record in this file. 5. The SISSchoolCode and the SchoolCDSCODE reflect the school that awarded the credits, not the school where the student currently attends.

SLIDE 19

P a g e | 19

Revision History

Changed from 1.2

• 1. References to CALPADS field numbers updated to reflect field names and numbering contained

in CALPADS Data Guide dated 8/29/2012.

• 2. Added new field to enrollment extract CompletionStatus, which maps to CALPADS field 1.28.

Changed from 1.51

• 1. Definition of primary language column in Demographics extract corrected.

Changed from 1.6

Added tables in each section under the heading “Verify Data Meets:” that lists rules that the file must meet to be acceptable.

Changed from 1.6.1

Added section on Which Students to Include in the Extract