topic Re: Attribute Rules: NextSequenceValue in a file database environment. in ArcGIS Pro Questions

Attribute Rules: NextSequenceValue in a file database environment.

Anonymous User — Thu, 30 Jan 2020 14:44:05 GMT

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.

Re: Attribute Rules: NextSequenceValue in a file database environment.

RobertKrisher — Thu, 30 Jan 2020 16:19:06 GMT

Let's take a look at the expression. I'm assuming this is some kind of immediate or batch calculation rule, which means that every time this expression fires the value returned from it will replace the value in your ID field.

It looks like you're trying to add in a test to see if the value is already populated, but because the statement isn't included in an if statement that means that it isn't controlling logic at all. Here is an example of an expression that will only calculate a new sequence value if the field is empty. In this case I assigned it as an immediate calculation rule on the "my_id" field.

if(isempty($feature.my_id)) //If the field is empty
return concatenate("SP-",nextsequencevalue("Test")); //calculate a new id
else //otherwise
return $feature.my_id //return the original value

Here's a screenshot of a few features I created using this rule.

Re: Attribute Rules: NextSequenceValue in a file database environment.

Anonymous User — Thu, 30 Jan 2020 16:30:35 GMT

Would I need to create a new database sequence, since I have made many attempts as shown in the ArcCatalog table screenshot.

After using the script you proved I am still getting "0" in the FID field, but the sequence counter in the catalog table continues to increase?

Re: Attribute Rules: NextSequenceValue in a file database environment.

RobertKrisher — Thu, 30 Jan 2020 16:36:59 GMT

Let's just focus on the geodatabase testing first, that way you can work out all the issues locally before pushing it all to enterprise.

Can you include the new expressions you're now using (with your field / sequence names) along with additional information about how the rule is configured? It is just assigned to fire on insert? update?

Re: Attribute Rules: NextSequenceValue in a file database environment.

Anonymous User — Thu, 30 Jan 2020 16:46:55 GMT

1.)

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

if(isempty($feature.FID)) //If the field is empty
return concatenate("SP-",nextsequencevalue("Traffic_SignalPole_ID")); //calculate a new id
else //otherwise
return $feature.FID //return the original value

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

2.)

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

if(isempty($feature.FID)) //If the field is empty
return "SP-"+nextsequencevalue("Traffic_SignalPole_ID"); //calculate a new id
else //otherwise
return $feature.FID //return the original value

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

The field is: FID

Only assigned to fire on insert.

The rules is an immediate calculation.

Re: Attribute Rules: NextSequenceValue in a file database environment.

RobertKrisher — Thu, 30 Jan 2020 16:54:42 GMT

I'll make some assumptions about the questions you didn't answer (it's an immediate calculation rule configured to fire on insert). Does your feature have a default value set on the field? Try adding logic to the check so it looks for empty OR your default value.

if(isempty($feature.FID) || $feature.FID=="0") //If the field is empty or has the default value
return concatenate("SP-",nextsequencevalue("Traffic_SignalPole_ID")); //calculate a new id
else //otherwise
return $feature.FID //return the original value

Re: Attribute Rules: NextSequenceValue in a file database environment.

Anonymous User — Thu, 30 Jan 2020 17:19:19 GMT

The FID field data type is "Long", with the concatenate function is this okay or would it need to be changed to "String" to accommodate for the addition of text characters to the field values?

Re: Attribute Rules: NextSequenceValue in a file database environment.

Anonymous User — Thu, 30 Jan 2020 17:34:26 GMT

I changed the data type for the FID field to "String" and after using the script you provided

if(isempty($feature.FID) || $feature.FID=="0") //If the field is empty or has the default value
return concatenate("SP-"+ nextsequencevalue("Traffic_SignalPole_ID")); //calculate a new id
else //otherwise
return $feature.FID //return the original value

The FID field is populated with values, but to my knowledge so far they seem to be random numbers. The positive thing is that it is returning values, now I am just trying to figure out how / where in the script this may be happening.

Also on a weird note, now when I create new features, there do not display, but are still selectable.

See attached.

Re: Attribute Rules: NextSequenceValue in a file database environment.

Anonymous User — Thu, 30 Jan 2020 18:18:15 GMT

Robert,

After some tinkering I was able to successfully populate unique IDs using this script.

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

if(isempty($feature.FID)) //If the field is empty or has the default value
return concatenate("SP-" + nextsequencevalue("Traffic_SignalPole_ID")); //calculate a new id

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

Re: Attribute Rules: NextSequenceValue in a file database environment.

RobertKrisher — Thu, 30 Jan 2020 18:25:55 GMT

Nice!

Re: Attribute Rules: NextSequenceValue in a file database environment.

Anonymous User — Thu, 30 Jan 2020 18:33:08 GMT

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?

Re: Attribute Rules: NextSequenceValue in a file database environment.

RobertKrisher — Thu, 30 Jan 2020 20:46:28 GMT

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.

Re: Attribute Rules: NextSequenceValue in a file database environment.

ChristopherBowering — Tue, 03 Mar 2020 19:27:44 GMT

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!

Re: Attribute Rules: NextSequenceValue in a file database environment.

RobertKrisher — Tue, 03 Mar 2020 19:39:36 GMT

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

If the inspection flag field is empty or set to false you'll return the current feature's FlagID value.
If the FlagID is already populated, return the current feature's flag ID value.
Then, and only then, should you return the next sequence value.
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").

Re: Attribute Rules: NextSequenceValue in a file database environment.

ChristopherBowering — Tue, 03 Mar 2020 22:09:24 GMT

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).

Re: Attribute Rules: NextSequenceValue in a file database environment.

RobertKrisher — Tue, 03 Mar 2020 22:44:03 GMT

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.

Re: Attribute Rules: NextSequenceValue in a file database environment.

ChristopherBowering — Tue, 03 Mar 2020 23:09:28 GMT

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!

Re: Attribute Rules: NextSequenceValue in a file database environment.

RobertKrisher — Wed, 04 Mar 2020 00:58:40 GMT

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.

Re: Attribute Rules: NextSequenceValue in a file database environment.

ChristopherBowering — Wed, 04 Mar 2020 14:38:48 GMT

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.

Re: Attribute Rules: NextSequenceValue in a file database environment.

RobertKrisher — Wed, 04 Mar 2020 14:46:10 GMT

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.