Get 1:M related objects using Arcade

[LindsyHales_Bentley]

Branched from: https://community.esri.com/thread/196867-get-related-objects-in-arcade 

I have a pipeline fc with geometry(fittings are drawn to scale as a polygon) with diameter, and globalid fields. 

And then a view(table) containing asset name, attribute value, and a GISid.

This is a one-to-many relationship (GISid = globalid) because a section of pipe could have multiple materials (inner, outer, encasement, coatings, etc.).

I want to show the related information in the pop-up of the pipeline.

[XanderBakker]

Hi Lindsy Hales Bentley ,

I branched the question into a new thread to avoid any confusion with the question asked by the OP. 

I will describe the steps below, and if you can provide me with the actual field names and name of the table in the map, I can help you create the Arcade expression:

• In your case you will have a globalid that you can access from the feature.
• You can use the FeatureSetByName function to access the table
• This globalid can be used to filter the table on GISid and only end up with the related records
• Then you can iterate through the related records and construct the (multiline) string that you want to show in your pop-up

BTW: if you can provide me access to the actual data, I will be able to test the Arcade expression. To do this you could invite my ArcGIS Online account (xbakker@esri.co_utility_esri_co) into a group where you share the data.

[LindsyHales_Bentley]

Thanks!

Unfortunately the data is protected behind a firewall, so I am unable to share it with you.

Here is a breakdown of the data:

Pipeline (polyline)

   Diameter_Inches

   globalid

mv_masterreference_assets (view)

   gis_id (same as globalid)

   asset_id

   name

   description

   ......etc

Thanks for your help!

[XanderBakker]

Hi lriddle8 , 

I'm thinking of something along the lines like this (see comments in code):

// read the GlobalID of the feature
var globalid = $feature.GlobalID;
// create a sql expression to filter the asset table
var sql = "gis_id = '" + globalid + "'";
// Access the asset view based on the name in the map
var asset_view = FeatureSetByName($map,"mv_masterreference_assets");
// filter the assets using the sql expression
var assets = Filter(asset_view, sql);

// get the count of the related assets
var cnt = Count(assets);
// create a variable to hold the resulting text
var asset_info = "";
// validate if there are any related records and handle accordingly
if (cnt > 0) {
    // there is at least 1 related record, create asset info
    asset_info = cnt + " Asset(s):";
    // loop through related records
    for (var asset in assets) {
        // create text using name and description (you should modify this to match your needs)
        var asset_text = " - " + asset.name + TextFormatting.NewLine + asset.description;
        // add the text for this asset to the asset info
        asset_info += TextFormatting.NewLine + asset_text;
    }
} else {
    // no related records (or the sql query did not work)
    asset_info = "No related assets";
}

return asset_info;
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[TheodoreRakel]

I've been trying to use this approach to get the count of related objects in an attribute rule calculation.  I'm trying to count the number of objects related to a feature which is from a feature service, and set the count on an attribute of the feature called 'RelCount'.  I use the $datastore instead of $map above.  Here's the arcade for the rule titled 'setRelCnt':

var id = $feature.GlobalID;
var sql = "Parent_GlobalID = '" + id + "'";
var asset_view = FeatureSetByName($datastore,"GISP.PE_GIS.SimpleUnits");
var assets = Filter(asset_view, sql);
var cnt = Count(assets);
return cnt

Here's how I defined it in Pro:

I assign the Rule and share the map to create the feature service.  When I edit a feature in Pro to trigger the rule, I get this error that the table "GISP.PE_GIS.SimpleUnits" is not found.

  

However, you can see the table from Portal:

Is there an easier way to get the number of related records to a feature using Arcade?  Should I be  able to get the table "GISP.PE_GIS.SimpleUnits" and count the related records when updating a feature from a branch versioned feature service?

[XanderBakker]

Hi Theodore Rakel ,

I haven't tried this in an Attribute Rule on a branched versioned database. Are you using the Utility Network? What version of Pro and Enterprise are you using? 

[TheodoreRakel]

I'm not using the Utility Network at this point, but at some point the customer will want a Rule like the one I wrote to calculate the related objects to features that are in the UN.  I'm using Pro 2.3 and Enterprise 10.6.1.  I created a support case with esri about this yesterday and they are helping me with it.  I'll post any results from that support case here.  Thanks.

[deleted-user--MMnVrog9xw_]

Xander, I'm trying to adapt this code for use in a web map but I keep getting the following error in the Arcade Window:

Execution Error:Runtime Error: Identifier Not Found. $map

Here is my Arcade expression:

var pin = $feature.Name;

var sql = "PIN = '" + pin + "'";

var ownerTable = FeatureSetByName($map, "OwnershipInformation");

var owners = Filter(ownerTable, sql);

var cnt = Count(owners);

var owner_info = "";

if (cnt > 0){
    owner_info = cnt + "Owner(s):";
    //loop through related records
    for (var owner in owners){
        var owner_text = " - " + owner.FULLNAME + TextFormatting.NewLine + owner.MAILINGADDRESS;
        
        owner_info += TextFormatting.NewLine + owner_text;
    }
}
else{
    owner_info = "No Owner Found";
}

return owner_info‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The layer that I am trying to access through the expression is a related table within the map service.

[XanderBakker]

Where are you trying to execute this expression? In case you use this for symbology, it will not work since the FeatureSetBy functions are not available there. For field calculations you shoud use the datastore to access the related layer.

[deleted-user--MMnVrog9xw_]

This is in a pop-up on a feature layer in an ArcGIS Online web map. Does this only work on hosted feature services? The feature layer I am trying to use it on is from a MapServer on ArcGIS Server (10.31)