Mapping CALPADS Assessment Extract File to eSchoolPLUS Tables

1

Record Type

 Character/4

Not processed

N/A

2

Unique Identification Number (UIN)

Character/20

Not processed

N/A

3

SSID

Character/10

Not processed

N/A

4

Local Student ID

Character/16

ltdb_stu_test.student_id
ltdb_stu_subtest.student_id
ltdb_stu_trk_data.student_id

Test Scores > All > Student > Test History by Student > click Description link for appropriate test: ID field

5

Student Legal Last Name

Character/50

Not processed

N/A

6

Student Legal First Name

Character/30

Not processed

N/A

7

Student Legal Middle Name

Character/30

Not processed

N/A

8

Gender Code

Character/1

Not processed

N/A

9

Student Birth Date

Date/8

Not processed

N/A

10

Grade Level Code

Character/2

Not processed

N/A

11

Assessment Program Code

Character/1

The import uses this value to determine the applicable test for each record in the CALPADS extract file:

1 – STAR
2 – CAHSEE
3 – CELDT

The import will add or update test information based on the Test Code, Test Level, and Test Form you indicate on the CALPADS Assessments Import page for each of these Assessment Program Codes.

In the extract file, the import first checks the Assessment Program Code to determine which prompt to reference on the CALPADS Assessments Import page for the test record to update.

Regulatory > CALPADS > Utilities > CALPADS Assessments Import: Test – Level – Form field for each of the three Assessment Program Codes

12

Test Type/Purpose

Character/2

ltdb_stu_trk_data.field_value where field_value = 1

This field may be blank.

If the extract record has an Assessment Program Code (Field 11) value of 1 (STAR), then the Test Type/Purpose also gets concatenated with Test Name Code (Field 14) to compare against Subtest score State Code Equiv values so the import can determine the correct Score field to update.

Test Scores > All > Student > Test History by Student > click Description link for appropriate test > Student Tracking panel: Test Type/Purpose field

For subtest score State Code Equivs: Test Score Center > Setup > Test Definition > Subtests and Scores panel > Scores link for appropriate Subtest > click appropriate Code link

13

Test Content Area Code

Character/2

Based on this code, get the matching catb_test_content.description. This goes into ltdb_stu_trk_data.field_value where field_number = 2

Test Scores > All > Student > Test History by Student > click Description link for appropriate test > Student Tracking panel: Content Area field

Administration > General Setup > District > Validation Tables > select California State Reporting > Assessment Content Area to see the setup of the Code and Description

14

Test Name Code

Character/2

This field is used to compare against Subtest score State Code Equiv values so the import can determine the correct subtest and score field to update.

If the extract record has an Assessment Program Code (Field 11) value of 1 (STAR), then the Test Type/Purpose (Field 12) gets concatenated with Test Name Code to determine State Code Equiv.

For subtest score State Code Equivs: Administration > Test Scores Setup > Setup > Test Definition > Subtests and Scores panel > Scores link for appropriate Subtest > click appropriate Code link

15

Assessment Year

Character/8

ltdb_stu_trk_data.field_value where field_number = 3

Test Scores > All > Student > Test History by Student> click Description link for appropriate test > Student Tracking panel: Assessment Year field

16

Administration

Character/10

ltdb_stu_trk_data.field_value where field_number = 4

Test Scores > All > Student > Test History by Student > click Description link for appropriate test > Student Tracking panel: Administration field

17

Administration Date

Date/8

ltdb_stu_test.test_date
ltdb_stu_subtest.test_date
ltdb_stu_trk_data.test_date

Test Scores > All > Student > Test History by Student > click Description link for appropriate test: Test Date field

18

Grade Code

Character/2

ltdb_stu_test.grade

Test Scores > All > Student > Test History by Student > click Description link for appropriate test: Grade on Test Date field

19

Scale Score

Character/3

If the extract record has an Assessment Program Code (Field 11) of 1 (STAR) and there is a Scale Score value, use the following concatenation to find the State Code Equiv for the appropriate subtest score field to update: Test Type/Purpose (Field 12) + Test Name Code (Field 14) + SS

For all other tests with a Scale Score value, use the following concatenation to find the appropriate subtest score State Code Equiv: Test Name Code (Field 14) + SS

Based on this State Code Equiv, the import updates the following fields:

ltdb_stu_subtest.subtest
ltdb_stu_subtest.score_code
ltdb_stu_subtest.score

To see the imported student subtest score:

Test Scores > All > Student > Test History by Student > click Description link for appropriate test > Subtests and Scores panel: find the appropriate Subtest and Score field

To see the subtest score State Code Equiv:

Administration > Test Scores Setup > Setup > Test Definition > Subtests and Scores panel > Scores link for appropriate Subtest > click appropriate Code link

20

Assessment Performance Level Code

Character/2

If the extract record has an Assessment Program Code (Field 11) of 1 (STAR) and there is an Assessment Performance Level Code value, use the following concatenation to find the State Code Equiv for the appropriate subtest score field to update: Test Type/Purpose (Field 12) + Test Name Code (Field 14) + P

For all other tests with an Assessment Performance Level Code value, use the following concatenation to find the appropriate subtest score State Code Equiv: Test Name Code (Field 14) + P

Based on this State Code Equiv, the import updates the following fields:

ltdb_stu_subtest.subtest
ltdb_stu_subtest.score_code
ltdb_stu_subtest.score

To see the imported student subtest score:

Test Scores > All > Student > Test History by Student > click Description link for appropriate test > Subtests and Scores panel: find the appropriate Subtest and Score field

To see the subtest score State Code Equiv:

Administration > Test Scores Setup > Setup > Test Definition > Subtests and Scores panel > Scores link for appropriate Subtest > click appropriate Code link

21

Assessment Result Code

Character/2

For the STAR test with Assessment Program Code (Field 11) value of 1, this field will be blank.

For all other tests with an Assessment Result Code value, use the following concatenation to find the appropriate subtest score State Code Equiv: Test Name Code (Field 14) + R

Based on this State Code Equiv, the import updates the following fields:

ltdb_stu_subtest.subtest
ltdb_stu_subtest.score_code
ltdb_stu_subtest.score

To see the imported student subtest score:

Test Scores > All > Student > Test History by Student > click Description link for appropriate test > Subtests and Scores panel: find the appropriate Subtest and Score field

To see the subtest score State Code Equiv:

Administration > Test Scores Setup > Setup > Test Definition > Subtests and Scores panel > Scores link for appropriate Subtest > click appropriate Code link

22

Testing Conditions

Character/20

If the extract record has an Assessment Program Code (Field 11) of 1 (STAR) and there is a Testing Conditions value, use the following concatenation to find the State Code Equiv for the appropriate subtest score field to update: Test Type/Purpose (Field 12) + Test Name Code (Field 14) + C

For all other tests with a Testing Conditions value, use the following concatenation to find the appropriate subtest score State Code Equiv: Test Name Code (Field 14) + C

Based on this State Code Equiv, the import updates the following fields:

ltdb_stu_subtest.subtest
ltdb_stu_subtest.score_code
ltdb_stu_subtest.score

To see the imported student subtest score:

Test Scores > All > Student > Test History by Student > click Description link for appropriate test > Subtests and Scores panel: find the appropriate Subtest and Score field

To see the subtest score State Code Equiv:

Administration > Test Scores Setup > Setup > Test Definition > Subtests and Scores panel > Scores link for appropriate Subtest > click appropriate Code link

23

Reporting LEA Code

Character/7

ltdb_stu_trk_data.field_value where field_number = 5

Test Scores > All > Student > Test History by Student > click Description link for appropriate test > Student Tracking panel: Reporting LEA field

24

Reporting LEA Name

Character/50

ltdb_stu_trk_data.field_value where field_number = 6

Test Scores > All > Student > Test History by Student > click Description link for appropriate test > Student Tracking panel: LEA Name field

25

School Code

Character/7

Take concatenation of Reporting LEA Code (Field 23) and School Code to find a matching reg_building.state_code_equiv.

If there is a match, populate ltdb_stu_test.building with the appropriate Building number. If there is no match, leave ltdb_stu_test.building blank.

School Code also populates ltdb_stu_trk_data.field_value where field_number = 7

Test Scores > All > Student > Test History by Student > click Description link for appropriate test > Building on Test Date field

Test Scores > All > Student > Test History by Student > click Description link for appropriate test > Student Tracking panel: School field

26

School Name

Character/50

ltdb_stu_trk_data.field_value where field_number = 8

Test Scores > All > Student > Test History by Student > click Description link for appropriate test > Student Tracking panel: School Name field

27

Local Record ID

Character/16

ltdb_stu_trk_data.field_value where field_number = 9

Test Scores > All > Student > Test History by Student > click Description link for appropriate test > Student Tracking panel: Local Report ID field

28

Enrollment Start Date

Date/7

ltdb_stu_trk_data.field_value where field_number = 10

Test Scores > All > Student > Test History by Student > click Description link for appropriate test > Student Tracking panel: Enrollment Start Date field

29

Enrollment Exit Date

Date/7

ltdb_stu_trk_data.field_value where field_number = 11

Test Scores > All > Student > Test History by Student > click Description link for appropriate test > Student Tracking panel: Enrollment Exit Date field