External Matching

Externals are used to:

• Return a value in your database when the source file does not include the field value needed for uploading data. For example, the student ID is part of the key in most tables that store student data. If the student ID is not in the source file, you have to be able to return the ID based on either values found in the source file or defined as literals in the interface record.
• Check whether data exists in Registration. For example, if the data file contains student IDs, but students' IDs have been changed in Registration, then matching on student IDs allows you to check whether IDs are valid. While the Test Score Error Scan (Test Scores > All > Utilities > Test Score Error Scan) would remove records with invalid student IDs, matching during the upload prevents invalid records from being created.

When multiple detail records have the same External Column Out value, the Import Scores from File option (Test Scores > All > Utilities > Import Scores from File) processes the external values with the boolean AND operator. To upload the External Column Out field, all External Column In values must match a unique record.

Note

You do not need to use external matching to return the test key when uploading to ltdb_stu_test and ltdb_stu_subtest. The test key for a unique test, form, and level is displayed on the Test Score Import Definition page (Administration > Test Scores Setup > Setup > Test Score Import Definition). You should define a detail record that includes the test key as a literal.

This topic provides examples of how literals can be used to return the student ID for an upload to the test score table and return the section key for an upload to a mark reporting table.

External Matching for Student ID

You are uploading test score data for students. The data file you received does not include the student ID that is part of the key for the ltdb_stu_test and ltdb_stu_subtest tables. However, the file does include the first names, last names, and birthdates of all students.

Using external matching, you can return the student ID from the reg table. You would define the following detail records to return the student ID for the header that uploads to the ltdb_stu_test table.

The statement for selecting the student ID to load into ltdb_stu_test would be similar to:

Select student_id from reg WHERE lname= 'Value from Source File Field 10' AND fname = 'Value from Source File Field 11' AND birthdate = 'Value from Source File Field 17'

You also would include similar detail lines on all headers for the ltdb_stu_subtest table, but you would change the Table Affected to ltdb_stu_subtest.

External Matching for Section Key

You are uploading mark reporting information for students. The data file you received does not include the section key that is part of the key for the mr_stu_hdr and mr_stu_marks tables, but it does include the course number and course section. These fields are not enough for returning a unique section key from the schd_ms table. To return the section key, you need to know the district, school year, and building, in addition to the course and course-section. Since the district, school year, and building will be the same for all the records you are uploading, you can use literal values to define the values for these fields.

To return the section key for the header that uploads to the mr_stu_hdr table, you would define the following detail records. In this example, assume the district number is 1, the school year is 2015, and the building is 2.

The statement to select the section key to load into mr_stu_hdr would be similar to:

Select section_key from schd_ms WHERE course = 'Value from Source File Field 12' AND course_section = 'Value from Source File Field 13' AND district = '1' AND school_year = '2015' AND building = '2'

You also would include similar detail lines on all headers for the mr_stu_marks table, but you would change the Table Affected to mr_stu_marks.