Mapping CALPADS Assessment Extract File to eSchoolPLUS Tables
The following table shows how the CALPADS Assessments Import utility updates eSchoolPlus tables with data from the Individual Student Assessment Profile Extract Record file you get from CALPADS. The three eSchoolPlus tables involved are Student Test Information (ltdb_stu_test), Student Subtest Information (ltdb_stu_subtest), and Student Tracking Test Information (ltdb_stu_trk_data).
Field # | Field Name | Field Type/Length | eSchoolPlus Table.Column | Where to find in eSchoolPlus |
---|---|---|---|---|
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 | 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: | 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. |
12 | Test Type/Purpose | Character/2 | ltdb_stu_trk_data.field_value where field_value = 1 | Test Scores > All > Student > Test History by Student > click Description link for appropriate test > Student Tracking panel: Test Type/Purpose field |
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 |
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. | 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 | 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 | To see the imported student subtest score: |
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 | To see the imported student subtest score: |
21 | Assessment Result Code | Character/2 | For the STAR test with Assessment Program Code (Field 11) value of 1, this field will be blank. | To see the imported student subtest score: |
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 | To see the imported student subtest score: |
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. | Test Scores > All > Student > Test History by Student > click Description link for appropriate test > Building on Test Date 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 |