|
<< Click to Display Table of Contents >> Navigation: Database > OBJECT_EUID |
•DT_FILE and DT_GEOGRAPHY Tables
OBJECT_EUID is a field found in several tables in the EQuIS Schema. The field is used to link child records to parent records by way of the parent’s EUID value. The OBJECT_EUID field acts as a foreign key linking the two tables when no foreign key relationship is defined between them.
The OBJECT_EUID field can be found in four data tables:
•DT_FILE
•DT_GEOGRAPHY
•DT_COST
•DT_GEO_TEST
The DT_FILE, DT_GEOGRAPHY, and DT_COST tables are part of the standard EQuIS Schema. The DT_GEO_TEST table is part of the EQuIS Geotech Schema, which must be applied to the database before the table can be found and written to in the database.
The OBJECT_EUID fields in the DT_FILE and DT_GEOGRAPHY tables can be used to associate a file or a geographic location, respectively, to any object record in the EQuIS database.
Four formats can map to the DT_FILE and DT_GEOGRAPHY tables and contain an object_euid field that can be leveraged to associate file and/or geography records to records elsewhere in an EDD: EDGE, Geotechnical_v2, EQuIS_Vapor_Intrusion, and COLLECT_INSPECTION.
When populating the object_euid field in these formats, it is not possible to use EUIDs that currently exist in the database. EDP will not recognize these EUID values and will produce an error on Create. Instead, local EUIDs can be used to associate records within an EDD.
Local EUIDs are unique identifiers that exist exclusively in EDDs to facilitate the connection between parent and child records. These fields, labeled “euid”, exist in several sections in each of the four aforementioned formats and will be populated by default starting with the value ‘1’ and will increase by 1 for each record in sections with this field. These local euids can then be used in the object_euid field of the file and/or geography section to associate the two records. Therefore, when tying a File or Geography record in an EDD to an existing record in the database, the existing database record must be recreated in the EDD and the local euid must be used in the corresponding object_euid field, rather than the EUID that already exists in the database.
On Create, EDP will update the local euid values with new, valid EUIDs that do not yet exist in the database. The corresponding object_euid values in the package will be updated with the new EUIDs.
On Commit, EDP will check the database and attempt to match records in the package to those that already exist in the database based on the primary key for each table. If a match is found for a parent record, EDP will update the OBJECT_EUID field in the child record with the EUID that is already in the database and discard the new EUID that was assigned on Create. If no match is found, both the parent and child records will be committed to the database with the new EUID value given on Create.
The Collect – Field Forms report utilizes the associations made between a field form and a DT_GEOGRAPHY record to include coordinates in the summary of a completed form.
The ‘Use DT_GEOGRAPHY for coordinate data’ parameter of the Analytical Results II report can be toggled on to include coordinate data in the output if an association has been made between DT_SAMPLE and DT_GEOGRAPHY.
The DT_FILE.OBJECT_EUID field is also used in the Notes workflow. When a note is created for a database record using the Notes Form, the contents are stored in the DT_NOTE table. If a file is attached to the note, the attachment is stored in the DT_FILE table. When this occurs, the EUID value of the DT_NOTE record is populated in the OBJECT_EUID field of the attachment's DT_FILE record to associate the two records.
The OBJECT_EUID field in the DT_COST table can be used to associate a cost to any object record in the EQuIS database.
The only format that currently maps to DT_COST.OBJECT_EUID is the Geo_Lab_Fees format, which is included with EQuIS Geotech. The format creates new records in the DT_COST table that are associated to records in the RT_GEO_TEST_METHOD table via OBJECT_EUID. Rather than populating the OBJECT_EUID manually, the “GetObjEuid” method mapping obtains the necessary RT_GEO_TEST_METHOD.EUID value by matching the Lab_Fees.test_method_code value to RT_GEO_TEST_METHOD.TEST_METHOD_CODE in the database.
Note: To associate a cost to a database record found in a table other than RT_GEO_TEST_METHOD, the association must be made manually in the database. |
Once a DT_COST record is associated to an RT_GEO_TEST_METHOD record, the Lab Fee Summary parameter of the Geotechnical Lab Forms Report can be toggled on to include a task’s associated cost(s) in the output.
The EQUIS.GEOTECH_LAB_ASSIGN_SAMPLES Geotechnical function can be used to provide a summary of the lab tests and their costs that are assigned to samples within a facility.
The only format that currently maps to DT_GEO_TEST.OBJECT_EUID is the Geotechnical_v2 format that is included with installations of EQuIS Geotech.
The OBJECT_EUID field in the DT_GEO_TEST table is populated via method mappings similar to DT_COST.OBJECT_EUID in the Geo_Lab_Fees format, rather than manually like DT_FILE.OBJECT_EUID and DT_GEOGRAPHY.OBJECT_EUID as detailed above. There are three different method mappings used in the Geotechnical_v2 format: GetSampleIdFacility, GetSampleOrLocation, and GetEUID. The relevant method mapping depends on the format section.
GetSampleIdFacility Method – This method mapping is the most common one used in the Geotechnical_v2 format. It is used in sections that map to DT_GEO_TEST/DT_GEO_RESULT and include sys_sample_code in their primary key. On Create, the method mapping will search the EDD for sample records and populate the sample’s EUID in DT_GEO_TEST.OBJECT_EUID. When using a sample value that already exists in the database, EDP will replace the temporary DT_GEO_TEST.OBJECT_EUID value in the package with the preexisting DT_SAMPLE.EUID value from the database during the Commit step.
GetSampleOrLocation Method – The Hand_Penetrometer_Test, Vane_Test, and Dynamic_CPT sections of the Geotechnical_v2 format use the GetSampleOrLocation method mapping. In these sections, either sys_loc_code or sys_sample_code must be populated, but not both. Depending on which field is populated, the method mapping will search the EDD for a matching location/sample record and populate the EUID value from the location/sample record in DT_GEO_TEST.OBJECT_EUID in the package. When using a location/sample value that already exists in the database, EDP will replace the temporary DT_GEO_TEST.OBJECT_EUID value in the package with the preexisting DT_LOCATION/DT_SAMPLE EUID value from the database during the Commit step.
GetEUID Method – The GetEUID method mapping is used in the Depth_Remarks, Electrical_Resistivity, Dissipation_Data, Dissipation_Details, and Other_Location_Results sections of the Geotechnical_v2 format to write to DT_GEO_TEST.OBJECT_EUID. During the Create step, the method mapping will use the sys_loc_code value to find and populate the object_euid field. The method will check the EDD first for any location records that match on sys_loc_code. If no such matches are identified, the method will search DT_LOCATION in the database.
Once data has been committed to the database using the Geotechnical_v2 format, associations between geotechnical tests and sample records can be viewed in EnviroInsite Boring Logs or in the output of the Geotechnical Results and Geotechnical Lab Test Status reports.
Copyright © 2024 EarthSoft, Inc. • Modified: 20 Oct 2024