Attribute Rule Expressions: calculate max value from a layer's field using Arcade

[KevinCheriyan]

I'm working with a feature class within an Enterprise GeoDB in ArcGIS Pro. I have a field called "Unique_ID" that I want to attach an attribute rule to.

Here's the rule: whenever a new feature is inserted, I want this field to calculate the maximum value from a field called "ORIG_ID" values in the same layer, add 1 to it, and update "Unique_ID" with this number. 

I have a rule within Expression Builder with the following Arcade expression. This max method being used is more or less, the same example given here: Mathematical Functions | ArcGIS for Developers under Max.

var competitors = FeatureSetByName($datastore,"route.to.FeatureClass");
var uuid = Number($feature.ORIG_ID);
Max(competitors,uuid) + 1‍‍‍‍‍‍

The expression is validated correctly. I set the triggers to be Insert and Update and save the process. But when I try to add a new row to the attribute table or create a feature on the map, the Unique_ID field is populated with the ORIG_ID value for that row + 1. Essentially, the maximum value of the entire field is not calculated.

Any ideas on how to solve this? Thanks.

[XanderBakker · Accepted solution]

Hi Kevin Cheriyan ,

Before going in to the Arcade expression you have, I would like to emphasize that there is something called a "sequence" which can create a unique number: Create Database Sequence (Data Management)—ArcGIS Pro | Documentation . There is an example of how to use it here: Attribute rule script expression examples—ArcGIS Pro | Documentation 

If you don't want to use sequences, you could change your expression to (notice that the Max function is used differently):

var competitors = FeatureSetByName($datastore,"route.to.FeatureClass");
var maxid = Max(competitors, "ORIG_ID");
return maxid + 1;‍‍‍

[KevinCheriyan]

Hi Xander, 

Thank you for links to the docs on Create DB Sequence. I don't think I can use this tool for my dataset though since my existing values for "Unique_ID" field are not always incrementing by 1. Nevertheless, thanks for this useful resource. 

On using Max() in Arcade, the code snippet you provided with the change of referring to the field as "ORIG_ID" instead of $feature.ORIG_ID worked! 

If this is the correct way to refer to a field, whether it be for calculating max, mean or other field-related stats, does this mean that the documentation provided here: Mathematical Functions | ArcGIS for Developers under Max is wrong? 

Thank you

[XanderBakker]

Hi Kevin Cheriyan ,

You can define a sequence that starts at the last number used in your field. It will take that number and whenever called, create the next number and never reuse a number. However, if a user would edit a record manually and use a number higher than the last number created by the sequence, you may run into duplicate numbers later on. This is not something that should happen, but if you would switch the calculation attribute rule off for some time and do manual edits, creating additional records and manually creating the unique number, your sequence will not be aware of that and duplicate number may be produced (just something to be aware of). 

To explain the Max (and other statistical functions) a bot more, you have two ways of using the function. One is using an array with numbers (as the example in the documentation shows). In that case you don't specify the second parameter (field name). However, when you use a featureset, you specify the featureset as the first parameter, but it will need the name of the field to perform the statistical function. That's why you have to use:

Max(competitors, "Unique_ID")

[SteveHolmes]

We discovered there may be a bit of problem with using the Create Database Sequence tool.  If you discard your edits because you want to start over, for example, the increment counter does not reset back to where you started your edits.  In other words, say you used the tool for setting up sequential ID numbers and you entered features with IDs 100, 101 and 102.  You then decided to start over by discarding the edits.  When you start editing again the next ID you get is 103 not 100.  This would be a great tool if it would reset back to where one started after discarding edits.  The work around we found is to deleted the sequence, since it apparently can't be modified, then recreate the sequence.

[BrendanFordDC]

Thank you for the post.  I had needed the same thing but kept getting tricked up by the syntax.  This worked for me as well.

[BenPaynter]

Hey Kevin,

I think I'm trying to create an attribute rule exactly as you were trying. I want the maximum number to be found first and assign the next number ID accordingly. Did you have any error like the following: "Script error: Evaluation of script expression returned not a number or infinity." when trying to add a new feature?