Attribute Rule Write Related Child Attribute to Parent Table

[marksm_macomb]

Hi there,

I have a 1:M relationship between Manholes (parent) and Inspections (child). I'd like for when a new inspection is submitted, that the condition values of the most recent inspection write to the Manhole. For example, I have a field called "Cover_Condition" in the inspection table, and a field called "Cover_Condition" in the Manhole and here is the Arcade I have so far (This rule is assigned to the Cover_Condition field and triggered on insert, update, and delete, and it is excluded from application evaluation):

//Forgein Key in child table
var fk = $feature.ParentGUID_

//Load all inspections of the feature
var inspections = FeatureSetByName($datastore, "DBO.SanitaryManholeInspections",['ParentGUID_', 'Date_Time', 'Cover_Condition'], false)
var related_inspections = Filter(inspections, 'ParentGUID_ = ')

//Filter out record if delete trigger
if($editcontext.editType == "DELETE") {
    var pk = $feature.GlobalID_1
    related_inspections = Filter(related_inspections, "GlobalID_1 <> ")
}

//Filter most recent inspection date
var last_inspection = First(OrderBy(related_inspections, "Date_Time DESC"))

//Return nothing if no records found
if(IsEmpty(last_inspection)){
return ;
}

//Update Cover_Condition field in parent feature class
return {
    "result": fk,
         "edit": [{
            "className" : "DBO.ssManhole_z",
               "updates": [{
                 //selecting the parent feature to update
                 "GlobalID" : last_inspection.ParentGUID_,
                 //Update the Cover_Condition field in parent feature class 
                 "attributes" : {'Cover_Condition': last_inspection.Cover_Condition
    }
                           }]
                  }]    
    };

The expression evaluates as valid in the expression editor, but when I test in Field Maps, I get the error "Unable to complete operation. Unable to perform applyEdits operation. Error: Invalid column value [Invalid column value] [Cover_Condition]."

I did check the spelling and the field names in both the Manholes and Inspection Table are spelled that way.

I also get confused when writing expressions like this which field I should assign the rule to, or can it be left blank?

Thank you!!

[JohannesLindner · Accepted solution]

You assigned this rule to Inspections.Cover_Condition, but you return a GlobalID. My guess is that Cover_Condition has a coded value domain, so it rejects anything not in that domain. Maybe it even is an integer field, so it also can't properly convert the GlobalID (a string) into an integer.

I also get confused when writing expressions like this which field I should assign the rule to, or can it be left blank?

You can leave the field blank, in that case don't use the "result" keyword. You could also assign your expression as it is to ParentGUID_, because that's the value you return right now.

I'm surprised you don't get errors on lines 6 and 11,  because you're missing the compare values in the SQL query.

Instead of loading the Featureset in line 5, you can just use the $featureset global. $featureset returns the current Featureset, the one this Attribute Rule is assigned to.

//Forgein Key in child table
var fk = $feature.ParentGUID_

//Load all inspections of the feature
var related_inspections = Filter($featureset, 'ParentGUID_ = @fk')

//Filter out record if delete trigger
if($editcontext.editType == "DELETE") {
    var pk = $feature.GlobalID_1
    related_inspections = Filter(related_inspections, "GlobalID_1 <> @pk")
}

//Filter most recent inspection date
var last_inspection = First(OrderBy(related_inspections, "Date_Time DESC"))

//Return nothing if no records found
if(IsEmpty(last_inspection)){
    return
}

//Update Cover_Condition field in parent feature class
return {
    "edit": [{
        "className" : "DBO.ssManhole_z",
        "updates": [{
            //selecting the parent feature to update
            "globalID" : last_inspection.ParentGUID_,
            //Update the Cover_Condition field in parent feature class 
            "attributes" : {
                'Cover_Condition': last_inspection.Cover_Condition,
//                "Other_Attribute": last_inspection.Other_Attribute,
            }
        }]
    }]
}

[JohannesLindner]

Also, consider this scenario:

A manhole has one inspection and you delete the inspection. As the rule is now, the manhole will keep the inspection values. You might want to set these to null instead of aborting in line 19.

[marksm_macomb]

Thanks a bunch! I did have those keys in my original compare values, not sure why they didn't copy over. Also that $featureSet trick is helpful, thank you.

I originally thought I would have to make a new attribute rule for each condition field I wanted to calculate, but I ended calculating them all in this one. With your tips, here's what I ended up with that worked:

//Forgein Key in child table
var fk = $feature.ParentGUID_

//Load all inspections of the feature
var inspections = $featureSet
var related_inspections = Filter(inspections, 'ParentGUID_ = ')

//Filter out record if delete trigger
if($editcontext.editType == "DELETE") {
    var pk = $feature.GlobalID_1
    related_inspections = Filter(related_inspections, "GlobalID_1 <> ")
}

//Filter most recent inspection date
var last_inspection = First(OrderBy(related_inspections, "Date_Time DESC"))

//Return nothing if no records found
if(IsEmpty(last_inspection)){
return {
    "edit": [{
        "className" : "DBO.ssManhole_z",
        "updates": [{
            //selecting the parent feature to update
            "globalID" : fk,
            //Update the Condition fields in parent feature class 
            "attributes" : {
                'Cover_Condition': null,
                "Adjust_Ring_Condition": null,
                "Frame_Condition": null,
                "Seal_Condition": null,
                "Chimney_Stu_Condition": null,
                "Cone_Stu_Condition": null,
                "Wall_Stu_Condition": null,
                "Bench_Stu_Condition": null,
                "Channel_Stu_Condition": null,
                "Step_Stu_Condition": null,
            }
        }]
    }]
}
}

//Update Condition fields in parent feature class
return {
    "result": $feature.Cover_Condition,
         "edit": [{
            "className" : "DBO.ssManhole_z",
               "updates": [{
                 //selecting the parent feature to update
                 "globalID" : last_inspection.ParentGUID_,
                 //Update the Condition fields in parent feature class 
                 "attributes" : {
                'Cover_Condition': last_inspection.Cover_Condition,
                "Adjust_Ring_Condition": last_inspection.Adjust_Ring_Condition,
                "Frame_Condition": last_inspection.Frame_Condition,
                "Seal_Condition": last_inspection.Seal_Condition,
                "Chimney_Stu_Condition": last_inspection.Chimney_Stu_Condition,
                "Cone_Stu_Condition": last_inspection.Cone_Stu_Condition,
                "Wall_Stu_Condition": last_inspection.Wall_Stu_Condition,
                "Bench_Stu_Condition": last_inspection.Bench_Stu_Condition,
                "Channel_Stu_Condition": last_inspection.Channel_Stu_Condition,
                "Step_Stu_Condition": last_inspection.Step_Stu_Condition,
            }
        }]
    }]
}

[Gene_Sipes]

I have been having such a hard time. I am trying to emulate this and other examples I have found on geonet/github with no luck. I am testing in a FGDB with a parent FC and related table (1:M). I want to be able to update a status field on my parent FC with a status field on my related table from the latest record. Can you provide some context into how your setup looks, so I can make sure I am using my FC names, field names, and such as proper replacements for your script? Or even better if you have this in a FDGB maybe you could share a demo version?