nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Najděte více příspěvků označených jako

Accepted answers

All comments

Jefcoat_Nathan

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

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:

<SPAN class="keyword token">var</SPAN> pin <SPAN class="operator token">=</SPAN> $feature<SPAN class="punctuation token">.</SPAN>Name<SPAN class="punctuation token">;</SPAN>

<SPAN class="keyword token">var</SPAN> sql <SPAN class="operator token">=</SPAN> <SPAN class="string token">"PIN = '"</SPAN> <SPAN class="operator token">+</SPAN> pin <SPAN class="operator token">+</SPAN> <SPAN class="string token">"'"</SPAN><SPAN class="punctuation token">;</SPAN>

<SPAN class="keyword token">var</SPAN> ownerTable <SPAN class="operator token">=</SPAN> <SPAN class="token function">FeatureSetByName</SPAN><SPAN class="punctuation token">(</SPAN>$map<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"OwnershipInformation"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>

<SPAN class="keyword token">var</SPAN> owners <SPAN class="operator token">=</SPAN> <SPAN class="token function">Filter</SPAN><SPAN class="punctuation token">(</SPAN>ownerTable<SPAN class="punctuation token">,</SPAN> sql<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>

<SPAN class="keyword token">var</SPAN> cnt <SPAN class="operator token">=</SPAN> <SPAN class="token function">Count</SPAN><SPAN class="punctuation token">(</SPAN>owners<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>

<SPAN class="keyword token">var</SPAN> owner_info <SPAN class="operator token">=</SPAN> <SPAN class="string token">""</SPAN><SPAN class="punctuation token">;</SPAN>

<SPAN class="keyword token">if</SPAN> <SPAN class="punctuation token">(</SPAN>cnt <SPAN class="operator token">></SPAN> <SPAN class="number token">0</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">{</SPAN>
    owner_info <SPAN class="operator token">=</SPAN> cnt <SPAN class="operator token">+</SPAN> <SPAN class="string token">"Owner(s):"</SPAN><SPAN class="punctuation token">;</SPAN>
    <SPAN class="comment token">//loop through related records</SPAN>
    <SPAN class="keyword token">for</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">var</SPAN> owner <SPAN class="keyword token">in</SPAN> owners<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">{</SPAN>
        <SPAN class="keyword token">var</SPAN> owner_text <SPAN class="operator token">=</SPAN> <SPAN class="string token">" - "</SPAN> <SPAN class="operator token">+</SPAN> owner<SPAN class="punctuation token">.</SPAN>FULLNAME <SPAN class="operator token">+</SPAN> TextFormatting<SPAN class="punctuation token">.</SPAN>NewLine <SPAN class="operator token">+</SPAN> owner<SPAN class="punctuation token">.</SPAN>MAILINGADDRESS<SPAN class="punctuation token">;</SPAN>
        
        owner_info <SPAN class="operator token">+</SPAN><SPAN class="operator token">=</SPAN> TextFormatting<SPAN class="punctuation token">.</SPAN>NewLine <SPAN class="operator token">+</SPAN> owner_text<SPAN class="punctuation token">;</SPAN>
    <SPAN class="punctuation token">}</SPAN>
<SPAN class="punctuation token">}</SPAN>
<SPAN class="keyword token">else</SPAN><SPAN class="punctuation token">{</SPAN>
    owner_info <SPAN class="operator token">=</SPAN> <SPAN class="string token">"No Owner Found"</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="punctuation token">}</SPAN>

<SPAN class="keyword token">return</SPAN> owner_info‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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

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

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

<SPAN class="comment token">// read the GlobalID of the feature</SPAN>
<SPAN class="keyword token">var</SPAN> globalid <SPAN class="operator token">=</SPAN> $feature<SPAN class="punctuation token">.</SPAN>GlobalID<SPAN class="punctuation token">;</SPAN>
<SPAN class="comment token">// create a sql expression to filter the asset table</SPAN>
<SPAN class="keyword token">var</SPAN> sql <SPAN class="operator token">=</SPAN> <SPAN class="string token">"gis_id = '"</SPAN> <SPAN class="operator token">+</SPAN> globalid <SPAN class="operator token">+</SPAN> <SPAN class="string token">"'"</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="comment token">// Access the asset view based on the name in the map</SPAN>
<SPAN class="keyword token">var</SPAN> asset_view <SPAN class="operator token">=</SPAN> <SPAN class="token function">FeatureSetByName</SPAN><SPAN class="punctuation token">(</SPAN>$map<SPAN class="punctuation token">,</SPAN><SPAN class="string token">"mv_masterreference_assets"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="comment token">// filter the assets using the sql expression</SPAN>
<SPAN class="keyword token">var</SPAN> assets <SPAN class="operator token">=</SPAN> <SPAN class="token function">Filter</SPAN><SPAN class="punctuation token">(</SPAN>asset_view<SPAN class="punctuation token">,</SPAN> sql<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>

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

<SPAN class="keyword token">return</SPAN> asset_info<SPAN class="punctuation token">;</SPAN>
<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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 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.

Připojte se k diskusi

Přihlášení členové mohou přispívat, sledovat aktualizace a další. Jste tu noví? Zaregistrujte si bezplatný účet.

Rychlé odkazy

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.