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
2	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

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

School of Attendance

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

SSID

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

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

Student Legal Last Name

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

Student Legal First Name

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

Student Legal Middle Name

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

Gender Code

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

Student Birth Date

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

Enrollment Start Date

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

Grade Level Code

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

English Language Acquisition Status Code

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

English Language Acquisition Status Start Date

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

Primary Language

The most recent value from SELA table in ODS.

Date SSID Created

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
1	Record Type Code	CS	4	A category describing the type of data record being submitted SECL.
2	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.
3	Job ID	CS	36	The Job ID associated with the Candidate List. May not be blank.
4	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.
5	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
6	Candidate Score	CS	3	The matching score of the candidate. Only populated when record type equals Candidate.
7	Student Local ID	CS	16	The Local Identifier of the candidate record or request record.
8	SSID	CS	10	The SSID for the student. This will only be populated when field Request Record Type equals “Candidate”.
9	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
1	Record Type Code	CS	4	A category describing the type of data record being submitted - SECL
2	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.
3	Job ID	CS	36	The Job ID associated with the Candidate List. May not be blank.
4	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.
5	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
6	Candidate Score	CS	3	The matching score of the candidate. Only populated when Request Record Type equals “Candidate”.
7	Student Local ID	CS	16	The Local Identifier of the candidate record or request record.
8	SSID	CS	10	The SSID for the student. This will only be populated when Request Record Type equals “Candidate”
9	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
1	Error Code	CS	2	The error code associated with the record. For example 1 for Invalid Submitter error.
2	Error Severity	CS	1	The error severity associated with the error (e., warning or fatal).
3	Error Description	CS	50	The description associated with the error code assigned to the record.
4	Field Name	CS	50	The field name that failed validation checks and resulted in the error code.
5	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)

• 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
1	Record Type Code	CS	4	A category describing the type of data record produced SUSP
2	Program	CS	1	The Program to which the suspense record belongs. May not be blank.
3	Administration	CS	10	The Program Administration to which the suspense record belongs. May not be blank.
4	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.
5	Student Local ID	CS	16	The student’s Local Identifier in the suspense record. May be blank.
6	SSID	CS	10	The student’s SSID in the suspense record. May be blank.
7	Student Legal Last Name	CS	50	The Last Name in the suspense record for the student. May be blank.
8	Student Legal First Name	CS	30	The First Name in the suspense record for the student. May be blank.
9	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
1	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.
2	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.
3	Program	CS	1	The Program to which the correction record belongs. May not be blank. Must be same as in Extract.
4	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.
5	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.
6	Student Local ID	CS	16	The Local Identifier of the correction SSID. May not be blank.
7	SSID	CS	10	The SSID for the correction record. May not be blank.
8	Student Legal Last Name	CS	50	The Legal Last Name of the correction SSID. May not be blank.
9	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.

Cooking with CALPADS: Fun with Extracts

There are 9 types of CALPADS Extract files:

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

Type #2 - "SSID extract"

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

Type #4 - "Replacement SSID extract"

Type #5 - "Rejected records extract"

Type #6 - "Direct Certification extract"

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

Type #8 - "Assessment - Student Results Extract"

Type #9 - "Assessment - Student Results Extract"

Related Posts