I have an Arcade rule that generates an assetid when a point is inserted. This code is generated by measuring and rounding the distance, in feet, of the inserted point feature from a fix reference point. The process could generate repeated assetids for point features that are located at the same distance from the reference point.
Therefore, I'm trying to compare the new assetid with the existing ones using 2 for loops, with the code shown below.
The code tries to compare the new assetid against all the existing (inner loop), if there is a duplicate then change the new assetid, compare again, and if there is no duplicate then exit the outer loop.
The rule is not work. It never enters the if statement inside the inner loop, see code below. I purposely created duplicate assetids, as you can see in the pic below. Tried adding a point and saving the edit, then adding another one and saving again, in case the Arcade rule was not seeing the repeated assetid until the edits have been saved; but that didn't work.
Is there something wrong on the code or there is a better and more efficient way to do this?
Hi Billy Guerrero ,
Just wondering, but why are you generating an assetid based on the distance in feet from a fixed point? Normally, when automatically creating an assetid, the recommendation is using the NextSequenceValue function in combination with a sequence defined in the database (Create Database Sequence—Data Management toolbox | Documentation ).
You also only showed part of the expression and I did not see any part where you return a value. Also, when the data volume starts growing, looping through the entire data will have an impact on performance. If you do need to use the distance for the assetid, use a filter first to only filter those that start with the same id and use the count to generate the new id and not a for loop. Also when there are 10 or more points at the same distance you will run into duplicate codes again with your code.
When we were reviewing the different ways to create an assetid we tested the NextSequenceValue option, but we want to have an assetid code that would have some meaning, not just any random number. Our new assetid code has the information about the district where the asset it located and the distance from a reference point. Maybe you would wonder why someone would need that since you can get that information from the map, but when you are jumping from paper maps to digital maps with the utility network, this king of transition is not easy for everybody. Our current assetid system for poles is not database friendly (a lot of duplicate numbers) but it's very intuitive for the people in the field. With our implementation of the utility network we decided to change our current assetid system, but we want to replace it with something that will convey some information for the people in the field when they are not using the digital map.
I share your concern about performance with the nested for-loops and growing data volume, that's why I was wondering if there was another method. Right now we have less than 10K poles, but we don't expect that that number will increase beyond 10K. Yesterday, I came to the same conclusion that you are suggesting and this is the new rule:
The rule performance, in the test environment, with the ~10K poles on the map is almost the same that we experience when the rule was not checking for duplicate values, so it looks like performance won't be an issue. But we still have to test it in the production environment, where we are adding the poles with a group template that includes other features (lines, electric attachment and street lights)
Since the assetid generated by the rule includes a "prefix" (pole district), I'm thinking that maybe filtering the pole class by the "prefix", to reduce the dataset being tested for duplicates, could make the rule more efficient. What do you think? Also, I still wonder why the first rule didn't work, even if it's not very efficient.
Hi Billy Guerrero ,
I can imagine that changing from paper maps to the Utility Network is a huge leap in digital transformation and will requiere proper change management for those that will interact with the UN. However, the benefits that you will have as organization are priceless. How far are you in the process of implementing the UN?
I just looked at the 2nd expression you shared and there are a couple of questions and comments I have:
When I ignore an updates and changes in distance and the prefix, I would think that the logic could be something like this:
// check first if the id is already set
var prefix = "W"; // assumably is extracted from a polygon fs
var dist_feet = 551; // dist_degree(...);
var fsPolesRules = FeatureSetByName(...);
var idcode = prefix + Text(Round(dist_feet), '00000');
// create a sql to query all features withe the same start of the assetid
var sql = "assetid LIKE '" + idcode + "%'";
// filter the fs
var fs_filtered = Filter(fsPolesRules, sql);
// use the count to create a new code
var cnt = Count(fs_filtered);
idcode = prefix + Text(Round(dist_feet), '00000') + "-" + Text(cnt+1);
// this will gloriously fail when existing points get moved
// and the id is adjusted to those changes
// return the result
As you mentioned, we agree that the benefits of the UN worth the effort. At this point, we have created our data dictionary, selected the Tiers, AG/AT (AssetGroup/AssetType), and related tables that we need. Also, we deployed the Electric Utility Network asset package with the sample data (as a testing environment) and deployed the same asset package with the schema only and the special reference changed to our location (our project environment). Since we don't have GIS quality data to import directly to the UN, we have to basically draw the network one pole at a time, with the help of group and preset templates. But we have decided to start by creating all the substations, then the interconnecting lines between them, and finally the distribution circuits and the other assets (transformers, junction box, street lights, etc) Also we're are taking our time to clean and transform the data that will be added to the UN, which will be added to the features using Arcade rules.
Thanks again for your comments and suggestions. Here are my comments: