Select to view content in your preferred language

Arcade Attribute Rule to Report on Unique Field Values

1318
7
Jump to solution
04-10-2023 04:36 PM
VinceE
by
Frequent Contributor

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.

VinceE_0-1681168846038.png

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.

0 Kudos
1 Solution

Accepted Solutions
JohannesLindner
MVP Frequent Contributor

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 = @ID"
    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}]
}

 


Have a great day!
Johannes

View solution in original post

7 Replies
JohannesLindner
MVP Frequent Contributor

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 = @ID"
    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}]
}

 


Have a great day!
Johannes
VinceE
by
Frequent Contributor

Thanks for the reply! If you have any idea why my version works via Field Calculator but not as an Attribute Rule, I would love to hear it. Must be something about how a script executes in one environment vs. the other?

This script sort of works, but it still doesn't address updating both rows once an ID becomes a duplicate. In other words, if I update row 1 to be "9", then row 2, which is already "9", should also be marked as a duplicate. Currently, that doesn't seem to happen.

Below, I have changed the first row from "8" to "9", and the "UNQ" field in that row correctly updates to "Not Unique." However, the second row, which is now ALSO a duplicate (value = "9"), does not receive the same update in the "UNQ" field. To be clear, I have also saved all edits. Perhaps this type of update is not possible? I tried with a fresh feature class using your field names as well, just to be sure.

VinceE_0-1681230898446.png

VinceE_3-1681232138572.png

BUT, if I manually change the second row to some other value, then back to "9", I get the correct reporting in the uniqueness field for that row as well, as expected.

VinceE_1-1681231087563.png

THEN, if I delete the second row, the first row (the one that remains, undeleted) will correctly update from "Not Unique" to the correct "Unique":

VinceE_2-1681231125466.png

Thanks again for the help, and anything additional you can provide would be appreciated!

0 Kudos
JohannesLindner
MVP Frequent Contributor

Thanks for the reply! If you have any idea why my version works via Field Calculator but not as an Attribute Rule, I would love to hear it. Must be something about how a script executes in one environment vs. the other?

What follows is pure speculation...

I think it has to do with null values. When you validate the script in the field calculator, it picks a feature from the table and runs the script on it to see if there are any errors. When you validate an Attribute Rule, I think it creates a new feature with default values (don't quote me on this, I'm really not sure!).

If you have an empty id field, the SQL query will be "DOM1 = null", which is invalid. The correct query would be "DOM1 is null". In the field calculator, your script doesn't see any null value, so it validates. In the Attribute Rule, DOM1 will be null and the script errors out.

 

This script sort of works, but it still doesn't address updating both rows once an ID becomes a duplicate.

It should do that, but you might have to refresh the table to see it.

 


Have a great day!
Johannes
VinceE
by
Frequent Contributor

I hadn't thought to "Refresh," works perfectly as expected!

Your speculation above is very interesting as well, I appreciate it! I'll play around with that. Regardless, it's helpful in thinking about what's going on under the hood.

Thanks again!

0 Kudos
VinceE
by
Frequent Contributor

I investigated your speculation about SQL and nulls, and it seems like you were correct! I added a "null catch" into my original script, and it now works as a Calculation Attribute Rule without throwing the SQL error upon saving the rule. Snippet of the catch (with the problematic SQL on line 5) is below:

if (fieldVal == null) {
    return "Null!"
}

var idCountStats = First(Filter(groupedFeatSet, `DOM1 = ${fieldVal}`))

This works, but my script was not advanced enough to update the other fields that would also need updating, in the event that those rows became duplicates after an update to a different row. So, you solved a problem I wasn't aware I had yet when I originally posted. Thanks again!

0 Kudos
VinceE
by
Frequent Contributor

@JohannesLindner one more question for you--I think I've managed to break this using Field Calculator (as Field Calculator often has the power to do...).

Here is the feature class table correctly reporting on the "uniqueness" of each of the IDs. As the screenshot shows, I have a pending calculation for the entireIntegerField1 column.

VinceE_0-1681318820333.png

And here we are after the calculation. Note there are no pending edits here, and the table has been refreshed. In my testing, it seems like every record processes correctly, except the last row processed, which is typically (or always?) the last ObjectID.

VinceE_1-1681318880115.png

So, for some reason, Field Calculator processes the given calculation for each row, and then kicks out at the last row without activating the Attribute Rule maybe? Or perhaps, Field Calculator is iterating over a temporary copy of the rows in such a way that at the very last row, it's the only row "left" in memory, and since there are no other rows to compare the value to, it will always report "Unique?" Any other ideas you have would be fantastic.

Here's another one with the @OID shown (sort in this case based on the GlobalID).

VinceE_3-1681319847258.png

 

0 Kudos
JohannesLindner
MVP Frequent Contributor

Oh, that's a very good catch.

The problem is line 12.

 

A common problem with Attribute Rules that edit other features is cycling. Imagine line 12 wasn't there. You change the id of feature F1 to be the same as the id of feature F2. The rule will return "Not Unique" for F1 and will instruct ArcGIS to also edit F2 to be "Not Unique". This is an actual edit, meaning that the rule will now trigger for F2. It returns "Not Unique" and instructs ArcGIS to edit F1. This in turn triggers the rule for F1 and so on. You get an infinite cycle (but ArcGIS recognizes that and gives an error instead).

We have to have logic that says "hey, we already calculated the uniqueness for this feature, carry on". This is what line 12 does: if the id didn't change in this update, abort.

The problem is that the condition is too simple. Here's what happens when you have 3 features F1, F2 & F3, all with id = 5, and you change it to 6 with the field calculator:

  1. rule is executed on F1 (original edit). ids = 6, 5, 5. It returns "Unique" for F1 and schedules edits for F2 & F3 to be "Not Unique" (their id is still 5)
    1. rule is executed on F2 (edit by F1). Its id didn't change yet, so the rule aborts
    2. rule is executed on F3 (edit by F1). Its id didn't change yet, so the rule aborts
  2. rule is executed on F2 (original edit). ids = 6, 6, 5. It returns "Not Unique" for F2 and schedules edits for F1 ("Not Unique") & F3 ("Unique")
    1. rule is executed on F1 (edit by F2). Its id hasn't changed since the rule was last executed here, so the rule abort. F1 is still "Unique"!
    2. rule is executed on F3 (edit by F2). Its id hasn't changed, the rule aborts.
  3. rule is executed on F3 (original edit). ids = 6, 6, 6. It returns "Not Unique" for F3 and schedules edits for F1 ("Not Unique") & F2 ("Not Unique")
    1. rule is executed on F1 (edit by F3). Its id hasn't changed since the rule was last executed here, so the rule abort. F1 is still "Unique"!
    2. rule is executed on F2 (edit by F3). Its id hasn't changed, the rule aborts.

So the feature that stays "Unique" was actually the first feature to be calculated.

 

To solve that, we have to let the edit happen if the uniqueness changed since the last update (line 32). So we have to calculate the uniqueness before deciding whether to abort. This leads to more computational demand (which is probably only noticeable for big tables, but still). We can alleviate that by not scheduling an update if the uniqueness of the tested feature didn't change (line 41).

As before, change the field names in lines 9, 11, 20, 41, 43, 50.

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

var mode = $editcontext.editType

// get the new and old id
var ids = [$feature.IntegerField1]
if(mode == "UPDATE") {
    Push(ids, $originalfeature.IntegerField1)
}

// get the filtered featuresets and the uniqueness for the ids
var featuresets = []
var uniqueness = []
for(var i in ids) {
    // get all features with that id
    var id = ids[i]
    var query = "IntegerField1 = @ID"
    var fs_id = Filter($featureset, query)
    Push(featuresets, fs_id)
    // 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)
}

// abort if this is an UPDATE and the id and the uniqueness didn't change
if(mode == "UPDATE" && ids[0] == ids[1] && uniqueness[0] == uniqueness[1]) { return }

// get the needed updates
var updates = []
for(var i in ids) {
    for(var f in featuresets[i]) {
        // don't update the current $feature to avoid cycling error
        if(f.GlobalID == $feature.GlobalID) { continue }
        // don't schedule update if uniqueness didn't change
        if(f.TextField2 == uniqueness[i]) { continue }
        // store the update to that feature in the update array
        var update = {globalID: f.GlobalID, attributes: {TextField1: uniqueness[i]}}
        Push(updates, update)
    }
}

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

 


Have a great day!
Johannes
0 Kudos