Arcade - Update Feature Class based on related table

[KierenTinning2]

All,

I am trying to implement an attribute rule which updates a feature class field when the related 1:M table gets updated.  The process right now is, the field in the related table changes, we get the keys, roll up the data into a string and then get the related record in the feature class and write the text value.

Issue is, a) the for loop is in error and if I comment it our and put a dummy value in my text variable, it doesn't actually write anything.

Any help would be appreciated.- thank you

var tbl = FeatureSetByName($datastore,"DB.LAND.CustomerAddress");
var fc = FeatureSetByName($datastore,"DB.LAND.AddressLocation");
var locationeguidKey = $feature["locationeguid"];
var CustomerSql = "locationeguidKey = '" + locationeguidKey + "'";
var premiseSQL = "GlobalID = '" + locationeguidKey + "'";
var CustomerAddressResult = Filter(tbl, CustomerSql);
var txt = null;

var address;

for (var address in nonCustomerAddressResult){
txt = Text(address.building_number, ',');
}

return {

if (Count(txt) > 0){
txt = Left(txt, Count(txt) - 1);
premiseResult = Filter(fc, premiseSQL)
// premiseKey.labeltext = txt;
}

"result" : txt,

"edit":[{
"className": "premiseResult",
"updates" : [{
"labeltext": txt
}]
}]
}

[XanderBakker]

Hi Kieren Tinning ,

A couple of things to start with:

• If the data is related through a relationshipclass, use the "FeatureSetByRelationshipName" to access any related data.
• If that is not the case, keep in mind that a valid sql expression to filter the data on global id requires son additional formatting. Example: var sql = "parentglobalid = '{" + Upper(code) + "}'";
• However, I think it should be possible to use: var premiseSQL = "GlobalID = @locationeguidKey";
• I would take out the if statement that is within the return statement and place it before the return
• In the for loop you are accessing a variable called "nonCustomerAddressResult". I suppose that should be "CustomerAddressResult"
• In the for loop you overwrite for each address the value you assign to variable txt. If you just need a single adress,just use the First (and check if you have any results).
• Try the expression for instance in a pop-up and return the txt variable to see if it gives any results. If you have the correct value assigned to the variable txt we will see the next step to write the value to the output. 

[KierenTinning2]

Xander,  Thank you ,I hadn't even noticed the logic error.

I've updated the code here - for the popup - would I still use $map in the attribute rule? I wouldn't think so, but I could be wrong.  Also, the $feature here is referencing the point I clicked, however in the attribute rule it's actually reversed as it would be from the table to the feature?

As for the SQL, I am using it as I need to roll up all of the values from the table and then write them to the the feature in a comma separated list.

var tbl = FeatureSetByName($map,"Non Customer Address");
var fc = FeatureSetByName($map,"Premise");
var premiseKey = $feature["GlobalID"];
var nonCustomerSql = "premiseguid = '" + premiseKey + "'";
var premiseSQL = "GlobalID = '" + premiseKey + "'";
var nonCustomerAddressResult = Filter(tbl, nonCustomerSql);
var txt = null;
var premiseResult = Filter(fc, premiseSQL);

for (var address in nonCustomerAddressResult){
txt = txt + Text(address.building_number, ',');
}

If (count(txt) > 0){
premiseResult = Filter(fc, premiseSQL);
premiseKey.labeltext = txt;
}

return {

"result": txt,
}

[KierenTinning2]

Xander Bakker‌, thank you.  I am much further ahead, the only part the script appears to be failing on now is an "failed edit operation, Required keyword not defined in script".  I wonder if it is the 'labeltext' element, which is actually a field that the result needs to be written to

var tbl = FeatureSetByName($datastore,"DB.LAND.NonCustomerAddress");
var fc = FeatureSetByName($datastore,"DB.LAND.Premise");
var premiseKey = $feature["GlobalID"];
var nonCustomerSql = "GlobalID = '" + premiseKey + "'";
var nonCustomerAddressResult = Filter(tbl, nonCustomerSql);

var txt = null;

for (var address in nonCustomerAddressResult){
txt = txt + address.building_number + ',';
premiseKey = address.premiseguid;
}

var premiseSQL = "GlobalID = '" + premiseKey + "'";
var premiseResult = Filter(fc, premiseSQL);

return {

"result" : txt,

"edit" : [{
"className" : "DB.LAND.Premise",
"updates" : [{
"labeltext" : txt
}]

}]

}

[XanderBakker]

Hi KierenTinning ,

For testing in the pop-up I would probably first use something like this:

var tbl = FeatureSetByName($datastore,"DB.LAND.NonCustomerAddress");
var fc = FeatureSetByName($datastore,"DB.LAND.Premise");
var premiseKey = $feature["GlobalID"]; // replace with an existing GlobalID from the table
var nonCustomerSql = "GlobalID = @premiseKey";
Console(nonCustomerSql);
var nonCustomerAddressResult = Filter(tbl, nonCustomerSql);
Console("Count:" + Count(nonCustomerAddressResult));

var txt = "";
if (Count(nonCustomerAddressResult)>0) {
    for (var address in nonCustomerAddressResult){
        txt = txt + address.building_number + ',';
        //premiseKey = address.premiseguid;
        Console(" - building_number:" + address.building_number);
        Console(" - txt:" + txt)
        Console(" - premiseguid:" + address.premiseguid)
    }
}

return txt;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

... and check the messages that are written to the console.

[KierenTinning2]

Xander Bakker‌ thank you very much for all the help.

Here is the update from our conversation.  I feel like I am soo close - Pro, during the edit, is complaining that a required keyword is missing, but near as I can tell I have them in there, I added the "attribute" from what I saw on a different blog post, but that doesn't appear to be it.

var tbl = FeatureSetByName($datastore,"GISLND.LAND.NonCustomerAddress");
var fc = FeatureSetByName($datastore,"GISLND.LAND.Premise");
var premiseKey = $feature["GlobalID"];
var nonCustomerSql = "GlobalID = @premiseKey";
var nonCustomerAddressPremise = Filter(tbl, nonCustomerSql);

for (var address in nonCustomerAddressPremise){
premiseKey = address.premiseguid;
}

var nonCustomerSql = "premiseguid = @premiseKey";
var nonCustomerAddress = Filter(tbl, nonCustomerSql);
var txt = "";

for (var address in nonCustomerAddress){
txt = txt + address.building_number + ',';
premiseKey = address.premiseguid;
}

var premiseSQL = "GlobalID = @premiseKey";
var premiseResult = Filter(fc, premiseSQL);

return {

"result": txt,

"edit":[{
"className": "Premise",
"updates" : [{
"attributes": [{
"labeltext": txt
}]

}]

}]

}

[KierenTinning2]

Xander Bakker‌

Thank you for your help, I have it working now. I had missed the "GlobalID" for the update edit method.

[XanderBakker]

Hi Kieren Tinning ,

Glad to hear that it is working now. If you included the GlobalID in the return object you are probably updating a single feature. In the help there is an example that shows how to update multiple features.

See: Attribute rule script expression examples—Geodatabases | Documentation  (Edit another featureclass). There you will see this example:

var fsAddress = FeatureSetByName($datastore, "Address_pnts", ["globalid"], true)
var fsListAddpnts = Intersects(fsAddress, $feature)
var AddList = []
var counter = 0
var noAddress = Count(fsListAddpnts)
if (noAddress > 0) {
    for (var address in fsListAddpnts) {
        AddList[counter] = {
            'globalid': address.globalid,
            'attributes': {
                'add_district_name': $feature.DistrictName
            }
        }
        counter++
    }
    return {
        'result': noAddress + ' addresses found in the district.',
        'edit': [{
            'className': 'Address_pnts',
            'updates': AddList
        }]
    }
} else {
    return 'No address points in district.'
}‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The key in this example is the AddList, which contains a array of objects that need to be updated. Each element in the array is an object with a globalid property filled with the globalid of the feature in the other featureclass that needs to be updated and an attributes property that contains another object where each attribute name is assigned a value (in this case there is only a single attribute called "add_district_name" that contains the name of the district of the intersecting feature).

Could you post your final expression so that other can see how you solved it?

[KierenTinning2]

I saw that one with the adds - do you know if deleting a record in the related table considered an "Update".

That's my next modification - if I add a row in the related table, then update the address value the system triggers properly and updates the Feature Class with the rolled up items.  When I delete a record, it doesn't trigger the script to roll up the values to update the feature class.

But, so far so good.

[XanderBakker]

Hi KierenTinning ,

I have not seen the possibility to delete records using an attribute rule. Can you elaborate a bit more on what your workflow looks like?

You can use a delete action as trigger for an attribute rule to run. You may also want to explore the deletion semantics, since this looks like a cascading delete that you need.