By Chris Moggia on Saturday, 08 October 2016
Category: What's New

Cooking with CALPADS: Fun with Extracts

There are 9 types of CALPADS Extract files:

1.  ODS Extracts

2.  SSID Extract

3.  Candidate List Extract

4.  Replacement SSID Extract

5.   Rejected Records Extract

6.  Direct Certification Extract

7.  Assessment - Student Results Extract New!

8.  Assessment - Individual Student Results Extract New!

9.  Suspense Resolution Records

Type #1 - "Operational Data Store or 'O.D.S.' extract" 

Generating an ODS extract is useful if you want to compare the data in CALPADS to what is in your own SIS system.  Since an ODS extract pulls out the "live" data, you can match what is in CALPADS to your own data to make sure you have enrolled or exited all of the students correctly, to identify students who might be participating in a program, determine the EL status, etc.

There are 13 different types of ODS extract files.  They are virtually identical to the files used to upload data to the calpads system from your student information system, with three differences: the ODS Extract file is structured like the input file with the addition of two fields, (A) the upload date and (B) the last date updated, at the end of each record. Additionally, the (C) Record Type Code will have a ‘-R’ appended to the Input Record Type Code.

ODS Extract Record Type Codes

#

Record Type

Input Record Type Code

ODS Extract Record Type Code

1

SSID Enrollment

SENR

SENR-R

Student Information

SINF

SINF-R

3

Student English Language Acquisition

SELA

SELA-R

4

Student Program

SPRG

SPRG-R

5

Student Discipline

SDIS

SDIS-R

6

Student Waivers

SWAV

SWAV-R

7

Staff Demographics

SDEM

SDEM-R

8

Staff Assignment

SASS

SASS-R

9

Course Section Enrollment

CRSE

CRSE-R

10

Course Section Completion

CRSC

CRSC-R

11

Student Course Section Enrollment

SCSE

SCSE-R

12

Student Course Section Completion

SCSC

SCSC-R

13

Student Career Technical Education

SCTE

SCTE-R


 

Type #2 - "SSID extract"

An LEA may request an extract of their "State Student Identifier Number" or "SSID" information from the CALPADS portal. CALPADS will create the extract file according to the user’s specified selection criteria. The SSIDs in the file may be imported into a local student information system and/or used locally on the user’s personal computing system.

The file will contain a list of SSIDs and limited demographics based on the selected filter criteria defined by the LEA. CALPADS will extract ONLY THE SSIDs associated with the students currently enrolled in the LEA. The content of the SSID Extract file is listed in the table below.

SSID Extract Record Layout

#

CALPADS File Element

Field Type

Max Length

Business Rule

Reporting LEA

CS

7

This will default to the institution identifier of the User ID. May not be blank.

School of Attendance

CS

7

The 7 digit code associated with the student’s school of attendance. May not be blank.

SSID

CS

10

The SSID for the student. If the SSID associated with the enrollment is retired, the system will populate the SSID field with the active SSID associated with the retired SSID. May not be blank.

Local Student ID

CS

16

The student’s Local Identifier associated with the enrollment. May not be blank.

Student Legal Last Name

CS

50

The Legal Last Name of the student. May not be blank.

Student Legal First Name

CS

30

The Legal First Name of the student. May not be blank.

Student Legal Middle Name

CS

30

The Legal Middle Name of the student. May be blank.

Gender Code

CS

1

The Gender Code of the student. May not be blank.

Student Birth Date

DT

8

The Birth Date of the student. May not be blank.

10 

Enrollment Start Date

DT

8

The Enrollment Start Date of the student. May not be blank.

11 

Grade Level Code

CS

2

The Grade Level of the student. May not be blank.

12 

English Language Acquisition  Status Code

CS

4

The most recent English Language Acquisition Status Code from SELA table in ODS.

13 

English Language Acquisition Status Start Date

DT

8

The most recent English Language Acquisition Status Start Date from SELA table in ODS.

14 

Primary Language

CS

2

The most recent value from SELA table in ODS.

15 

Date SSID Created

DT

8

The date the SSID enrollment was created in CALPADS. May not be blank.


Type #3 - "Candidate List extract (SECL)"

 An LEA may request a Candidate List Extract file for a specified SSID batch submission. The extract file will contain data an LEA may use with their local computing environment to analyze and select SSID candidates provided by CALPADS. The LEA can then upload a response file (see SSID Enrollment Candidate List Response section below) of resolved candidates to CALPADS for processing.

Candidate List Extract Record Layout

#

CALPADS File Element

Field Type

Max Length

Business Rule

Record Type Code

CS

4

A category describing the type of data record being submitted SECL.

Request ID

CS

36

CALPADS assigned unique ID associated with the SSID request candidate list of selection records generated based on the SSID request submission. The Request ID links the SSID request candidate list records to the original SSID Request record.

May not be blank.

Job ID

CS

36

The Job ID associated with the Candidate List. May not be blank.

Request Record Type

CS

20

The type of the record in the Candidate List. The values may include Request, Candidate, New, and None. May not be blank.

Select Flag

CS

1

The flag will be set to “Y” to indicate the candidate record selected by the LEA. This field will be blank in the extract file. Only one candidate record per Request ID may be selected. Valid values are “Y” or blank

Candidate Score

CS

3

The matching score of the candidate. Only populated when record type equals Candidate.

Student Local ID

CS

16

The Local Identifier of the candidate record or request record.

SSID

CS

10

The SSID for the student. This will only be populated when field Request Record Type equals “Candidate”.

Student Legal Last Name

CS

50

The Legal Last Name of the request record or candidate. May not be blank.

10 

Student Legal First Name

CS

30

The Legal First Name of the request record or candidate. May not be blank.

11 

Student Legal Middle Name

CS

30

The Legal Middle Name of the request record or candidate. May be blank.

12 

Student Legal Name Suffix

CS

10

The Legal Name Suffix of the request record or candidate. May be blank.

13 

Student Alias First Name

CS

30

The Alias Last Name of the request record or candidate. May be blank.

14 

Student Alias Middle Name

CS

30

The Alias First Name of the request record or candidate. May be blank.

15 

Student Alias Last Name

CS

50

The Alias Middle Name of the request record or candidate. May be blank.

16 

Student Birth Date

DT

8

The Birth Date of the request record or candidate. May not be blank.

17 

Student Gender Code

CS

1

The Gender Code of the request record or candidate SSID. May not be blank.

18 

Student Birth Country Code

CS

2

The Birth Country Code of the request record or candidate. May not be blank.

19 

Student Birth State Province Code

CS

6

The Birth State Province Code of the request record or candidate. May be blank.

20 

Primary Language Code

CS

2

The Primary Language Code of the request record or candidate. May not be blank.

21 

Reporting LEA

CS

7

The 7 digit Reporting LEA code of the request record or candidate. May not be blank.

22 

School of Attendance

CS

7

The 7 digit School of Attendance code of the request record or candidate. May not be blank.

23 

Enrollment Start Date

DT

8

The Enrollment Start Date of the request record or candidate. May not be blank.

24 

Enrollment Exit Date

DT

8

The Enrollment Exit Date of the request record or candidate. May be blank.

25 

Grade Level Code

CS

2

The Grade Level Code of the request record or candidate. May not be blank.

SSID Enrollment Candidate List Response

The following sections describe the process and file layout associated with the offline resolution of the Candidate List. This process will allow an LEA to resolve the Candidate List outside of CALPADS through use of a local automated or manual process. The resolved Candidate List, the Candidate List Response file, may then be uploaded to CALPADS for processing of the selected candidate records. This upload process is available only to authorized LEA users granted the appropriate user role by the local LEA Administrator.

After the Candidate List is resolved offline, the LEA creates the Candidate List Response file and uploads the file to CALPADS for processing. CALPADS will process the file and produce one of the following results:

• If errors exist, an error message is generated and the LEA is informed that changes are required 

• If no errors exist, CALPADS will update the disposition of each SSID request in the ODS

Candidate List Response File Content

The Candidate List Response file will contain the resolution of the candidates included in the original Candidate List extract file. The Candidate List Response file will be produced by the LEA. The Candidate List Response file must adhere to the following rules:

• The Candidate List Response file must contain all records included in the original extract file

• The records in the file do not have to be in any particular sequence

• One and only one Select Flag field may contain “Y” for each SSID request candidate list group (where the Request ID fields are the same value).

The content of the Candidate List Response file is listed in Table 42 below.

Candidate List Response File Content

#

CALPADS File Element

Field Type

Max Length

Business Rule

Record Type Code

CS

4

A category describing the type of data record being submitted - SECL

Request ID

CS

36

CALPADS assigned unique ID associated with the SSID request candidate list of selection records generated based on the SSID request submission. The Request ID links the SSID request candidate list records to the original SSID Request record.

May not be blank.

Job ID

CS

36

The Job ID associated with the Candidate List. May not be blank.

Request Record Type

CS

20

The type of the record in the Candidate List. The values may include Request, Candidate, New, and None. May not be blank.

Select Flag

CS

1

The flag will be set to “Y” to indicate the candidate record selected by the LEA. This field will be blank in the extract file. Only one candidate record per Request ID may be selected. Valid values are “Y” or blank

Candidate Score

CS

3

The matching score of the candidate. Only populated when Request Record Type equals “Candidate”.

Student Local ID

CS

16

The Local Identifier of the candidate record or request record.

SSID

CS

10

The SSID for the student. This will only be populated when Request Record Type equals “Candidate”

Student Legal Last Name

CS

50

The Legal Last Name of the request record or candidate. May not be blank.

10 

Student Legal First Name

CS

30

The Legal First Name of the request record or candidate. May not be blank.

11 

Student Legal Middle Name

CS

30

The Legal Middle Name of the request record or candidate. May be blank.

12 

Student Legal Name Suffix

CS

10

The Legal Name Suffix of the request record or candidate. May be blank.

13 

Student Alias First Name

CS

30

The Alias Last Name of the request record or candidate. May be blank.

14 

Student Alias Middle Name

CS

30

The Alias First Name of the request record or candidate. May be blank.

15 

Student Alias Last Name

CS

50

The Alias Middle Name of the request record or candidate. May be blank.

16 

Student Birth Date

DT

8

The Birth Date of the request record or candidate. May not be blank.

17 

Student Gender Code

CS

1

The Gender Code of the request record or candidate SSID. May not be blank.

18 

Student Birth Country Code

CS

2

The Birth Country Code of the request record or candidate. May not be blank.

19 

Student Birth State Province Code

CS

6

The Birth State Province Code of the request record or candidate. May be blank.

20 

Primary Language Code

CS

2

The Primary Language Code of the request record or candidate. May not be blank.

21 

Reporting LEA

CS

7

The 7 digit Reporting LEA code of the request record or candidate. May not be blank.

22 

School of Attendance

CS

7

The 7 digit School of Attendance code of the request record or candidate. May not be blank.

23 

Enrollment Start Date

DT

8

The Enrollment Start Date of the request record or candidate. May not be blank.

24 

Enrollment Exit Date

DT

8

The Enrollment Exit Date of the request record or candidate. May be blank.

25 

Grade Level Code

CS

2

The Grade Level Code of the request record or candidate. May not be blank.


Type #4 - "Replacement SSID extract"

 Honestly, I don't know why you would need this extract?


 

Type #5 - "Rejected records extract"

An LEA may request an extract of the rejected records for a specific batch submission. The data in the file may be used locally on the user’s personal computing system for analysis and resolution of errors.

The following record types will follow the Rejected Records Extract format described in this section:

• SENR (SSID Enrollment)

• SINF (Student Information)

• SPRG (Student Program)

• SDIS (Student Discipline)

• SWAV (Student Waivers)

• SDEM (Staff Demographics)

• SASS (Staff Assignment)

• CRSE (Course Section Enrollment)

• CRSC (Course Section Completion)

• SCSE (Student Course Section Enrollment)

• SCSC (Student Course Section Completion)

• SCTE (Student Career Technical Education)

• SELA (Student English Language Acquisition)

Record Layout

The Reject Record Extract file layout for each file type above will follow the same structure as their corresponding input file format as documented in the CALPADS File Specification (CFS) document with the addition of the following six fields identified in Table 61:

Rejected Records Extract Record Layout

#

CALPADS File Element

Field Type

Max Length

Business Rule

Error Code

CS

2

The error code associated with the record. For example 1 for Invalid Submitter error.

Error Severity

CS

1

The error severity associated with the error (e., warning or fatal).

Error Description

CS

50

The description associated with the error code assigned to the record.

Field Name

CS

50

The field name that failed validation checks and resulted in the error code.

Field Value

CS

50

The field value that failed validation checks and resulted in the error code.

The following two fields exist in each record type and will contain the following:

•   Record Type Code will have a "-E" appended to the input Record Type Code value.

•   Transaction Type Code will be blank.

• File Format

The extract file created will be in caret delimited format. The structure of the file will conform to the order and data type listed in the previous section. The date formats will follow the same formats required for the CALPADS File Formats (Format: CCYYMMDD, e.g. 20081025). For example:

Record Type

Caret Delimited Fields

SDEM

Record Type Code = “SDEM-E”

Transaction Type Code = Blank

All other fields for record type as defined in the CFS

Error Code

Error Severity

Error Description

Field Value

CRSE

Record Type Code = “CRSE-E”

Transaction Type Code = Blank

All other fields for record type as defined in the CFS

Error Code

Error Severity

Error Description

Field Value


Type #6 - "Direct Certification extract"

An authorized LEA may request an extract of their direct certification results through the CALPADS on-line portal for all their primary enrolled students. CALPADS will create the extract file according to the user’s specified selection criteria.

The fields displayed in Table 71 below can be selected to filter the content of the data included in the Direct Certification Extract file.

Table 71: Direct Certification Extract Selection Criteria

Field

Default

Clarification

Reporting LEA

Default LEA

LEA defaults based on the Job ID selected. (Required)

School of Attendance

All

The LEA may select all schools or only one school to be included in the file.

Enrollment Status Code

10 “Primary enrollment”

Selection includes ONLY primary enrollments

Certification Status

All (checkbox for S, O, and N are all checked)

The LEA may select all certification statuses or only select certification statuses to be included in the file.

Record Layout

The file will contain the direct certification results for:

•   all students “currently” enrolled within the requesting LEA that have a “primary” enrollment status regardless of when the enrollment begins (includes pre-enrolled students)

or

•   all primary (enrollment status = 10) enrollments where exit code = E155 (YearEndEnrlmntExitSameSchl) AND the exit date for that enrollment is less than 1 year. The content of the Direct Certification file is listed in the table below.

Direct Certification Extract Record Layout

#

CALPADS File Element

Field Type

Max Length

Business Rule

1

Academic Year ID

CS

9

Format: CCYY-CCYY (ex. 2014-2015) Indicates the school year associated with the file. May not be blank.

2

Reporting LEA

CS

7

This will default to the institution identifier of the User ID. May not be blank.

3

School of Attendance

CS

7

The 7 digit code associated with the student’s school of attendance. May not be blank.

4

Local Student ID

CS

16

The student’s Local Identifier associated with the enrollment. May not be blank.

5

SSID

CS

10

The SSID for the student. May not be blank.

6

Student Legal First Name

CS

30

The Legal First Name of the student. May not be blank.

7

Student Legal Middle Name

CS

30

The Legal Middle Name of the student. Optional and may be blank.

8

Student Legal Last Name

CS

50

The Legal Last Name of the student. May not be blank.

9

Certification Date

DT

8

The date the student was certified eligible for NSLP. May not be blank.

10

Certification Status

CS

1

“S” in field 10 means that the student is eligible for free lunches through the SNAP program.

 “O” in field 10 means the student is eligible for free lunches through either TANF or MediCAL. This value will replace M for O for LEAS participating in the MediCAL Pilot Program. All other LEAs will have T values displayed as O.

“N” in field 10 means that the student is Not Eligible for free lunches.


Type #7 - "Assessment - Individual Student Profile Extract"

An LEA may request an extract of the historical assessment data contained in the AADS for each student enrolled in the LEA as of the date selected. The extract file will contain only matched student assessment records and can serve as an expanded alternative to exporting Report 40.1 Individual Student Profile Assessments, which provides currently enrolled student profiles.

The selection criteria identified will be used to generate the AADS Download extract files; however, not all of these criteria will be included in the extract itself.

Individual Student Assessment Profile Extracts Selection Criteria

Item

Record Type

Selection Criteria

1

Individual Student Assessment Profile

Selection Criteria Options:

• As of Date enrolled students

• Assessment Year (multi select)

• Reporting LEA

• School

• Program

• Test Type/Purpose (Assessment)

• Test Content Area

• Test Name

• Grade

• Gender

• Ethnicity/Race

• English Language Acquisition Status

• Primary Language

• Title III Eligible Immigrant

• Title I Part C Migrant

• Socio-economically Disadvantaged

• Special Education

• Gifted and Talented

• Student SSID

• Local Student ID


Type #8 - "Assessment - Student Results Extract"

 An LEA may request an extract of the details of each assessment contained in the AADS for each student enrolled in the LEA as of a select Academic Year.

Selection Criteria

The selection criteria identified in the table below will be used to generate the AADS Download extract files:

Table 101: Student Results Extracts Selection Criteria

Item

Record Type

Selection Criteria

1

Assessment Student Details

Selection Criteria Options:

• Program

• Administration

• Academic Year

• Reporting LEA

• School

• Performance Level

• Result

• Student SSID

• Local Student ID

Record Layout

The file will contain a list of SSIDs, limited demographics, and associated assessment data based on the selected filter criteria defined by the LEA. CALPADS will extract the assessment data associated with the students tested at the LEA as of the select Academic Year, regardless of whether the Assessment Record was matched to an ODS record. This extract is intended to inform the LEA of the content imported for the requested testing administration. The content of the Student Results Extract file is listed in the table below.

File Transmission

The generated file will be available for download through CALPADS via the View Student Results > screen button. The file will not be transmitted via email, but may be transmitted through the approved SFTP process for larger LEAs.


Type #9 - "Assessment - Student Results Extract"

An LEA may request a Suspense Resolution Records extract file for a specified assessment Program and Administration. The extract file will contain student assessment results records that were not matched when vendor assessment data was loaded. An LEA may use their local computing environment to analyze and select an SSID enrollment to resolve the suspense. The LEA can then upload a file (see Suspense Resolution Records Response section below) of resolved student assessment suspense records to CALPADS for processing.

The Suspense Resolution Records extract file contains assessment results records that are in suspense. The content of the Suspense Resolution Records extract file is listed in Table 10-1 below.

Suspense Resolution Extract Record Layout

#

CALPADS File Element

Field Type

Max Length

Business Rule

Record Type Code

CS

4

A category describing the type of data record produced SUSP

Program

CS

1

The Program to which the suspense record belongs.

May not be blank.

Administration

CS

10

The Program Administration to which the suspense record belongs.

May not be blank.

Administration Date

DT

8

The date used by CALPADS to validate a student’s enrollment during the Program Administration. (See Glossary: Verification of Enrollment) Format: CCYYMMDD

May be blank.

Student Local ID

CS

16

The student’s Local Identifier in the suspense record.

May be blank.

SSID

CS

10

The student’s SSID in the suspense record.

May be blank.

Student Legal Last Name

CS

50

The Last Name in the suspense record for the student.

May be blank.

Student Legal First Name

CS

30

The First Name in the suspense record for the student.

May be blank.

Student Legal Middle Name

CS

30

The Middle Name in the suspense record for the student.

May be blank.

10 

Student Birth Date

DT

8

The Date of Birth Year, Month, and Day in the suspense record for the student. Format: CCYYMMDD

May be blank.

11 

Student Gender Code

CS

1

The Gender in the suspense record for the student.

May be blank.

12 

Reporting LEA

CS

7

The County/District code in the suspense record for the student’s enrollment.

May be blank.

13 

School of Attendance

CS

7

The School code in the suspense record for the student’s enrollment.

May be blank.

14 

Enrollment Start Date

DT

8

Will be blank.

15 

Enrollment Exit Date

DT

8

Will be blank.

16 

Grade Level Code

CS

2

The Grade in the suspense record for the student.

May be blank.

17 

Suspense Resolution

CS

3

Will be blank.

18 

Unique Identification Number

CS

20

The UIN of the assessment record from the vendor. Will not be blank.

19 

Suspense Reason

CS

20

Will not be blank.

Suspense Resolution Records Response File Content

• The Suspense Resolution Records response file will contain the resolution of the suspense records included in the original Suspense Resolution Records extract file. The Suspense Resolution Records response file will be produced by the LEA.

The content of the Suspense Resolution Records response file is listed in the table below.

Suspense Resolution Records Response File Content

#

CALPADS File Element

Field Type

Max Length

Business Rule

Record Type Code

CS

4

A category describing the type of data record being submitted SUSP

May not be blank. Must be same as in Extract.

Suspense Record ID

CS

36

CALPADS assigned unique ID associated with the assessment suspense record. The Suspense Record ID links the suspense record to the correction record. It must be the same ID received in the suspense record.

May not be blank. Must be same as in Extract.

Program

CS

1

The Program to which the correction record belongs.

May not be blank. Must be same as in Extract.

Administration Code

CS

10

The Administration Code of the Program to which the correction record belongs.

May not be blank. Must be same as in Extract.

Administration Date

DT

8

The date used by CALPADS to validate a student’s enrollment during the Program Administration. (See Glossary: Verification of Enrollment)

May be blank. Must be same as in Extract.

Student Local ID

CS

16

The Local Identifier of the correction SSID.

May not be blank.

SSID

CS

10

The SSID for the correction record.

May not be blank.

Student Legal Last Name

CS

50

The Legal Last Name of the correction SSID.

May not be blank.

Student Legal First Name

CS

30

The Legal First Name of the correction SSID.

May not be blank.

10 

Student Legal Middle Name

CS

30

The Legal Middle Name of the correction SSID.

May be blank.

11 

Student Birth Date

DT

8

The Birth Date of the correction SSID.

May not be blank.

12 

Student Gender Code

CS

1

The Gender Code of the correction SSID.

May not be blank.

13 

Reporting LEA

CS

7

The 7 digit Reporting LEA code of the correction SSID enrollment.

May not be blank.

14 

School of Attendance

CS

7

The 7 digit School of Attendance code of the correction SSID enrollment.

May not be blank.

15 

Enrollment Start Date

DT

8

The Enrollment Start Date of the correction SSID enrollment.  Format: CCYYMMDD

May not be blank.

16 

Enrollment Exit Date

DT

8

The Enrollment Exit Date of the correction SSID enrollment. Format: CCYYMMDD

May be blank.

17 

Grade Level Code

CS

2

The Grade Level Code of the correction SSID enrollment.

May not be blank.

18 

Resolution Code

CS

2

Valid codes:

Match = 10

Not Mine = 20

May not be blank.

19 

Unique Identification Number

CS

20

The UIN of the assessment record from the vendor. May not be blank.

20 

Suspense Reason

CS

20

May not be blank.

Related Posts

Leave Comments