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

[Boomer1187]

Hi folks,

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

In this case the intermediary relationship table is RelationshipTable_AB. 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 created, it seems to generate a double entry in the relationship table - with one entry containing all of the populated fields in the intermediary table, and the second one having some null values. Rows 1-2 are generated when a new Project_A polygon is created, whereas Rows 3-4 are created when a new Project_B polygon is created. I only want row 1 to remain, and row 3 to remain.

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? Would an if/else statement be the way to go here?

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_proj_number, "PROJB_NUMBER": $feature.projb_proj_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", ["proja_proj_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_proj_number,"PROJB_NUMBER":$feature.projb_proj_number}}
}
  
return {
    "edit": [{
        "className": "RelationshipTable_AB",
        "adds": payload
    }]
}

I've been trying my hand with the below scripts, each to no avail 😞

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

for (var proj in fsInt) {
    var projb_proj_num = $feature.projb_proj_number;
    var proja_proj_num = proj.proja_proj_number;

    // Check if projb_proj_number already exists in the payload
    if (!payload[projb_proj_number]) {   
        payload[projb_proj_number] = {
            "PROJECT_NAME": $feature.projectname,
            "PROJA_NUMBER": proja_proj_number,
            "PROJB_NUMBER": projb_proj_number
        };
        uniqueEntries.push(payload[projb_proj_number]);
    }
}

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

and

// Query to retrieve existing records from RelationshipTable_AB table
var existingRecords = FeatureSetByName($datastore, " RelationshipTable_AB", ["project_name"]);
var fs = FeatureSetByName($datastore, "Project_A", ["proja_proj_number"]);
var fsInt = intersects(fs, $feature);
var payload = [];
var c = 0;

for (var proj in fsInt) {
    // Check if the project_name attribute is populated in existing records
    var projectNamePopulated = existingRecords.project_name != null && record.project_name != '’;

    if (projectNamePopulated) {
        payload[c++] = {
            "attributes": {
                "PROJECT_NAME": $feature.project_name,
                "PROJA_PROJ_NUMBER": proj.proja_proj_number,
                "PROJB_PROJ_NUMBER": $feature.projb_proj_number
            }
        };
    } else {
        // If project_name is not populated in existing records, do not insert the record
        console.log("project_name attribute is not populated in existing records. Skipping record insertion.");
    }
}

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

Any help would be very much appreciated, thanks so much.