Attribute Rules for M:N Relationship Class and Updating Relationship Class Intermediary Table

Question

Working on an attribute rule that populates a M:N relationship table between two polygon layers in cases where they intersect.

In this case the intermediary relationship table is RelationshipTable_AB that includes the project_name field from Project A, project_number (PROJA_NUMBER) field from Project A and intersecting project_number field from Project B, as well as Global ID. For some reason the M:N relationship class in EGDBs in Enterprise 10.9.1 is not updated automatically upon creation/update/deletion of intersecting layers and needs to be attributed to update the intermediary relationship class table manually.

Anytime a Project A or Project B polygon is just updated , it seems to generate a double entry in the relationship table, one with the correct values with all fields populated, and one with NULL values for the project_name and project_number (PROJA_NUMBER) from Projects A that is not correct.

Is there logic or a function in Arcade that checks to see if a record exists in a table before inserting/updating the relationship table?

I can't seem to find a function that does so in documentation.

This is a snippet of my attribute rule applied to Projects_A upon insert and update triggers:

var fs = FeatureSetByName($datastore, "Project_B");
var fsInt = intersects(fs, $feature);
var payload = [];
var c = 0;
for (var proj in fsInt)
{
     payload[c++] = {"attributes": { "PROJECT_NAME": $feature.project_name, "PROJA_NUMBER": $feature.proja_project_number, "PROJB_NUMBER": $feature.projb_project_number} }
} 


return {
     "edit": [{"className": "RelationshipTable_AB",
                "adds": payload
              }]
} 

This is a snippet of my attribute rule applied to Projects_B upon insert and update triggers:

var fs = FeatureSetByName($datastore, "Project_A", ["project_number"]);
var fsInt = intersects(fs, $feature);
var payload = [];
var c = 0;

for (var proj in fsInt) 
  {
    payload[c++] = {"attributes": {"PROJECT_NAME": $feature.projectname, "PROJA_NUMBER": proj.proja_project_number,"PROJB_NUMBER":$feature.projb_project_number}}
}
  
return {
    "edit": [{
        "className": "RelationshipTable_AB",
        "adds": payload
    }]
}

Answer 1

On the null entries does script A have a typo in it? $feature.project_name has a hyphen in script A but not B, and script A doesn't fetch projb number from the projb featureset. Should it read:

for (var proj in fsInt) 
{         
    push(payload,{"attributes": { "PROJECT_NAME": $feature.projectname, "PROJA_NUMBER": $feature.proja_project_number, "PROJB_NUMBER": proj.projb_project_number} }
}
    

To ensure the entries are unique, there may be a more appropriate attribute rules based solution, but I think Arcade could implement this as:

    var relationship_fs = featuresetbyname($datastore, "RelationshipTable_AB", ["PROJECT_NAME", "PROJA_NUMBER", "PROJB_NUMBER"])
    function entry_not_present_in_relationship_table(entry)
    {
      var attrs = entry.attributes;
      return count(filter(relationship_fs, `PROJECT_NAME= "${attrs.PROJECT_NAME}" AND PROJA_NUMBER= "${attrs.PROJA_NUMBER}" AND PROJB_NUMBER= "${attrs.PROJB_NUMBER}"`) == 0
    }
    payload = filter(payload, entry_not_present_in_relationship_table)
    

Which all together would make script B:

var fs = FeatureSetByName($datastore, "Project_A", ["project_number"]);
    var fsInt = intersects(fs, $feature);
    var payload = [];
    
    for (var proj in fsInt) 
    {
        push(payload, {"attributes": {"PROJECT_NAME": $feature.projectname, "PROJA_NUMBER": proj.proja_project_number,"PROJB_NUMBER":$feature.projb_project_number}})
    }
    
    var relationship_fs = featuresetbyname($datastore, "RelationshipTable_AB", ["PROJECT_NAME", "PROJA_NUMBER", "PROJB_NUMBER"])
    function entry_not_present_in_relationship_table(entry)
    {
      var attrs = entry.attributes;
      return count(filter(relationship_fs, `PROJECT_NAME= "${attrs.PROJECT_NAME}" AND PROJA_NUMBER= "${attrs.PROJA_NUMBER}" AND PROJB_NUMBER= "${attrs.PROJB_NUMBER}"`) == 0
    }
    payload = filter(proj, entry_not_present_in_relationship_table)
    
    return {
        "edit": [{
            "className": "RelationshipTable_AB",
            "adds": payload
        }]
    }