Using the Lead Service Line Inventory Solution

[MariahSalazar]

Watch this webinar to learn how the Lead Service Line Inventory solution can help your organization meet the federal regulations of the revised Lead and Copper Rule (LCR).

Esri’s Lead Service Line Inventory solution is available at no cost and configures ArcGIS to meet the challenges of the revised Lead and Copper Rule. Organizations can use ArcGIS to create and maintain lead service inventories, conduct material verifications in the field, manage replacement activities, monitor progress, and inform the community. This solution helps water utilities improve public health by reducing the potential of community exposure to lead in drinking water and maintaining compliance with the revised LCR.

Howard Crothers and Alex Kabak from Esri’s solutions team demonstrate how you can get started with the Lead Service Line Inventory solution, including how to

• Load existing data into the solution. 
• Collect service line information in the field.
• Coordinate service line replacement activities.
• Create dashboards to monitor progress.
• Configure a public-facing website to enable information sharing with the community.

Visit the Lead Service Line Inventory solution web page to learn more.

[bburrell]

Is there anyway to share the notepad++ document with all the If, then statements? The video does pretty good at laying everything out, but looking for details on the dlt step and python script. 

[AlexKabak]

Hi @bburrell ,

I have attached the base.py file shared in the video as a .txt file as .py files are not supported as attachments here. Please let me know if you have any questions.

Thanks,

Alex

[bburrell]

Alex, 

I am just now seeing this. Thank you very much. I will be in touch if I have any additional questions. 

[TimG_OWWSB]

I have watched the video a few times now, and even with this text document, I'm missing how it's actually implemented into the Excel file. 

Also, when I run the DLT, it doesn't create the blue Field Worksheets for me. I've redone the entire process a few times to make sure I didn't miss anything, and I still have the same results.

[AlexKabak]

In the base.py (which is located in the Scripts folder of the DataLoadingWorkspace), one of the functions is defined as "calculate_utility_status(MATERIAL_UTIL,INSTALLDATE_UTIL,RESYRBLT)".

In the Expression column of the Utility Status field in the Excel file, reference the function by adding "calculate_utility_status(!MATERIAL_UTIL!,!INSTALLDATE_UTIL!,!RESYRBLT!)"

They are nearly the same except in the excel file function the fields are wrapped in "!". Repeat these steps for Customer Status, Utility Source, and Customer Source fields. When the Execute Data Load tool runs, it knows to look for the specified functions in the base.py file and will apply the logic.

As for the blue tabs, there need to be domains in the source database fields to compare against the target database field domains. Please ensure domains are assigned to the fields in your source database. If you are running the Spatial Join tool, the domains get dropped from the result, so you will need to manually add them back before running Create Data Loading Workspace.

[SarahRowley]

Alex, can you please expand on how the blue tabs are created?  What if there are no domains in the source database?  Like TimG, I've watched this multiple times, but I'm missing how to get from loading the data, adding the expressions you've mentioned to the excel file and where the blue tabs are created.

[AlexKabak]

The blue tabs are designed to map values in source coded value domains to values in a target coded value domains. This is especially useful when the values are different or different field types. If your source data doesn't have coded value domains assigned to fields, the blue tabs do not get created.

There are a couple of options to still map your data to the solution:

1. Create domains in your source data and assign them to fields. This will automatically create the blue tabs when running the Create Data Loading Workspace tool.
2. Manually create the blue tabs (they don't have to be blue).
   • Name the blue tab the name of your source field.
   • In column A of the blue tab add the target values from the field in the solution.
   • In column B, add the source values. Ensure the title of columns A and B are not the same.
   • In the mapping tab find the target field. In the LookupSheet column add the name of the blue tab, in the LookupKeys column add the title of column A from the lookup sheet, and in the LookupValue column add the title of column B from the lookup sheet.
   • If you have values in the Lookup columns, the Expression column for that field needs to be empty.

[Laura]

I'm trying to populate data into the diameter field however it will not accept the inch character amd will therfore not append and update. 

[AlexKabak]

Hi Laura,

Is your diameter field a text/string field? If so, here are a couple of suggestions:

1. Try removing the inch character. The diameter fields of Lead Service Line Inventory are Double fields, and will not accept text characters.
2. Try using the Data Loading Tools instead of Append. Data Loading Tools will allow you to load data from a text/string field to a double field. Here is a link to the Data Loading Tools documentation. And here is a link to help with the toolbox installation

Hope this helps!