Select to view content in your preferred language

When Multiple Child Records Exist, How to Carry Over Field Value of MOST RECENT Record to Parent Field

318
9
04-24-2024 11:17 AM
ChristopherBowering
Occasional Contributor II

Hello.  I currently have an Attribute Rule which carries over a desired value from a child field to a specified parent field (globalid to GUID, 1:M relationship class).  I didn't think about the fact that when multiple child records are associated to a single parent record I need to specify which child record's value will transfer over! 

I have a date field in the child table (DATE_INSPECTION) and would like to be able to bring the most recent/current date value from said field (amongst multiple child records in a 1:M relationship) over to a parent field (DATE_LAST_INSPECTION). It also needs to function if there is only one related child record.  I'm not proficient enough in Arcade to figure this out.  Any date-related examples I've come across are too complex for me to finagle.  If someone (possibly @MikeMillerGIS coming in clutch for a second time today??) could help me out, I'd greatly appreciate it!  My current code is below.  Feel free to tweak it or provide something totally different that might work.

 

// Calculation Attribute Rule on Child
// Triggers: Insert, Update
// Field: empty

// do nothing if value did not change
if($originalfeature.CHILD_FIELD == $feature.CHILD_FIELD) { return null }

// get the feature
var TEST = First(FeatureSetByRelationshipName($feature, "OCGIS.DPW_TESTING_REL"))
if(TEST == null) { return null }

// Send a request to update the field in the parent FC
return {
  "edit": [
    {
      "className": "PARENT_NAME",
      "updates": [
        {"globalID": TEST.GlobalID, "attributes": {"PARENT_FIELD": $feature.CHILD_FIELD}}
      ]
    }
  ]
}

 

0 Kudos
9 Replies
MikeMillerGIS
Esri Frequent Contributor

This one is a little harder, be easier to write if you can share a sample dataset.

The most performant, would be that we query the table the related record is using the foreign key to get the other records.  We then create a sort function by date to get the current date.  Then use that key or FSbyRelationship to get the parent record and update it if needed.

 

0 Kudos
ChristopherBowering
Occasional Contributor II

Thanks for responding once again, I appreciate it!

The test datasets I've been using are incredibly simplistic.  If you create a point feature class with a GlobalID field and a date field and a table with a GUID field and a date field...using a GlobalID to GUID relationship class, that's the setup I'm using.  If there are 5 table records related to a single point, for example, each with a different date, I'd like the most current date to populate the parent date field (or the only existing date if there's only one record).

What you explain makes sense conceptually.

0 Kudos
MikeMillerGIS
Esri Frequent Contributor

This should work.  If the parent features date is less than the edited child feature, update the parent.  

Expects($feature, 'DATEFIELD');
if ($originalfeature.DATEFIELD == $feature.DATEFIELD) {
    return
}

var fs = FeatureSetByRelationshipName($feature, "RELNAME", ['DATEFIELD', 'GLOBALID'], false);

var parent_feat = First(fs);

if (IsEmpty(parent_feat)) {
    return;
}
if (parent_feat.DATEFIELD > $feature.DATEFIELD) {
    return
}


return {
    "edit": [
        {
            "className": "PARENTFCNAME",
            "updates": [{
                "globalID": parent_feat.globalid,
                "attributes": {
                    "Size": $feature.DATEFIELD
                }]
        }
    ]
}
0 Kudos
ChristopherBowering
Occasional Contributor II

I will give this a try!  I assume the attribution rule is run on the child table with the field set to empty?  If it's not too much trouble, could you specify DATEFIELD_Parent or DATEFIELD_Child in your code?  Thank you!

0 Kudos
MikeMillerGIS
Esri Frequent Contributor

Yes this rule would be assigned to the child table.  As for code changes, that is an exercise for the reader.

0 Kudos
ChristopherBowering
Occasional Contributor II

I have been playing with this all morning and continue to get script errors on various lines regarding the Child field upon entering a date.

Here is how I've used your script (I've tried both with the attribute rule field empty and on the child field being used):

Parent Date Field: DATE_1

Child Date Field: DATE_INSPECTION

 

 

Expects($feature, 'DATE_INSPECTION');
if ($originalfeature.DATE_INSPECTION == $feature.DATE_INSPECTION) {
    return
}

var fs = FeatureSetByRelationshipName($feature, "OCGIS.DPW_TESTING_REL", ['DATE_1', 'GLOBALID'], false);

var parent_feat = First(fs);

if (IsEmpty(parent_feat)) {
    return;
}
if (parent_feat.DATE_INSPECTION > $feature.DATE_1) {
    return
}


return {
    "edit": [
        {
            "className": "OCGIS.DPW_TESTING",
            "updates": [{
                "globalID": parent_feat.globalid,
                "attributes": {
                    "DATE_1": $feature.DATE_INSPECTION
                }]
        }
    ]
}

 

 

The first script error message tells me the DATE_INSPECTION field can't be found on line 13.  If I swap out the parent for the child field on line 6 the error message then says the DATE_INSPECTION field can't be found on line 8 (which is odd because there isn't even a field present on that line).

I do appreciate your teaching technique mentioned in your last message but I am pretty new at this and am trying my best!  Thanks.

0 Kudos
MikeMillerGIS
Esri Frequent Contributor

You are trying to access a row that not listed in the FeatureSet, make sure you list all the fields you need to use on the parent feature in the 3rd parameters 

 

var fs = FeatureSetByRelationshipName($feature, "OCGIS.DPW_TESTING_REL", ['DATE_1', 'GLOBALID'], false);

to

var fs = FeatureSetByRelationshipName($feature, "OCGIS.DPW_TESTING_REL", ['DATE_INSPECTION ', 'GLOBALID'], false);

 

But then you are mixing fields later on in the return statement.

 

What date fields are on the parent and what are on the child?

0 Kudos
MikeMillerGIS
Esri Frequent Contributor
Expects($feature, 'DATE_INSPECTION');
if ($originalfeature.DATE_INSPECTION == $feature.DATE_INSPECTION) {
    return
}

var fs = FeatureSetByRelationshipName($feature, "OCGIS.DPW_TESTING_REL", ['DATE_1', 'GLOBALID'], false);

var parent_feat = First(fs);

if (IsEmpty(parent_feat)) {
    return;
}
if (parent_feat.DATE_1 > $feature.DATE_INSPECTION) {
    return
}


return {
    "edit": [
        {
            "className": "OCGIS.DPW_TESTING",
            "updates": [{
                "globalID": parent_feat.globalid,
                "attributes": {
                    "DATE_1": $feature.DATE_INSPECTION
                }]
        }
    ]
}
0 Kudos
ChristopherBowering
Occasional Contributor II

This seems to be working now!  I assumed the Parent field would have to be facing the 'less than' side of the operator based on the conceptual description.  The remainder of the script I did have correct during one of my attempts but I didn't try switching around the operator on that line.  Thank you very much for your help with this scripting.

0 Kudos