﻿ Using Lookups in DT_ACTION_LEVEL_LOOKUP

# Using Lookups in DT_ACTION_LEVEL_LOOKUP

EQuIS supports sample-specific action levels from the DT_ACTION_LEVEL_LOOKUP table.

Details about how to set up the Action Level to use the Lookup table, and how to populate the necessary rows in DT_ACTION_LEVEL_LOOKUP for using LOOKUPS, is available in the help article, Setup - DT_ACTION_LEVEL_LOOKUP. Additional information on the other LOOKUP_METHODs is found in the following Advanced Action Levels help articles.

## Creation of Sample-Specific Action Level using LOOKUP Methods

There are three LOOKUP Methods, each explained below.

1.SINGLE_LOOKUP: The Action Level Exceedance II Report applies the specified action level based on results for a single comparison analyte.
For example, action level X for Lead applies if Hardness is less than 100 mg/l for the same given sample.

2.MULTIPLE(AND)_LOOKUP: The Action Level Exceedance II Report applies specified action level based on all comparisons for multiple comparison analytes within a COMPARISON_GROUP being met. For example, action level X for Lead only applies if:

a.Hardness is less than 100 mg/l and

b.Copper is less than 0.05 ug/l for the same given sample.

3.MULTIPLE(OR)_LOOKUP: The Action Level Exceedance II Report applies a specified action level based on any of multiple comparisons within a COMPARISON_GROUP being met. For example, action level X for Lead applies if:

a.Hardness is less than 100 mg/l or

b.Copper is less than 0.05 ug/l for the same given sample.

## Example EDD with All Three Methods Populated ## SINGLE_LOOKUP

LOOKUP_SOURCE (25 char): If this action level is from an environmental agency, the agency's name or an abbreviation of the name is appropriate for the source column. Example: USEPA

LOOKUP_CODE (40 char): This column is used to create different categories for each source. Combining land use and matrix would be common entries for the code column. Example: Comm_Surf_Soil

LOOKUP_METHOD (255 char): The entries in this column must match the method name exactly. In this case that would be SINGLE_LOOKUP.

PARAM_CODE (CAS_RN in the EDD) (20 char): This entry must equal the value in the PARAM_CODE column of DT_ACTION_LEVEL_PARAMETER.
The first four columns above are the link between DT_ACTION_LEVEL_PARAMETER and DT_ACTION_LEVEL_LOOKUP.

COMPARISON_GROUP (integer): This column is not used in the SINGLE_LOOKUP

COMPARISON_ID (20 char): The CAS_RN of the analyte whose value will control the lookup process.

COMPARISON_OPERATOR (10 char): The operator to use in the comparison between the analytical result value and the COMPARISON_VALUE. Accepted values are NULL, <, <=, >, and >=.

COMPARISON_VALUE (19 char): The value to compare to the COMPARISON_ID's analytical result value.

COMPARISON_UNIT (15 char): The units of the COMPARISON_VALUE

ACTION_LEVEL (19 char): The value to use as the sample-specific action level for the PARAM_CODE analyte.

ACTION_LEVEL_UNIT (15 char): The units for the sample-specific action level.

REMARK (2000 char): A remark that will be placed in ACTION_LEVEL_NOTE (only the first 255 characters will fit in ACTION_LEVEL_NOTE).

During the execution of the Action Level Exceedance II Reports, for each PARAM_CODE sample result, the Advance Action Level processor will find the value of the COMPARISON_ID analyte from the same sample and compare it to each COMPARISON_VALUE, one at a time from the top of the list; and will use the first qualifying row's ACTION_LEVEL as the sample-specific action level for the sample result.

## MULTIPLE(AND)_LOOKUP

LOOKUP_SOURCE (25 char): If this action level is from an environmental agency, the agency's name or an abbreviation of the name is appropriate for the source column.
Example: USEPA

LOOKUP_CODE (40 char): This column is used to create different categories for each source. Combining land use and matrix would be common entries for the code column.
Example: Comm_Surf_Soil

LOOKUP_METHOD (255 char): The entries in this column must match the method name exactly. In this case, that would be MULTIPLE(AND)_LOOKUP.

PARAM_CODE (CAS_RN in the EDD) (20 char): This entry must equal the value in the PARAM_CODE column of DT_ACTION_LEVEL_PARAMETER.
The first four columns above are the link between DT_ACTION_LEVEL_PARAMETER and DT_ACTION_LEVEL_LOOKUP.

COMPARISON_GROUP (integer): This column is used to create groups for the Multiple Lookup process. Each group is processed together, before moving on to the next group.
If ALL of the comparisons in the group are true, then the process ends and the sample-specific action level is assigned from that group.

To enable a default action level in the report when conditions for all other groups do not apply, create a record with COMPARISON_GROUP = 0 and COMPARISON_ID = LOOKUP. Only the COMPARISON_ID = LOOKUP is processed for any zero group. The zero group is only processed if all of the other groups do not result in a positive result.

COMPARISON_ID (20 char): The CAS_RN of the analyte whose value will control the lookup process. For the Multiple Lookup process, each COMPARISON_GROUP needs to include two or more CAS_RNs and one COMPARISON_ID = LOOKUP.

COMPARISON_OPERATOR (10 char): The operator to use in the comparison between the analytical result value and the COMPARISON_VALUE. Accepted values are NULL, <, <=, >, and >=. For the COMPARISON_ID = LOOKUP row, the value does not matter, but, "=" is suggested for clarity.

COMPARISON_VALUE (19 char): The value to compare to the COMPARISON_ID'S analytical result value. For the COMPARISON_ID = LOOKUP row, the value does not matter, but leaving this blank is suggested for clarity.

COMPARISON_UNIT (15 char): The units of the COMPARISON_VALUE.

ACTION_LEVEL (19 char): The value to use as the sample-specific action level for the PARAM_CODE analyte. Only the value in the COMPARISON_ID = LOOKUP row is used for this Multiple Lookup process.

ACTION_LEVEL_UNIT (15 char): The units for the sample-specific action level. Only the value in the COMPARISON_ID = LOOKUP row is used for this Multiple Lookup process.

REMARK (2000 char): A remark that will be placed in ACTION_LEVEL_NOTE (only the first 255 characters will fit in ACTION_LEVEL_NOTE).

During the execution of the Action Level Exceedance Report, for each PARAM_CODE sample result, the Advance Action Level processor will find the values of the COMPARISON_ID analytes from the same sample and compare them to each COMPARISON_VALUE, one group at a time in numerical order; and will use the first qualifying group's COMPARISON_ID = LOOKUP row's ACTION_LEVEL as the sample-specific action level for the sample result. A group qualifies when ALL of the comparisons in the group are true.

## MULTIPLE(OR)_LOOKUP

LOOKUP_SOURCE (25 char): If this action level is from an environmental agency, the agency's name or an abbreviation of the name is appropriate for the source column.
Example: USEPA

LOOKUP_CODE (40 char): This column is used to create different categories for each source. Combining land use and matrix would be common entries for the code column.
Example: Comm_Surf_Soil

LOOKUP_METHOD (255 char): The entries in this column must match the method name exactly. In this case, that would be MULTIPLE(OR)_LOOKUP.

PARAM_CODE (CAS_RN in the EDD) (20 char): This entry must equal the value in the PARAM_CODE column of DT_ACTION_LEVEL_PARAMETER.
The first four columns above are the link between DT_ACTION_LEVEL_PARAMETER and DT_ACTION_LEVEL_LOOKUP.

COMPARISON_GROUP (integer): This column is used to create groups for the Multiple Lookup process. Each group is processed together before moving on to the next group. If one of the comparisons in the group is true, then the process ends and the sample-specific action level is assigned from that group A COMPARISON_GROUP = 0 may be added to create a default action level value. Only the COMPARISON_ID = LOOKUP is processed for any zero group. The zero group is only processed if all of the other groups do not result in a positive result.

COMPARISON_ID (20 char): The CAS_RN of the analyte whose value will control the lookup process. For the Multiple Lookup process, each COMPARISON_GROUP needs to include two or more CAS_Rns and one COMPARISON_ID = LOOKUP.

COMPARISON_OPERATOR (10 char): The operator to use in the comparison between the analytical result value and the COMPARISON_VALUE. Accepted values are NULL, <, <=, >, and >=. For the COMPARISON_ID = LOOKUP row, the value does not matter, but "=" is suggested for clarity.

COMPARISON_VALUE (19 char): The value to compare to the COMPARISON_ID'S analytical result value. For the COMPARISON_ID = LOOKUP row, the value does not matter, but leaving this blank is suggested for clarity.

COMPARISON_UNIT (15 char): The units of the COMPARISON_VALUE

ACTION_LEVEL (19 char): The value to use as the sample-specific action level for the PARAM_CODE analyte. Only the value in the COMPARISON_ID = LOOKUP row is used for this Multiple Lookup process.

ACTION_LEVEL_UNIT (15 char): The units for the sample-specific action level. Only the value in the COMPARISON_ID = LOOKUP row is used for this Multiple Lookup process.

REMARK (2000 char): A remark that will be placed in ACTION_LEVEL_NOTE (only the first 255 characters will fit in ACTION_LEVEL_NOTE).

During the execution of the Action Level Exceedance II Report, for each PARAM_CODE sample result, the Advance Action Level processor will find the values of the COMPARISON_ID analytes from the same sample, compare them to each COMPARISON_VALUE, one group at a time in numerical order, and will use the first qualifying group's COMPARISON_ID = LOOKUP row's ACTION_LEVEL as the sample-specific action level for the sample result. A group qualifies when one of the comparisons in the group is true.