topic Re: Get 1:M related objects using Arcade in Developers Questions

Get 1:M related objects using Arcade

LindsyHales_Bentley — Sat, 02 Feb 2019 00:44:44 GMT

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.

Re: Get 1:M related objects using Arcade

XanderBakker — Sun, 03 Feb 2019 15:43:44 GMT

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.

Re: Get 1:M related objects using Arcade

LindsyHales_Bentley — Mon, 04 Feb 2019 16:55:00 GMT

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!

Re: Get 1:M related objects using Arcade

XanderBakker — Sun, 12 Dec 2021 07:23:52 GMT

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;
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Get 1:M related objects using Arcade

TheodoreRakel — Tue, 19 Feb 2019 23:40:28 GMT

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?

Re: Get 1:M related objects using Arcade

XanderBakker — Fri, 22 Feb 2019 13:22:43 GMT

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?

Re: Get 1:M related objects using Arcade

TheodoreRakel — Fri, 22 Feb 2019 18:27:39 GMT

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.

Re: Get 1:M related objects using Arcade

deleted-user--MMnVrog9xw_ — Sun, 12 Dec 2021 07:23:55 GMT

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.

Re: Get 1:M related objects using Arcade

XanderBakker — Wed, 10 Apr 2019 21:37:59 GMT

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.

Re: Get 1:M related objects using Arcade

deleted-user--MMnVrog9xw_ — Thu, 11 Apr 2019 12:34:36 GMT

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)

Re: Get 1:M related objects using Arcade

XanderBakker — Thu, 11 Apr 2019 12:45:47 GMT

I don't see anything wrong with the expression, so I think the source data might be the problem, although it is strange that the error points to the $map. What you can do is extract a small part of the data of the service and publish it to ArcGIS Online to test and see if that works (it should). The other aspect is the fact that you are using a map service and not a feature service. Not sure if that could cause the problem.

Re: Get 1:M related objects using Arcade

deleted-user--MMnVrog9xw_ — Thu, 11 Apr 2019 12:53:51 GMT

I just tested with a feature service of the same data from the server and it now accepts it as a valid expression. So looks like map service is not supported. Thanks for your help

Re: Get 1:M related objects using Arcade

deleted-user--MMnVrog9xw_ — Thu, 11 Apr 2019 13:19:30 GMT

EDIT: I removed the other map service that I had in the map and the pop-up is now working! I guess it may have been getting confused with the two different tables even though they had different names

The expression seems to be valid now, when I click test in the expression builder it returns what I would expect. Also when I look at the network traffic through the dev console when clicking on a feature I can see the query going out and returning valid features. However, I can't get anything to actually display in the pop-up. Do you see any reason why this would not work? I also tried the custom attribute display with the field but that didn't work either.

Re: Get 1:M related objects using Arcade

Jefcoat_Nathan — Mon, 07 Oct 2019 18:37:57 GMT

Just want to say thanks for this. Saved me some time!

Re: Get 1:M related objects using Arcade

XanderBakker — Thu, 10 Oct 2019 15:07:46 GMT

Hi Jefcoat, Nathan ,

Glad to hear that this discussion has helped you. Just to add some exiting news (in ArcGIS Online) for getting to related records: the new function "FeatureSetByRelationshipName": https://developers.arcgis.com/arcade/function-reference/data_functions/#featuresetbyrelationshipname which allows for a faster and easier way to access related records.

See also this blog: What’s New in Arcade 1.8

Re: Get 1:M related objects using Arcade

JérémiePedoia — Tue, 12 Nov 2019 05:14:59 GMT

HiXander Bakker‌

Thank you for this really helpfull code.

Is there a way to include a unique url link by asset ?

Is it possible to build a link based on the globalID of each related record in the main feature popup ?

Thanks in advance.

Re: Get 1:M related objects using Arcade

XanderBakker — Fri, 15 Nov 2019 15:01:05 GMT

Hi Jérémie Pedoia ,

This is possible, yes! Is it easy? Not even close...

Let me try and explain why this is the case. With Arcade you cannot construct HTML. Any HTML you construct will be returned and interpreted as text and parts might even be purged from the result. So, with Arcade you could create a list of URLs of all assets, but this will be presented as a text and not clickable URLs. You can use Arcade and create the list of A tags with the links to the assets but this will still be text and does not provide clickable links to the assets.

You can do this, but this would be require a huge amount of separate Arcade expressions inside a custom html pop-up. I really don't recommend you to go there. Hopefully, in the near feature it will be possible to generate HTML with Arcade that is actual usable in a pop-up.

Re: Get 1:M related objects using Arcade

JérémiePedoia — Sun, 17 Nov 2019 21:17:33 GMT

Hi Xander Bakker,

Thank you for your answer.

I can't wait to generate HTML with Arcade.

As I know that there will be max 5-6 related records, I prepared 10 links with display fonctionnality.

I had to make 20 Arcade expression for that but I works and works only because there are only ten.

Thanks again for sharing.

Re: Get 1:M related objects using Arcade

IvanGnevanov — Thu, 04 Jun 2020 23:42:50 GMT

Hi, I have the same issue with Portal for ArcGIS 10.7.1 when trying to add a custom attribute to a layer in a Web Map layer (POL for example). I am not able to reference $map Global variable since Globals only include $feature.* fields. I have no other layers in my map so I can't remove something to check.

When I am trying to setup a popup for separately added related table, it allows to reference all the variables from the example ($map, $layer, $datastore, etc.).

Re: Get 1:M related objects using Arcade

AhmadSALEH1 — Wed, 19 Aug 2020 18:21:41 GMT

Xander Bakker‌ is there a way to change the change the returned sting color ?