nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Attribute rule to actively transfer data from a related table to it’s parent feature class and vice versa

Terry_Bearb

Using ArcGIS Enterprise 10.9.1 - Field Maps version 21.4.0. I am curious if we are able to create an attribute rule to transfer a value from a related table to the parent feature class upon record submission?

I currently have attribute rules in place that transfers a water meter’s brand, meter ID, and latest reading from the parent feature class to the related work history table when the record is created. This is successful. I would like to then use another attribute rule on the feature class to automatically update the new meter ID once the new value is typed into the related work history table.

Is this type of workflow supported - actively transferring data from the parent feature to its related table and vice versa? If so, is the attribute rule created in the same manner? Unfortunately I’m not having successful results returning values from the related table back to the parent feature class.

Trouver plus de publications taguées avec

Accepted answers

Terry_Bearb

Hi @JohannesLindner I just wanted to reach back out to you as a follow up and say that I finally have the expression working for me! Posted below is what ended up working for me where I had to specifically call out the field I wanted to use for the update. I then just applied the expression to individual fields when I set up the rule. I want to thank you for all your help and getting me on the correct path, it is really appreciated!

// Calculation Attribute Rule on WaterMeterWorkHistory table
// Triggers: Insert, Update
// Execution: Exclude from application evaluation
// Field: New_MeterId

var water_meter = First(FeatureSetByRelationshipName($feature, "WaterMeterTOWorkHistory"))
if($originalfeature.New_MeterId == $feature.New_MeterId) { return null }

// get attributes from water_meter
var old_attributes = {
  "Meter_ID": water_meter.Meter_ID
}

// map the attribute names from the work table to the feature class
var update_attribute_map = [
  ["New_MeterId", "Meter_ID"]
]

// if the new attributes are filled in, add them to the edit request
var updated_attributes = Dictionary()
for(var i in update_attribute_map) {
  var work_att = update_attribute_map[i][0]
  var asset_att = update_attribute_map[i][1]
  var new_att = $feature.New_MeterId
  var old_att = water_meter[asset_att]
  if(!IsEmpty(new_att)) {
    updated_attributes[asset_att] = new_att
  }
}
var edit = [{"className": "WaterMeter", "updates": [{"globalID": water_meter.GlobalID, "attributes": updated_attributes}]}]

return {
  "edit": edit
}

All comments

HusseinNasser2

That should be possible, you need to add an attribute rule to the related table. One thing to watch for is cyclic execution and infinite loops. (Parent updates child, child turns out updates parent, which turns out and update child and that goes on.) We detect and protect against such cases but you will need to write the rules such that it has a base condition to quit.

JohannesLindner

It is possible. But you dont define the rule on the feature class, you do it on the work history table.

// Attribute Rule on the work history table
// Triggers: Update
// Field: empty

// do nothing if meter id did not change (so you don't send senseless update requests)
if($originalfeature.MeterID == $feature.MeterID) { return null }

// get the water meter
var water_meter = First(FeatureSetByRelationshipName($feature, "RelationshipName"))
if(water_meter == null) { return null }

// Send a request to update the MeterID in the parent FC
return {
  "edit": [
    {
      "className": "WaterMeterFC",
      "updates": [
        {"globalID": water_meter.GlobalID, "attributes": {"MeterID": $feature.MeterID}}
      ]
    }
  ]
}

Terry_Bearb

Thank you @HusseinNasser2 and @JohannesLindner for the replies. I think my main issue comes from me placing the attribute rule on the parent feature class rather than the related work history table and there seemed to be some looping occurring. Can you guys please let me know if this is what I should be inputting as an attribute rule?

Where Old_MeterId is the field on the work history table that stores the original Meter ID from the feature class, New_MeterId is the field on the work history table that is used for input of the new Meter ID after it has been replaced, test.DBO.WaterMeterTOWorkHistory_TEST is the name of the relationship class being used, test.DBO.WaterMeterTEST is the name of the parent feature class, and Meter_ID is the Meter ID field on the parent feature class that is to be updated if a new Meter ID was inputted into the work history table.

// Attribute Rule on the work history table
// Triggers: Update
// Field: empty

// do nothing if meter id did not change (so you don't send senseless update requests)
if($originalfeature.Old_MeterId == $feature.New_MeterId) { return null }

// get the water meter
var water_meter = First(FeatureSetByRelationshipName($feature, "test.DBO.WaterMeterTOWorkHistory_TEST"))
if(water_meter == null) { return null }

// Send a request to update the MeterID in the parent FC
return {
"edit": [
{
"className": "test.DBO.WaterMeterTEST",
"updates": [
{"globalID": water_meter.GlobalID, "attributes": {"MeterID": $feature.Meter_ID}}
]
}
]
}

JohannesLindner

What's your workflow here? Do you insert the work history record with Old_MeterID and New_MeterID or do you change New_MeterID after the record is already inserted?

Terry_Bearb

Both of the values are inserted at the same time. User input on the New_MeterId field in the work history table and a triggered insert/update immediate attribute rule on the Old_MeterId field to populate the value from the parent FC.

JohannesLindner

OK, then the rule should trigger on Insert.

// Attribute Rule on the work history table
// Triggers: Insert
// Field: empty


// get the water meter
var water_meter = First(FeatureSetByRelationshipName($feature, "test.DBO.WaterMeterTOWorkHistory_TEST"))
if(water_meter == null) { return null }

// construct the edit parameter, only fill it if meter got replaced
var edit = []
if($feature.New_MeterID != water_meter.MeterID) {
  Push(edit, {
    "className": "test.DBO.WaterMeterTEST"
    "updates": [{"globalID": water_meter.GlobalID, "attributes": {"MeterID": $feature.MeterID}}]
  })
}

// return
return {
  "result": {
    "attributes": {
      "Old_MeterID": water_meter.MeterID,
// you can also copy the other fields here:
//      "Brand": water_meter.Brand,
//      "LatestReading": water_meter.LatestReading,
    }
  },
  "edit": edit
}

Terry_Bearb

I haven't had much success with placing the attribute rule on the related table to update the feature class. Just out of pure curiosity, I placed an attribute rule on the parent feature class with the following settings / expression.

Field: Meter_ID

Trigger: Insert / Update

var WM = FeatureSetByName($datastore, 'waterutilities.DBO.WorkHistoryTEST');
var water_meter = First(FeatureSetByRelationshipName($feature, "waterutilities.DBO.WaterMeterTOWorkHistory_TEST"))
var globalid = $feature.globalid
var filterStatement = 'Related_Asset = @globalid'
var related_data = Filter(WM, filterStatement)
var cnt = Count(related_data);

var results = "";

if ((cnt > 0) && (water_meter.Issue == 'MR')) {
for (var row in related_data) {
var line = row.New_MeterId;
results +=line;
}
} else {
results = "No related record";
}
return results;

The new Meter_ID is placed in the field on the FC (from the related table) after the record is created, but is appending the Meter_ID to whatever was previously there with each new record that is added. So, Meter ID of 7777 then becomes 77778888 when a new related record is added and then becomes 777788889999 when another record is added. Rather than just 8888 or 9999. Any ideas why?

JohannesLindner

The first error comes from me not testing the rule. When no relates asset is found, the rule should just return, not return null.

Second error: no idea.

I built a rudimentary structure:

Feature class:

Table:

Relationship class:

Attribute rule on the work table:

// Calculation Attribute Rule on WaterMeterWorkHistory
// Triggers: Insert
// Exclude from application evaluation
// Copies old values from water meter t
// get the water meter
var water_meter = First(FeatureSetByRelationshipName($feature, "WaterMeterTOWorkHistory"))
if(water_meter == null) { return }

// get attributes from water_meter
var old_attributes = {
  "Old_MeterID": water_meter.Meter_ID,
  "Old_Brand": water_meter.Brand,
  "Old_Reading": water_meter.Latest_Reading,
}

// map the attribute names from the work table to the feature class
var update_attribute_map = [
  ["New_MeterID", "Meter_ID"],
  ["New_Brand", "Brand"],
  ["New_Reading", "Latest_Reading"],
]

// if the new attributes are filled in, add them to the edit request
var updated_attributes = Dictionary()
for(var i in update_attribute_map) {
  var work_att = update_attribute_map[i][0]
  var asset_att = update_attribute_map[i][1]
  var new_att = $feature[work_att]
  var old_att = water_meter[asset_att]
  if(!IsEmpty(new_att)) {
    updated_attributes[asset_att] = new_att
  }
}
var edit = [{"className": "WaterMeter", "updates": [{"globalID": water_meter.GlobalID, "attributes": updated_attributes}]}]

// return
return {
  "result": {"attributes": old_attributes},  // copy old attributes from water meter to work table
  "edit": edit                               // copy new attributes from work table to water meter
}

WaterMeter:

WaterMeterWorkHistory:

WaterMeter after these changes:

JohannesLindner

It's because of this line: results +=line

What your code does is: go through ALL related work records and concatenate their New_MeterIDs. You need some way to check only the LATEST work record.

You shouldn't need the filter, as the FeatureSetByRelationshipName function returns only the related work records.

Note that, while this is absolutely a legit way of copying the new Meter_ID to the feature class, this won't happen automatically. You have to update the WaterMeter after you added a new WaterMeterWorkHistory record.

Terry_Bearb

Hi, @JohannesLindner I really appreciate all the help you have given me thus far! I am admittedly new to arcade expressions / attribute rules so your assistance is really helpful. I hate to be nuisance, but for whatever reason it keeps telling me the fields in the first index [0] cannot be found for the $feature work history table. Everything appears to be set up correctly. From what I can tell, the fields are able to be found in the second index [1] water_meter FC.

Field "New_MeterId" cannot be found

Work History table fields

Water Meter FC fields

JohannesLindner

Hey! Busy week, sorry for ghosting you...

Sadly, I can't really help you here. The fields seem to be fine.

My only suggestion would be to rebuild the two tables in a test gdb and try it again, mabe there's some interference from other rules (or something else, idk).

Terry_Bearb

// Calculation Attribute Rule on WaterMeterWorkHistory table
// Triggers: Insert, Update
// Execution: Exclude from application evaluation
// Field: New_MeterId

var water_meter = First(FeatureSetByRelationshipName($feature, "WaterMeterTOWorkHistory"))
if($originalfeature.New_MeterId == $feature.New_MeterId) { return null }

// get attributes from water_meter
var old_attributes = {
  "Meter_ID": water_meter.Meter_ID
}

// map the attribute names from the work table to the feature class
var update_attribute_map = [
  ["New_MeterId", "Meter_ID"]
]

// if the new attributes are filled in, add them to the edit request
var updated_attributes = Dictionary()
for(var i in update_attribute_map) {
  var work_att = update_attribute_map[i][0]
  var asset_att = update_attribute_map[i][1]
  var new_att = $feature.New_MeterId
  var old_att = water_meter[asset_att]
  if(!IsEmpty(new_att)) {
    updated_attributes[asset_att] = new_att
  }
}
var edit = [{"className": "WaterMeter", "updates": [{"globalID": water_meter.GlobalID, "attributes": updated_attributes}]}]

return {
  "edit": edit
}

ChristopherBowering

Hi there. I am interested in doing what you have already accomplished successfully - using an attribute rule to transfer parent feature class attributes to a related table. Would you be able to share your workflow/arcade script for that? My nuances would be combining (summation) a few fields' worth of attributes from the parent with a few from the child (these child fields populated once child record is created) to come up with a single number. However, one step at a time! Thank you.

Terry_Bearb

Hi, @ChristopherBowering, glad you found this useful. Here is the attribute rule we have running in the background on the related table that passes an attribute from the parent feature class (xyz.DBO.WaterMeter) to the related table.

Here, the last recorded meter reading (field = Current_Read) will get transferred to the related work history table's field called "Old_Read". You'll see I have lots of else if lines here, but that is only because I wanted the rule to be triggered when certain Issue subtypes were selected for the related table when a record was being created.

//Pass old read to Work History
//Field: Old_Read
//Triggers: Insert, Update
//Execution: N/A
var WM = FeatureSetByName($datastore, 'xyz.DBO.WaterMeter');
var globalid = $feature.Related_Asset
var filterStatement = 'globalid = @globalid'
var related_data = Filter(WM, filterStatement)
var cnt = Count(related_data);

var results = "";

if ((cnt > 0) && ($feature.Issue == 'MR')) {
 for (var row in related_data) {
  var line = row.Current_Read;
  results +=line;
 }
} else if ((cnt > 0) && ($feature.Issue == 'L')) {
 for (var row in related_data) {
  var line = row.Current_Read;
  results +=line;
 }
} else if ((cnt > 0) && ($feature.Issue == 'TR')) {
 for (var row in related_data) {
  var line = row.Current_Read;
  results +=line;
 }
} else if ((cnt > 0) && ($feature.Issue == 'BL')) {
 for (var row in related_data) {
  var line = row.Current_Read;
  results +=line;
 }
} else if ((cnt > 0) && ($feature.Issue == 'F')) {
 for (var row in related_data) {
  var line = row.Current_Read;
  results +=line;
 }
} else if ((cnt > 0) && ($feature.Issue == 'MB')) {
 for (var row in related_data) {
  var line = row.Current_Read;
  results +=line;
 }
} else {
 results = "No related record";
}
return results;

ChristopherBowering

Thanks for sharing that! In your example, it looks like the "globalID" field in both the feature class and related table is used as the relationship key. In the majority of my relationship classes, I use "globalID" from the feature class and a "GUID" field from the related table. This seems to be the most efficient way to handle one-to-many relationships without having the manual update attributes to make sure the key fields are the same. Is it possible to use your scripting if the key fields are not the same?

I'm working with a very 'dumb' testing point feature class and table so I can get it right before applying to my real work. If my parameters are simply:

Feature Class has Field_A
Table has Field_B
Relationship Class 'Origin Primary Key' (Feature Class) is GlobalID; 'Origin Foreign Key' (Table) is GUID

All I want is for the attribute in Field_A to populate Field_B in the related table. How would this look in your example code?

Terry_Bearb

Sorry for the delay in getting back to you. I am actually using a GUID field type as well in my scenario on my related table. My feature class is utilizing a GlobalID field type and my table is utilizing a GUID field type (field is called 'Related_Asset') within the relationship class.

The association between the two fields happens in these lines where I am specifying the variable globalid as $feature.Related_Asset. The 'Related_Asset' field being the GUID field from the related table. The @globalid in the filter statement is being called upon against the feature class:

var globalid = $feature.Related_Asset
var filterStatement = 'globalid = @globalid'

Rejoindre la conversation

Les membres connectés peuvent publier, suivre les mises à jour, et plus encore. Nouveau ici ? Inscrivez-vous gratuitement.

Liens rapides

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.