Database Properties - Test Alternate Key (Index)
Copyright © 2018 EarthSoft, Inc • Modified: 23 Nov 2018
The Test Alternate Key (also referred to as test-level key or index), consists of the group of fields used to determine record uniqueness in the DT_TEST table. Prior to creating a new database, determine which fields are necessary to uniquely identify each test/analysis. Chose the minimum number of fields needed (typical recommendation). As part of the Test Alternate Key, the fields chosen will be required in all data sets.
The TestAlternateKey must include the following fields:
•FACILITY_ID: Identifier of the facility
•SAMPLE_ID: Identifier for the sample, unique within the facility
•ANALYTIC_METHOD: Name of the analytic method
The TestAlternateKey can optionally include the following fields:
•ANALYSIS_DATE: Date/time the sample was analyzed
•FRACTION: Such as Total, Dissolved, etc.
•COLUMN_NUMBER: Such as 1C, 2C, NA, etc.
•TEST_TYPE: Such as Initial, Reanalysis, Dilution, etc.
To view the EQuIS Database Properties, navigate to the EQuIS Professional Connect tab of the backstage menu.
1.Click on a database to select it.
2.Right-click and select Properties from the context menu.
The Database Properties window opens and displays the Test-level Key Fields selections for the database.
It is best to configure the permanent test level keys when creating a new EQuIS Database. While it is possible to change the test-level key after a database is created, it is not advisable. If data already exists in the database, it is only possible to add or remove fields if the change does not cause a conflict with the existing data. Otherwise, removing a field from the test-level key could create many duplicate entries that would require resolution.
<errorLog Status="ERROR" Table="dt_result" Row="1/-1/TSS/..."Message=Column facility_id, test_id, cas_rn' is constrained to be unique. Value 'a, -1, TSS' is already present." />
Based on the database's Test Alternate Key settings, an error of this type may result in the Create step, if the EDD contains several records that are not unique. For this example, the reason this record is not passing the Create step is because the TEST_ID is the same as the CAS_RN of another record in the EDD. Adding another TAK could allow this record to be unique. Note that once a Test Alternate Key has been added to the database and new data has been loaded, it may no longer be possible to remove a Test Alternate Key. Backup the database prior to adding a Test Alternate Key, in case you want to revert back without the additional Test Alternate Key.