Arcade Attribute Rule to Report on Unique Field Values

VinceE

I am trying to write an Attribute Rule to report on whether a value in a field is unique within the entire field (unique IDs for features, for example). I am aware of things like database sequences, GlobalIDs, and GUIDs--that's not what this question is about. I'm using ArcGIS Pro 3.1.1, and my data is in a local File Geodatabase.

The uniqueness "reporting" will be done in a separate field. The image below shows this.

The code below achieves the result above; however, this is done using Field Calculator:

var fieldName = "DOM1"
var fieldVal = $feature["DOM1"]

// Create FeatureSet using field name above. Do not include geometry.
var featSet = FeatureSetByName($datastore, "ATTR_RULE_TEST", [fieldName], false)
Console(`ROWS IN FEATURE SET: ${Count(featSet)}\n`)

// Create a new FeatureSet, dissolving by given field.
var groupedFeatSet = GroupBy(featSet, [fieldName], {name: "COUNT", expression: "0", statistic: "COUNT"})

// Filter FeatureSet to where FeatureSet ID matches ID of the row being calculated.
var idCountStats = First(Filter(groupedFeatSet, `DOM1 = ${fieldVal}`))

// Get the number of times the current ID appears in the FeatureSet. If > 1, it's not unique.
var idCount = idCountStats["COUNT"]
var result = IIf(idCount > 1, "NOT UNIQUE", "UNIQUE")

Console(`FeatureSet Row (w/ Count Info): ${idCountStats}\nCOUNT: ${idCount}\nRESULT: ${result}`)
return result

My goal would be to implement this is a Calculation-style Attribute Rule, updating the "UNQ" column as IDs change (Updates and Insertions). The error that is reported to me when attempting to save the same code in an Attribute Rule is: "ERROR 002717: Invalid Arcade expression, Arcade error: General SQL error, Script line 12," which corresponds to the SQL query on that line.

But... if a modification is made to a row resulting in that row becoming a duplicate, there must also be another row that requires updating simultaneously (because it too, will have become a duplicate), which may not be possible.

As an alternative, it seems like this should be implementable as a Constraint-style Attribute Rule, however I'm getting the same error message when attempting to save the rule. Any advice would be appreciated.

JohannesLindner

I used "IntegerField1" as my ID field and "TextField1" as status field. Change those to your field names (lines 9, 11, 36, 43).

// Calculation ATtribute Rule
// field: leave empty
// triggers: insert, update, delete
// exclude from application evaluation

var mode = $editcontext.editType

// get the new and old id, do nothing if this is an update that doesn't change the id
var ids = [$feature.IntegerField1]
if(mode == "UPDATE") {
    Push(ids, $originalfeature.IntegerField1)
    if(ids[0] == ids[-1]) { return }
}

// prepare empty arrays to store the updates and the uniqueness of the ids
var updates = []
var uniqueness = []

// loop over the ids
for(var i in ids) {
    // get all features with that id
    var id = ids[i]
    var query = "IntegerField1 = <a href="https://community.esri.com/t5/user/viewprofilepage/user-id/354972">@ID</a>"
    var fs_id = Filter($featureset, query)
    // count those features (substract the current feature if this is a delete))
    var c_id = Count(fs_id)
    if(mode == "DELETE") { c_id -= 1 }
    // determine the uniqueness
    var unique = IIf(c_id > 1, "Not Unique", "Unique")
    Push(uniqueness, unique)
    // loop over those features
    for(var f in fs_id) {
        // skip the current $feature to avoid cycling error
        if(f.GlobalID == $feature.GlobalID) { continue }
        // store the update to that feature in the update array
        var update = {globalID: f.GlobalID, attributes: {TextField1: unique}}
        Push(updates, update)
    }
}

// return the result and the updates
return {
    result: {attributes: {TextField1: uniqueness[0]}},
    edit: [{className: "TestPoints", updates: updates}]
}