Attribute Rules: NextSequenceValue in a file database environment.

[Anonymous User]

Attempting to create sequential IDs for managing above ground assets.

I successfully create the database sequence and have been working with two scripts. 

1.) Returns zeros in the FID field

--------------------------------------------------------------------------------------

var FID = $feature.FID;
($feature.FID == 1)
return "SP-" + NextSequenceValue("Traffic_SignalPole_ID")

--------------------------------------------------------------------------------------

2.) The FID values remain Null

----------------------------------------------------------------------------------------

var FID = $feature.FID;
(FID == 1)
return "SP-" + NextSequenceValue("Traffic_SignalPole_ID")

----------------------------------------------------------------------------------------

If is put an "if" before the second line regardless, the field value will be null.

Also when I check the table in arc catalog is shows that the sequence is adding to the database table, but is still not being appended to the feature its self.

[Anonymous User]

Thank you for your time and help!

Is there currently a tool or script that resets the sequences or is the point to set constraints and validations so mis-numberings do not happen?

[RobertKrisher]

Gaps in the numbers are something that will occur naturally as features are deleted or edits are undone, but the sequence itself will ensure that the values that it generates are unique.  If you're worried about your users manipulating the data and creating duplicate numbers, you could create an attribute constraint or validation rule that ensures that the numbers are unique.

[ChristopherBowering]

Hi Robert,

I am trying to do something very similar to Brian but am stumbling with my Arcade expression.  I have 2 fields: "INSPFLAG" and "FlagID".  The former is a True/False domain while I am trying to make the latter an auto-incremented field.  If the "INSPFLAG" value is set to True, I want the "FlagID" to auto-increment.

I have created a database sequence for the "FlagID" field (named "FlagID_Sequence); I've set the beginning value to 300 (for testing purposes because I already have some records in the table) with an increment of 1.  This is the arcade script I thought would work but it does not.  It gets the green check mark in terms of validation but when I attempt to edit the table accordingly, the "FlagID" value does not auto-increment. 

if($feature.INSPFLAG == True)
return nextsequencevalue ("FlagID_Sequence")

Thanks for any insight you can provide!

[RobertKrisher]

If your expression fires on update you'll need to add a bit more logic to this script:

1. If the inspection flag field is empty or set to false you'll return the current feature's FlagID value.
2. If the FlagID is already populated, return the current feature's flag ID value.
3. Then, and only then, should you return the next sequence value.
4. If your "True/False" domain is actually for an integer field you may need to change your check to either compare the feature value against the underlying domain value ($feature.INSPFLAG==1), or dynamically compare against the coded value (DomainName($feature,"INSPFLAG") == "True").

[ChristopherBowering]

Thanks for the quick response.    My True/False domain is in a text field.  I actually have it set to 'insert' because I only want this to fire when a new record is created in my table and when said record has a value of True for the INSPFLAG field.  If a new record is added and it doesn't require a flag (default is False), then nothing should happen in the corresponding FlagID field (remains Null).  Since I only want this to be an 'insert', I don't believe your points 1 and 2 will be relevant as there currently won't be a FlagID associated with a new record.  This is why I thought my initial script made sense.  When I input the script in the last line of your response, it again was verified to work but ultimately did not upon my test (no error just no auto-increment occurred in the desired field).

[RobertKrisher]

Ok, because you have a domain on then your issue is likely issue #4.  My script won't work for you data as is because you need to adjust it to match the codes and values in your domain.

As an example, if your true/false domain uses a "t" for true and a "f" for false your comparison would be $feature.INSPFLAG == "t" .  You'll just substitute it with whatever string value you have in that field ("true", "TRUE", "T", "Yes", "y" "yes", "1", etc).

If you have any concerns about your code actually being executed I'd also recommend have a default return value of something like -1 so you know that you can confirm your code is running.  Any time you change your expression you will need to restart ArcGIS Pro to get the latest code to fire, and if you're editing through feature services you will also need to restart your service (if your service uses a dedicated pool of workers) or the entire ArcGIS Server service (if your service is using shared instances).  Because of this I'd recommend you make use of a service with dedicated workers while you're developing your expressions.

[ChristopherBowering]

Robert - I didn't realize that I had to close out of Pro and open it back up!  I was making changes to the attribute rule and saving assuming the changes would take affect when I put the table back into the map.  Your script is now working as it should.  Thank you very much.

I've been working in our test SDE on this (no service yet).  When I do publish this out through Pro, what do you mean by making use of the service with dedicated workers?  Or, now that I have it figured out does it not matter when publishing?

Thanks!

[RobertKrisher]

Chris - Excellent!  I've been bit by that problem more than a few times.  Now that you've got it figured out you should be good when you publish your service.  If you do decide to make changes to it in the future just make sure you stop/start the service after making changes any that anyone who was connected to the service restarts their application.

[ChristopherBowering]

Robert - I really appreciate your assistance.  I was working on ESRI on this matter but it was a slow go so this has saved me time.  Your final response to Brian on 1/30/20 was in regards to gaps in the numbering.  I've also found this to be the case in my testing when I add a handful of records, delete them, then go back to adding.  The auto-increment picks up at the last deleted value instead of restarting based on the current last remaining value.  I assume based on your response that nothing can really be done about that?  I don't have data validity concerns (you can make the field not-editable when configuring the attribute rule), more so how it looks with strange numbering gaps.

[RobertKrisher]

Chris - That is correct.  The 'nextsequencevalue' uses a database sequence which uses a simple auto-increment functionality.  If you wanted something that could go back and 'fill in gaps' or dynamically find the last used number you would have to write your own code to do that.  These rules work great for simple, local datasets but when you start dealing with Enterprise, Versioned datasets the problem becomes much harder to track down because certain numbers may be referenced in unposted versions for days or even years (and Esri doesn't provide an easy API to scan all versions for specific values).  This is why they provide and recommend you use a simple sequence.