Return count from Related Table to Feature Class

Question

Hello everyone!

I have a map with a Point Feature (Pole) and a Related Table (PoleRepairHistory).

I am trying to calculate how many times a light pole (parent feature) has been hit by a car, based off information in a related table (CARACCIDENT). It is a one to many relationship. For each unique pole, I’d like to get a count of how many related records contain a “Yes” Boolean value and calculate that count into a field  in the parent table (ACCIDENTCOUNT).

Is it possible to create an attribute rule that will accomplish this task? I've tried to search up solutions, but not a lot of answers for attribute rule between a related table and parent feature class.

Using ArcGIS Pro 2.9.4

JohannesLindner · Answer

For a one-time calculation: use Calculate Field on Pole.ACCIDENTCOUNT, switch to Arcade

// get the repair history for this pole
var repair_history = FeaturesetByRelationshipName($feature, "RelationshipName")

// get the repairs due to car accidents
var car_accidents = Filter(repair_history, "CARACCIDENT = 'Yes'")

// count and return
return Count(car_accidents)

For automatic calculation: Create a Calculation Attribute Rule on PoleRepairHistory

// triggers: Insert
// field: empty

// if this repair is not due to a car accident, abort
if($feature.CARACCIDENT != "Yes") { return }

// get parent pole
var pole = First(FeaturesetByRelationshipName($feature, "RelationshipName"))
if(pole == null) { return } // no related pole found, abort

// add 1 to the current accident count
var new_count = pole.ACCIDENTCOUNT + 1

// instead of returning a value, we can return a dictionary with specified keys
// to edit other tables
// https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/overview/attribute-rule-dictionary-keywords.htm
return {
    "edit": [{
        "className": "Pole", // full class name
        "updates": [{
            "globalID": pole.GlobalID,  // if the fc doesn't have GlobalIDs, use ObjectID instead
//            "objectID": pole.OBJECTID,
            "attributes": {"ACCIDENTCOUNT": new_count}
        }]
    }]
}

MikeMillerGIS · Answer

Do you want this as a pop up or stored in the attributes?  If stored as an attribute, you need to determine how this will get triggered?

- On update of the parent feature?  This seems the less than optimal route, as every update would have to search and count the number of related records.

- As a batch rule?  On the insert or modification of the child record, this would set the Caclulation required on the parent record and then you have to run Evaluate Batch rules to trigger the update on the parent, which would have logic to count the child record.  This is a good option, but takes a little scripting and requires a scheduled or nightly process.  Ee are going to run into an order of operations issue(see link below)

- On insert or change of child record?  This is probably the easiest, but we are going to run into an order of operations issue(see link below)

Because of the order of operations(https://community.esri.com/t5/attribute-rules-questions/how-to-change-the-value-of-a-field-in-a-feature/m-p/1221528#M597) I think the only option at the moment is option 1.