Database Properties - Test Alternate Key (Index)

<< Click to Display Table of Contents >>

Navigation:  Professional > Database >

Database Properties - Test Alternate Key (Index)

Common Test Alternate Key Related Error at Create in EDP

 

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. Choose 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 Test Alternate Key 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 Test Alternate Key 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.

IDENTIFIER: 60-character field that can be set to a value so that each row in the TAK is unique (any value client determines needed)

 

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 available to select 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.

 

Note: Removing columns from the TAK from the UI in Professional is not possible, but must be done manually in the database.

 

Common Test Alternate Key Related Error at Create in EDP

 

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