Setup Reference Values for EQuIS AQS Module
Copyright © 2020 EarthSoft, Inc • Modified: 14 Feb 2020
Multiple fields and tables will need to be populated to ensure that AQS values are being translated to EQuIS values and vice-versa. This is important when importing and exporting AQS data into EQuIS.
Use the EQuIS AQS-refvals Format to populate the reference tables added to the EQuIS database by the AQS Schema. This format accepts the comma-separated (CSV) reference table files distributed on the EPA’s AQS website: https://www.epa.gov/aqs/aqs-code-list. The AQS-refvals.zip format file is available for download from the Products\AQS Module folder on the Downloads Dashboard on the EarthSoft Community Center.
To translate AQS numeric codes to EQuIS reference values during the data import process, users will need to populate the following fields in the EQuIS database:
Note: When a many-to-one relationship exists between AQS reference values and EQuIS reference values, users will need to decide how to handle that association. For example, an AQS unit code of 001 and 002 may both apply to an EQuIS unit code of ug/m3, but imply certain temperature/pressure differences. Users may opt to specify the temperature/pressure differences in their RT_UNIT table and separate out the ug/m3 units, or they may choose to keep one ug/m3 record. If the preference is to keep one ug/m3 record, this should be noted for any users running standard EarthSoft reports that do not report the AQS unit code with the results.
For any data loaded using the Raw Data or Raw Blanks sections (aka Transaction Types) in the AQS Format, the raw Parameter Occurrence Codes (POC) will be stored in the DT_SAMPLE_PARAMETER table for the associated SYS_SAMPLE_CODE. The exact value will be stored in DT_SAMPLE_PARAMETER.PARAM_VALUE and the associated PARAM_CODE will be “AQS_POC”. The AQS.zip format file is available for download from the Products\AQS Module folder on the Downloads Dashboard on the EarthSoft Community Center.
For any AQS data loaded into EQuIS using a different format, users may need to setup remaps to translate a DT_SAMPLE.SAMPLE_TYPE_CODE (foreign key to RT_SAMPLE_TYPE) value to a valid POC. This will become important when exporting data to the EPA using the AQS Format export file. This can be accomplished using the EarthSoft Reference Values Format (refvals.zip), typically installed in the EQuIS Professional formats folder (e.g., C:\Program Files\EarthSoft\EQuIS\Formats\EarthSoft).
Suggestions for populating fields in the remap tables are provided below.
•REMAP_CODE – Populate this field with "AQS".
For SAMPLE_TYPE_CODE remaps, the REMAP_CODE must be in this format:
"AQS_<Screening_Group_Code>_<Parameter>", where applicable.
If <Parameter> or <Screening_Group_Code> does not exist, "AQS" should be used.
•REMAP_DESC – Populate this field with anything that contains "AQS".
•REMAP_CODE – Should match what was used in RT_REMAP.REMAP_CODE.
•EXTERNAL_FIELD – Populate with the AQS Format Section Name.Field Name or just the field name (e.g., RAW_BLANKS.TRANSACTION_TYPE or TRANSACTION_TYPE).
•EXTERNAL_VALUE – Populate with the value that will be submitted to the EPA.
•Transaction_Type-POC. Multiple EXTERNAL_VALUES can be separated by a pipe (e.g., RD-1|RD-2|RD-3).
•INTERNAL_VALUE – Populate with a valid internal RT_SAMPLE_TYPE.SAMPLE_TYPE_CODE (e.g., N).
Populate the DT_EQUIPMENT table with a list of all the monitors, calibrators, cylinders, or samplers that will be used. This can be performed using the Equipment_v1 section of the Collect_Inspection or EarthSoft EDGE Formats. The Collect_Inspection.zip format file is located in the .\Formats\ folder in the Collect package. The EDGE.zip format file is located in the .EDGE\EDGE\Formats\EDGE folder in the EDGE package.
Suggestions for populating fields in the Equipment_v1 section of the Collect_Inspection or EDGE Formats are provided below.
•EQUIPMENT_CODE – Populate with the equipment’s serial number. This code needs to be unique across the database.
•SYS_LOC_CODE – Populate with the applicable location code. For AQS, this will be a concatenation of the State-County-Site ID. For any equipment that are not tied to a specific location but need to be assigned to an activity in SPM, EarthSoft recommends that a generic placeholder location code (e.g., AIR LAB) be created so that SPM can be used.
•EQUIPMENT_TYPE – Populate with the monitor’s classification (e.g., "Air Monitor: CO" can be abbreviated to AMCO, "Air Sampler: H2S" can be abbreviated to AMH2S, and "Air Cylinder NO" can be abbreviated to ACNO).
•OWNER – Populate with the name of the group/team that performs the check (e.g., Calibration, Auditors, QA).
•EQUIPMENT_DESC – Populate with descriptive text of the type of equipment or monitor.
•OPERATION_STATUS – Populate with the operation status (e.g., Active, Inactive Retired, Decommissioned, Out for Service, Relocated).
If desired, populate the DT_PERSON table with a list of all the field personnel involved in the data collection. This is optional and may be useful when setting up field personnel fields in the Collect Forms.
•PERSON_NAME – This is a required field and the name must be unique across the database.
•TITLE – If helpful, populate this field with the person’s title or to which team they belong (e.g., Calibrator, Auditor, Sampler, Manager).
•COMPANY_CODE – This field can be used to distinguish the AQS personnel from the non-AQS personnel. In Collect or EDGE, a filter can be setup to only show the AQS personnel on forms.
Populate the DT_ACTION_LEVEL and DT_ACTION_LEVEL_PARAMETER tables to setup the action levels, range checks, or stabilization criteria for each Transaction Type or Field Activity. This can be performed using the EarthSoft Action Levels Format. The ActionLevels.zip format file is typically installed in the EQuIS Professional formats folder (e.g., C:\Program Files\EarthSoft\EQuIS\Formats\EarthSoft).
Advanced field alerts (aka range checks) and stabilization criteria allow users to select the action levels and field Method Analyte Groups (MAGs) to be used for individual locations or groups of locations. They support several new stabilization calculations and allow the combination of such criteria for a given parameter. Read more about Advanced Field Alerts and Stabilization Criteria.
These action levels are used when running Action Level Exceedance reports to compare result data with the expected range check or stabilization criteria for each Transaction Type or Field Activity. EQuIS Information Agents (EIAs) can be setup to alert individuals when submitted result data are out of range.
Reminder: The DT_ACTION_LEVEL_PARAMETER.WARNING_LEVEL and WARNING_LEVEL_MIN fields need to be populated with the appropriate range values/limits.
Suggestions for setting up the action levels are provided below.
•ACTION_LEVEL_CODE – Populate this field with anything that contains "AQS" and indicates the type of field alert (e.g., AQS_Level 1 Audit or AQS_Level 1 Audit_Trace or AQS_Precision AQS_Span, AQS_Zero and Trace).
•ACTION_LEVEL_TYPE – Populate this field for Field Alerts with a valid RT_ACTION_LEVEL_TYPE value. Suggest adding “AQS” to the table.
•REMARK – Transaction Type.
•ACTION_LEVEL_CODE – This field should match the DT_ACTION_LEVEL. ACTION_LEVEL_CODE.
•WARNING_LEVEL – Populate this field with the upper range check value/limit.
•WARNING_LEVEL_MIN – Populate this field with the lower range check value/limit.