topic Re: Arcade - Update Feature Class based on related table in ArcGIS Pro Questions

Arcade - Update Feature Class based on related table

KierenTinning2 — Thu, 23 Jul 2020 20:48:22 GMT

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
}]
}]
}

Re: Arcade - Update Feature Class based on related table

XanderBakker — Thu, 23 Jul 2020 22:29:31 GMT

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.

Re: Arcade - Update Feature Class based on related table

KierenTinning2 — Thu, 23 Jul 2020 23:30:52 GMT

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,
}

Re: Arcade - Update Feature Class based on related table

KierenTinning2 — Fri, 24 Jul 2020 00:14:44 GMT

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
}]

}]

}

Re: Arcade - Update Feature Class based on related table

XanderBakker — Sun, 12 Dec 2021 04:01:05 GMT

Hi Kieren Tinning ,

Would I still use $map in the attribute rule?

No, $map is not available for attribute rules. They are executed at the database level and at that level there is no knowledge about any map. I also recommend using the $datastore in the pop-up to be closer to the actual situation you want to solve.

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?

That changes things a bit. You will have to use a default value for the table record to simulate the process. Instead of using

var locationeguidKey = $feature["locationeguid"];‍

You should assign an existing "loctioneguid" value from your table to ensure that you can simulate the process.

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.

The SQL will not work the way you configured it. Verify if you can use FeatureSetByRelationshipName function and if not, change the sql using the example I provided before. Either use this:

var nonCustomerSql = "premiseguid = @premiseKey";
var premiseSQL = "GlobalID = @premiseKey";‍‍

... or:

var nonCustomerSql = "premiseguid = '{" + Upper(premiseKey) + "}'";
var premiseSQL = "GlobalID = '{" + Upper(premiseKey) + "}'";
‍‍

The following line will not work:

txt = txt + Text(address.building_number, ',');‍

If the building number is numeric then you will have to use the Text function to convert it to text (applying any formatting you want) and concatenate it with the comma.

txt = txt + Text(address.building_number) + ',';‍

The line:

premiseKey.labeltext = txt;‍

... will result in an error, since premiseKey is a GlobalID and does not have a property "labeltext".

Re: Arcade - Update Feature Class based on related table

XanderBakker — Sun, 12 Dec 2021 04:01:07 GMT

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.

Re: Arcade - Update Feature Class based on related table

KierenTinning2 — Fri, 24 Jul 2020 01:30:02 GMT

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
}]

}]

}]

}

Re: Arcade - Update Feature Class based on related table

KierenTinning2 — Fri, 24 Jul 2020 03:17:10 GMT

Xander Bakker‌

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

Re: Arcade - Update Feature Class based on related table

XanderBakker — Sun, 12 Dec 2021 04:01:10 GMT

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?

Re: Arcade - Update Feature Class based on related table

KierenTinning2 — Fri, 24 Jul 2020 13:29:33 GMT

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.

Re: Arcade - Update Feature Class based on related table

XanderBakker — Fri, 24 Jul 2020 14:44:11 GMT

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.

Re: Arcade - Update Feature Class based on related table

KierenTinning2 — Fri, 24 Jul 2020 14:55:33 GMT

Xander Bakker‌ what needs to happen is

Premise has label field - records to label from come from related customer table (1:M relationship class). As the label profile for Arcade doesn't support relationship classes, we roll up the address information from the customer table and write the building numbers to a string which is then written to the label field on the feature class. A complicated label script then runs and voila, all is well.

If we add a related record and then add a building number, the rule runs and rolls everything up. If we modify the customer record then the script runs and updates the label field.

The problem, if I remove a related record from the customer table the script crashes on a keyword error. If I delete a related record in the customer table the script doesn't run, thereby leaving the label field in an incorrect state. If I update an address it's fine.

Key is deleting a customer record (table) doesn't cause the Update to happen.

Re: Arcade - Update Feature Class based on related table

XanderBakker — Fri, 24 Jul 2020 19:27:01 GMT

Hi KierenTinning ,

Thanks for the clarification. Have you looked at "Message notification direction" in Relationship class properties—Relationships and related objects | Documentation ? It may provide what you need by deleting related records when the parent is deleted.

Re: Arcade - Update Feature Class based on related table

KierenTinning2 — Sat, 25 Jul 2020 04:01:57 GMT

Xander Bakker‌ thank you for the idea. In this case though, I need to update a record in the parent when the child is deleted, I am not actually triggering deletes..

Example, ParentID 54 has children A, B and C - each child has an address record. Due to a limitation in the Arcade labeling profile, the script reads the building number from the children and writes the building numbers to the Parent as a comma separated list in a "label" field - which a labeling Arcade script processes.

If we add Child D - it's building number when created triggers the update and the building number is written to Parent 54, so it now has building numbers from child A, B, C and D.

The problem is, if we delete child C - then the Arcade script never runs, so the Parent has building numbers from Child A, B, C and D. If we modify a building number in child A for example, then the script runs and updated the parent removing the building number from child C. So my problem that I am working on, is if Child C is deleted, how do I trigger an update such that the Parent will only have building numbers from A,B and D

Thanks for the help.

Kieren

Re: Arcade - Update Feature Class based on related table

XanderBakker — Sun, 26 Jul 2020 14:58:53 GMT

Hi Kieren Tinning ,

Thanks for the additional explanation. What you want to do is configure an attribute rule on the address table that is triggered by insert, update and delete. This way you can use the new situation to modify the building numbers on the other featureclass.