Automating Field Data Collection: Lookup Tables
Automation with Lookup Tables
Part 3 0f 5
By Tom DeWitte and Tom Coolidge
Our first blog of this series provided an overview of the steps a utility can take to improve the productivity of the utility field worker. If you missed it, you can access it here.
The second blog of this series explained how barcodes can help to automate field data collection. If you missed it, you can access it here.
In this blog article, we will dig into the second method in automating field data collection. The second method for making life easier for the utility field worker is to deploy a configuration of ArcGIS Field Maps that uses lookup tables to auto-populate what the organization already knows about the asset or data collection activity. As noted in the first blog article, this is the second of four methods to automate field data collection.
- Minimize manual data entry
- Auto-populate what is already known
- Leverage sensors on your mobile device
- Use geography
Solving the Steel Issue
Within pipe utility organizations, automating the documentation of steel pipe construction has been a challenge. Unlike plastic pipe, valves, and fittings, there is no industry adopted barcoding standard for steel. The polyethylene pipe and component manufacturers have adopted the ASTM F2897 barcode standard. But steel has not done so.
For many pipe utility organizations, documenting steel pipe construction is a very manual process. It requires the utility field worker to:
- Obtain the manufacturer paperwork or a copy of manufacturer paperwork to acquire the information about the steel components.
- Read through the many pages of documentation to find the desired information.
- Manually enter the retrieved information into the mobile application.
- Now leave the truck, walk to the construction trench, or direct bore and capture GNSS coordinates of steel components.
- Return to the truck to retrieve the information for the next unique steel component.
- Repeat process for each unique steel pipe, steel valve, and steel fitting.
Automating the documentation of steel construction requires a different method to streamline data entry. Lookup tables can be that method.
What are Lookup Tables
Lookup tables are Esri geodatabase tables. The purpose of these tables is to store information that the organization already knows about the specifications of the pipe segment or pipe component prior to construction. In the design and cost estimation phase of a project, this information is often referred to as compatible units.
To avoid complications with using these geodatabase tables to auto-populate an asset’s record, the lookup table should be a schema duplicate of the featureclass it is supporting. For example, there should be a separate asset catalog table for just the pipe data. This geodatabase table would be a schema duplicate of the PipelineLine featureclass in the gas and pipeline industry data model, UPDM.
Pipe Asset Catalog – PipelineLine schema
Device Asset Catalog – PipelineDevice schema
Fitting Asset Catalog – PipelineJunction schema
Auto-Populating what is known
When addressing the steel construction documentation issue, these lookup tables contain the information the pipe utility organization knows about the steel pipe or component prior to construction. This is typically more information than is provided by the plastic barcodes. For steel this includes knowing the additional pipe characteristics for Barlow’s equation, such as Specified Minimum Yield Strength (SMYS), outside diameter and design factors. These tables can also contain other steel pipe or component characteristics, such as coating type, and pipe specification.
These asset catalog tables are pre-populated with the known information. This pre-population occurs prior to the project entering the construction phase. At many gas and pipeline organizations, this list does not change dramatically between projects. Gas and pipeline organizations have codes and standards that they follow, and a select number of vendors from whom they purchase pipe and pipe components.
Deploying the Automation
Deploying a technology which is continually, and aggressively enhancing its capabilities, such as ArcGIS, means that sometimes there are different deployment patterns for ArcGIS Field Maps with a certain version of ArcGIS Enterprise versus ArcGIS Field Maps with ArcGIS Online.
For ArcGIS Enterprise 10.9.1, the ability to use form calculations in Field Maps did not exist when Enterprise was released in December of 2021. For this environment you will need to use geodatabase attribute rules for the automation.
For this article we will focus on the use of ArcGIS Field Map form calculations, as that provides a consistent capability for an ArcGIS Online deployment and an ArcGIS Enterprise version 11.0 deployment. A key benefit of using ArcGIS Field Map form calculations is that they work in both a connected and disconnected network environment.
Step 1: Create asset catalog tables.
When duplicating the schema of your system of record layers, it is useful to use the Featureclass to XML Workspace tool available in ArcGIS Pro. This preserves the coded value domains assigned to the data fields.
Step 2: Add the data field AssetCatalogID to both the asset catalog table and the asset layer
The AssetCatalogID field should be a short integer field to eliminate the issue of trailing and leading spaces when querying the table for the record.
Step 3: Populate the asset catalog tables with asset information.
Step 4: For each asset layer to query the asset catalog tables, create a coded value domain with a listing of all asset catalog entries.
The code of the coded value domain will be used to query the asset catalog record containing the utility known information about the asset.
Asset Layer assetcatalogid = asset catalog table assetcatalogid
This coded value domain needs to be assigned to the “assetcatalogid” field which was added to the asset layer in step 2. The description of this coded value domain will be what field users see when selecting an asset catalog item to describe the newly constructed asset
When working with a subtype heavy asset layer such as PipelineJunction (enterprise) has a separate subtype for each type of fitting. A separate coded value domain should be used for each subtype (ie. type of fitting). This shortens the picklist presented to the field user. These different coded value domains can still query the same asset catalog table.
Step 5: Assign the arcade script to query asset catalog table
The last piece of this configuration of Field Maps is to use the ArcGIS Field Maps web application to assign a form expression to the asset layer field to be automated. In this example that will be the manufacturer field. Here is an example of the form calculation to assign to manufacturer:
//Form Calculation: Pipe_Barcode_Manufacturer
//Description: Read the BARCODE, then decode and populate the MANUFACTURER value from the BARCODE value
//Description: If an ASSETCATALOG value is entered, query the AssetCatalog Pipes table to retrieve MANUFACTURER value.
//Field: MANUFACTURER
//Edit scenario 1: A barcode has been entered
if ($feature.barcode != null)
return mid($feature.barcode,0,2)
//Edit scenario 2: No barcode, no Asset Catalog entry
else if ($feature.assetcatalog == null)
return ($feature.manufacturer)
//Edit scenario 3: No barcode, AssetCatalog value has been entered
var assetcatalogId = $feature.assetcatalog;
if (assetcatalogId == null) {
return;
}
//Query assetcatalog table to retrieve MANUFACTURER values
var cuTable = FeatureSetByName($map, "AssetCatalog Pipes", ['manufacturer'], false);
//Filter the selected Assetcatalog table records based on assetcatalogid value
//Use the first record returned from the query
var cuAttribute = First(Filter(cuTable, 'assetcatalog = @assetcatalogId'));
if (cuAttribute == null) {
return;
}
else {
return cuAttribute.manufacturer
}
With the asset catalog tables deployed and the form expression in place to query the table, this automation is ready to put in the hands of your field users.
Going to the Field
The user experience is very intuitive for the field user. Simply tap on the Asset Catalog data field. This will open the coded value domain list. The field user taps on the desired type of asset to select from the list.
Once selected, the form expressions will immediately initiate. This will populate the edit form with the asset catalog table retrieved data.
With the steel asset data retrieved and populated, the field user can focus on completing the rest of the edit form.
Automating Data Entry
The gas and pipeline industry has long struggled with how to automate the documentation of new steel pipe construction. Lookup tables in ArcGIS Field Maps is one approach to solving this inefficiency and additional cost.
Using lookup tables is a very straight forward method for automating data entry by field users. This method of automation is applicable to many instances where field users are being asked to enter information that the organization already knows.
About This Blog Series
This blog article is the third in a series of five blog articles. Upcoming blogs will continue explaining in greater detail how to configure the Esri ArcGIS Field Maps mobile application to deploy these examples.
PLEASE NOTE: The postings on this site are our own and don’t necessarily represent Esri’s position, strategies, or opinions.