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

How do you reference another field from a related table in Arcade?

deleted-user-TRj-mR_qEmwV

Through a Web Map in ArcGIS Online, I'm creating a field in Feature #1 and Feature #1 is related to Feature #2. Is there a way to get the specific values in Feature #2 that are related via for an Arcade expression in Feature #1?

It's a 1 to many relate and related through a Text ID Field that they both have.
EDIT: I think it has something to do with FeatureSetBy functions

Find more posts tagged with

Accepted answers

All comments

XanderBakker

Hi Scott Tram ,

Please find below a sample of how you can do this. It really depends a lot on what you want to do with the related results and how your data is structured:

<SPAN class="comment token">// first read out the ID of the Feature1</SPAN>
<SPAN class="keyword token">var</SPAN> id <SPAN class="operator token">=</SPAN> $feature<SPAN class="punctuation token">.</SPAN>ID<SPAN class="punctuation token">;</SPAN>
<SPAN class="comment token">// access the table Feature2</SPAN>
<SPAN class="keyword token">var</SPAN> tbl <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">'Feature2'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="comment token">// create a sql expression to query on ID</SPAN>
<SPAN class="keyword token">var</SPAN> sql <SPAN class="operator token">=</SPAN> <SPAN class="string token">"ID = '"</SPAN> <SPAN class="operator token">+</SPAN> ID <SPAN class="operator token">+</SPAN> <SPAN class="string token">"'"</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="comment token">// filter the table using the sql expression</SPAN>
<SPAN class="keyword token">var</SPAN> related_data <SPAN class="operator token">=</SPAN> <SPAN class="token function">Filter</SPAN><SPAN class="punctuation token">(</SPAN>tbl<SPAN class="punctuation token">,</SPAN> sql<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="comment token">// count the resulting records</SPAN>
<SPAN class="keyword token">var</SPAN> cnt <SPAN class="operator token">=</SPAN> <SPAN class="token function">Count</SPAN><SPAN class="punctuation token">(</SPAN>related_data<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>

<SPAN class="comment token">// initiate a variable to hold the result</SPAN>
<SPAN class="keyword token">var</SPAN> result <SPAN class="operator token">=</SPAN> cnt <SPAN class="operator token">+</SPAN> <SPAN class="string token">" related records"</SPAN><SPAN class="punctuation token">;</SPAN>

<SPAN class="comment token">// check if there are related records found for the current ID</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">// loop through related records</SPAN>
    <SPAN class="keyword token">for</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">var</SPAN> row <SPAN class="keyword token">in</SPAN> related_data<SPAN class="punctuation token">)</SPAN> <SPAN class="punctuation token">{</SPAN>
        <SPAN class="comment token">// read some data and create the line you want</SPAN>
        <SPAN class="keyword token">var</SPAN> line <SPAN class="operator token">=</SPAN> TextFormatting<SPAN class="punctuation token">.</SPAN>NewLine <SPAN class="operator token">+</SPAN> <SPAN class="string token">" - "</SPAN> <SPAN class="operator token">+</SPAN> row<SPAN class="punctuation token">.</SPAN>SomeFieldOfInterest<SPAN class="punctuation token">;</SPAN>
        <SPAN class="comment token">// add the line to the result</SPAN>
        result <SPAN class="operator token">+</SPAN><SPAN class="operator token">=</SPAN> line<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>
    result <SPAN class="operator token">=</SPAN> <SPAN class="string token">"No related records:"</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="punctuation token">}</SPAN>

<SPAN class="comment token">// return the result</SPAN>
<SPAN class="keyword token">return</SPAN> result<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>

Some more examples and explanation can be found here:

deleted-user-TRj-mR_qEmwV

Hey Xander,

Thanks again for the reply. I'm a bit confused at the line:

<SPAN class="" style="color: #0077aa; border: 0px; font-weight: inherit;">var</SPAN> cnt <SPAN class="" style="color: #a67f59; background: rgba(255, 255, 255, 0.5); border: 0px; font-weight: inherit;">=</SPAN> <SPAN class="" style="color: #d74444; border: 0px; font-weight: inherit;">Count</SPAN><SPAN class="" style="color: #999999; border: 0px; font-weight: inherit;">(</SPAN>related_data<SPAN class="" style="color: #999999; border: 0px; font-weight: inherit;">)</SPAN><SPAN class="" style="color: #999999; border: 0px; font-weight: inherit;">;</SPAN>

it seems to generate an error message.

My Feature1 is a point layer and it has an attribute table that is related to Feature2 which is just a CSV layer/table. They are related with a 1:many relationship through having a ID_TEXT field. Do the two features have to have the same Name/Alias for the related field? I'm using ID_TEXT for feature 1 and TEXT_ID for feature 2. Thanks again.

XanderBakker

I guess the error is probably generated by the sql expression. It should have the correct name of the field. Did you change the field name ID in the code to ID_TEXT?

deleted-user-TRj-mR_qEmwV

Thanks Xander, I figured it out. I think what happened was one field was indexed while the other field wasn't indexed(*).

KenMorefield

Hi Xander,

I'm wondering if your suggested Arcade solution could be expanded to include 3 relationships deep? Your example shows a "Parent - Child" relationship. I'm wondering if Arcade can handle a "Grandparent - Parent - Child" relationship? Any idea on how to code that? My example consists of a Site Inpection (Grandparent), where multiple instances of Pollution can be found (Parent), and for each Pollution instance - multiple Violations can occur. Site Inspection>Pollution>Violation

Any ideas? I'm working on modifying your code provided above, but I'm not quite getting there!

XanderBakker

Hi Ken Morefield ,

This is possible is a similar way. You will have a Site Inspection that will link to multiple Pollutions and for each pollution you will possibly have a number of violations. This will need to nest another loop and filter each set of violations for each pollution that you have. It is possible, but depending the performance of these multiple queries you will know if this is a good thing to do.

ChelseyAiton2

Hi Xander,

I am trying to show attributes on a point feature service pop-up from a table using a primary and foreign key, 1-1 relationship. I have followed your example above and get an error when I try to use Filter the way you have shown. The data is hosted on ArcGIS Online as a Feature Layer and a Table. Any thoughts about what the issue could be? I am new to arcade so any help would be appreciated.

XanderBakker

That is a strange error message. You are using the Filter function and provide a FeatureSet and a string representing the SQL query. Since you write the sql to the Console, can you have a look at the message written and share it here? If possible, access to the data would help a lot.

ChelseyAiton2

I require special permission to share the whole dataset, so I have provided a trimmed down version of the data. For Ms_points2, I have removed all but 5 features, the schema remains the same. For the table, I have kept the same 5 related records and removed the majority of the fields. A little more background, appraisers in the field will be using an app to edit the location of the points. The table service will be overwritten weekly from an export from the assessor's office database. Therefore, the point layer is editable and the table is not.

It feels like the expression isn't actually connecting to the table service.

Removed links to test data: Deleted feature services

XanderBakker

Hi Chelsey Aiton ,

Not sure why it is not working for you. It seems to be working with the sample data you shared:

Arcade expression used:

<SPAN class="keyword token">var</SPAN> tbl <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">"MS_table2 - ms_table2"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="token function">Console</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="token function">Count</SPAN><SPAN class="punctuation token">(</SPAN>tbl<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">var</SPAN> pt <SPAN class="operator token">=</SPAN> $feature<SPAN class="punctuation token">.</SPAN>TAXACCT<SPAN class="punctuation token">;</SPAN>
<SPAN class="token function">Console</SPAN><SPAN class="punctuation token">(</SPAN>pt<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">var</SPAN> sql <SPAN class="operator token">=</SPAN> <SPAN class="string token">"TAXACCT = '"</SPAN> <SPAN class="operator token">+</SPAN> pt <SPAN class="operator token">+</SPAN> <SPAN class="string token">"'"</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="token function">Console</SPAN><SPAN class="punctuation token">(</SPAN>sql<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">var</SPAN> msaccts <SPAN class="operator token">=</SPAN> <SPAN class="token function">Filter</SPAN><SPAN class="punctuation token">(</SPAN>tbl<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>msaccts<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>

<SPAN class="keyword token">var</SPAN> result <SPAN class="operator token">=</SPAN> <SPAN class="string token">"There are no related records..."</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>
    result <SPAN class="operator token">=</SPAN> <SPAN class="string token">"Related records: "</SPAN> <SPAN class="operator token">+</SPAN> cnt<SPAN class="punctuation token">;</SPAN>
    <SPAN class="keyword token">for</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">var</SPAN> msacct <SPAN class="keyword token">in</SPAN> msaccts<SPAN class="punctuation token">)</SPAN> <SPAN class="punctuation token">{</SPAN>
        result <SPAN class="operator token">+</SPAN><SPAN class="operator token">=</SPAN> TextFormatting<SPAN class="punctuation token">.</SPAN>NewLine <SPAN class="operator token">+</SPAN> <SPAN class="string token">" - "</SPAN> <SPAN class="operator token">+</SPAN> msacct<SPAN class="punctuation token">.</SPAN>PARK <SPAN class="operator token">+</SPAN> <SPAN class="string token">" ("</SPAN> <SPAN class="operator token">+</SPAN> msacct<SPAN class="punctuation token">.</SPAN>PARKNUM <SPAN class="operator token">+</SPAN> <SPAN class="string token">")"</SPAN><SPAN class="punctuation token">;</SPAN> 
    <SPAN class="punctuation token">}</SPAN>
<SPAN class="punctuation token">}</SPAN>

<SPAN class="keyword token">return</SPAN> result<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>

joerodmey

Hi Xander Bakker‌,

I'm looking to do something similar with Arcade. I have a feature service with related table. Within the related table I want to pull data from one field containing quantity and I want to multiply this by 2.50 as this is the price. The result would the value I want to use in my popup. How would I accomplish this?

Thanks

XanderBakker

Hi joe rodmey ,

If the value you want to extract from the related table is a single value or the sum of multiple values, this result can be used to multiply by 2.50 before showing it in the pop-up. You would basically use the same structure as explained above, however, you would not concatenate a text, but you would have a single value or the sum of multiple values extracted from different records. If you can explain a little more about what you have so far and how the structure of your data is, I can help you configure the Arcade expression.

joerodmey

Xander Bakker This I what I have so far

<SPAN class="comment token">// first read out the ID of the Feature1</SPAN>
var id <SPAN class="operator token">=</SPAN> $feature<SPAN class="punctuation token">.</SPAN>objectid<SPAN class="punctuation token">;</SPAN>
<SPAN class="comment token">// access the table Feature2</SPAN>
var tbl <SPAN class="operator token">=</SPAN> <SPAN class="token function">FeatureSetByName</SPAN><SPAN class="punctuation token">(</SPAN>$datastore<SPAN class="punctuation token">,</SPAN><SPAN class="string token">"Work"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="comment token">// create a sql expression to query on ID</SPAN>
var sql <SPAN class="operator token">=</SPAN> <SPAN class="string token">"ID = '"</SPAN> <SPAN class="operator token">+</SPAN> ID <SPAN class="operator token">+</SPAN> <SPAN class="string token">"'"</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="comment token">// filter the table using the sql expression</SPAN>
var related_data <SPAN class="operator token">=</SPAN> <SPAN class="token function">Filter</SPAN><SPAN class="punctuation token">(</SPAN>tbl<SPAN class="punctuation token">,</SPAN> sql<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="comment token">// count the resulting records</SPAN>
var cnt <SPAN class="operator token">=</SPAN> <SPAN class="token function">Count</SPAN><SPAN class="punctuation token">(</SPAN>related_data<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>

<SPAN class="comment token">// initiate a variable to hold the result</SPAN>
var result <SPAN class="operator token">=</SPAN> cnt <SPAN class="operator token">+</SPAN> <SPAN class="string token">" related records"</SPAN><SPAN class="punctuation token">;</SPAN>

<SPAN class="comment token">// check if there are related records found for the current ID</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">// loop through related records</SPAN>
    <SPAN class="keyword token">for</SPAN> <SPAN class="punctuation token">(</SPAN>var row in related_data<SPAN class="punctuation token">)</SPAN> <SPAN class="punctuation token">{</SPAN>
        <SPAN class="comment token">// read some data and create the line you want</SPAN>
        var line <SPAN class="operator token">=</SPAN> TextFormatting<SPAN class="punctuation token">.</SPAN>NewLine <SPAN class="operator token">+</SPAN> <SPAN class="string token">" - "</SPAN> <SPAN class="operator token">+</SPAN> row<SPAN class="punctuation token">.</SPAN>Item<SPAN class="punctuation token">;</SPAN>
        <SPAN class="comment token">// add the line to the result</SPAN>
        result <SPAN class="operator token">+=</SPAN> line<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>
    result <SPAN class="operator token">=</SPAN> <SPAN class="string token">"No related records:"</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="punctuation token">}</SPAN>

<SPAN class="comment token">// return the result</SPAN>
<SPAN class="keyword token">return</SPAN> result<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>

Some info on my data:

-Related table is called "Work"

-Within related table I want to pull the data in field "Item"

-From the "Item" field I want to take that and multiply it by 2.5

-Need to do this for every point i have in my feature service and show it as a new field in the popup

-Will then take that value and pass it into Survey123 using the custom attribute disapy and the custom URL to send to Survey123

-Not common to have multiple related records on one feature service point but it can happen sometimes

XanderBakker

Hi joe rodmey

You can try something like this:

<SPAN class="comment token">// first read out the ID of the Feature1</SPAN>
<SPAN class="keyword token">var</SPAN> id <SPAN class="operator token">=</SPAN> $feature<SPAN class="punctuation token">.</SPAN>objectid<SPAN class="punctuation token">;</SPAN>
<SPAN class="comment token">// access the table Feature2</SPAN>
<SPAN class="keyword token">var</SPAN> tbl <SPAN class="operator token">=</SPAN> <SPAN class="token function">FeatureSetByName</SPAN><SPAN class="punctuation token">(</SPAN>$datastore<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"Work"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="comment token">// create a sql expression to query on ID</SPAN>
<SPAN class="keyword token">var</SPAN> sql <SPAN class="operator token">=</SPAN> <SPAN class="string token">"ID = '"</SPAN> <SPAN class="operator token">+</SPAN> ID <SPAN class="operator token">+</SPAN> <SPAN class="string token">"'"</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="comment token">// filter the table using the sql expression</SPAN>
<SPAN class="keyword token">var</SPAN> related_data <SPAN class="operator token">=</SPAN> <SPAN class="token function">Filter</SPAN><SPAN class="punctuation token">(</SPAN>tbl<SPAN class="punctuation token">,</SPAN> sql<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="comment token">// count the resulting records</SPAN>
<SPAN class="keyword token">var</SPAN> cnt <SPAN class="operator token">=</SPAN> <SPAN class="token function">Count</SPAN><SPAN class="punctuation token">(</SPAN>related_data<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>

<SPAN class="comment token">// initiate a variable to hold the result</SPAN>
<SPAN class="keyword token">var</SPAN> result <SPAN class="operator token">=</SPAN> <SPAN class="number token">0</SPAN><SPAN class="punctuation token">;</SPAN>

<SPAN class="comment token">// check if there are related records found for the current ID</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">// loop through related records</SPAN>
    <SPAN class="keyword token">for</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">var</SPAN> row <SPAN class="keyword token">in</SPAN> related_data<SPAN class="punctuation token">)</SPAN> <SPAN class="punctuation token">{</SPAN>
        <SPAN class="comment token">// add the value  to the result</SPAN>
        result <SPAN class="operator token">+</SPAN><SPAN class="operator token">=</SPAN> row<SPAN class="punctuation token">.</SPAN>item<SPAN class="punctuation token">;</SPAN> 
    <SPAN class="punctuation token">}</SPAN>
<SPAN class="punctuation token">}</SPAN>

<SPAN class="comment token">// return the result</SPAN>
<SPAN class="keyword token">return</SPAN> result <SPAN class="operator token">*</SPAN> <SPAN class="number token">2.5</SPAN><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>

MikeKrois

HI Xander,

I've used simple Arcade Expressions in the past but my knowledge isn't that deep. So hoping you can help. Much like everyone here I have a feature layer with a related table (1:M) and I simply would like to pass along a field from the feature layer to the table. I have a layer called Culverts with a GlobalID and a table called Culvert_Inspection with a GUID. In this example how can I pass along the ObjectID from the culverts to the culvert inspection table?

Thanks for your help.

Mike

Anonymous User

Hi Xander Bakker‌!

I've been trying to achieve the very similar results as the others above, but keep getting hit with the error "Execution Error:Cannot read property 'parse' of undefined" that seems to occur at the Filter function. Here is the first part of the code that I have created:

<SPAN class="comment token">//Read out the GlobalID of the seawall feature layer</SPAN>
<SPAN class="keyword token">var</SPAN> id <SPAN class="operator token">=</SPAN> $feature<SPAN class="punctuation token">[</SPAN><SPAN class="string token">"globalid"</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">;</SPAN>

<SPAN class="comment token">//Create a sql expression to query on GlobalID</SPAN>
<SPAN class="keyword token">var</SPAN> sql <SPAN class="operator token">=</SPAN> <SPAN class="string token">"wall_id = '"</SPAN> <SPAN class="operator token">+</SPAN> <SPAN class="token function">Upper</SPAN><SPAN class="punctuation token">(</SPAN>id<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">+</SPAN> <SPAN class="string token">"'"</SPAN><SPAN class="punctuation token">;</SPAN>

<SPAN class="comment token">//Get related table</SPAN>
<SPAN class="keyword token">var</SPAN> tbl <SPAN class="operator token">=</SPAN> <SPAN class="token function">FeatureSetByName</SPAN><SPAN class="punctuation token">(</SPAN>$datastore<SPAN class="punctuation token">,</SPAN><SPAN class="string token">"Seawall Inspections"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>

<SPAN class="comment token">//Filter the table using the sql expression</SPAN>
<SPAN class="keyword token">var</SPAN> related_data <SPAN class="operator token">=</SPAN> <SPAN class="token function">Filter</SPAN><SPAN class="punctuation token">(</SPAN>tbl<SPAN class="punctuation token">,</SPAN> sql<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>

<SPAN class="comment token">//check filter</SPAN>
<SPAN class="keyword token">return</SPAN> related_data‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<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>

Once I check to see if the filter works, that's when the parsing error comes back... I also tried changing the sql variable but that didn't change the error.

<SPAN class="comment token">//Create a sql expression to query on GlobalID</SPAN>
<SPAN class="keyword token">var</SPAN> sql <SPAN class="operator token">=</SPAN> <SPAN class="string token">"wall_id ="</SPAN> <SPAN class="operator token">+</SPAN> id <SPAN class="operator token">+</SPAN> <SPAN class="string token">""</SPAN><SPAN class="punctuation token">;</SPAN>‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

The data that I'm using is a Portal-hosted feature service containing a seawall feature layer and related inspection table. The relationship is 1:Many and the globalid field of the feature layer and wall_id field of the related table tie them together. At the end of the day, what I'm trying to do is pull the most recent inspection record date from the related table into the popup for the feature layer.

This is what I imagine the final script will look like:

<SPAN class="comment token">//Read out the GlobalID of the seawall feature layer</SPAN>
<SPAN class="keyword token">var</SPAN> id <SPAN class="operator token">=</SPAN> $feature<SPAN class="punctuation token">[</SPAN><SPAN class="string token">"globalid"</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">;</SPAN>

<SPAN class="comment token">//Create a sql expression to query on GlobalID</SPAN>
<SPAN class="keyword token">var</SPAN> sql <SPAN class="operator token">=</SPAN> <SPAN class="string token">"wall_id = '"</SPAN> <SPAN class="operator token">+</SPAN> <SPAN class="token function">Upper</SPAN><SPAN class="punctuation token">(</SPAN>id<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">+</SPAN> <SPAN class="string token">"'"</SPAN><SPAN class="punctuation token">;</SPAN>

<SPAN class="comment token">//Get related table</SPAN>
<SPAN class="keyword token">var</SPAN> tbl <SPAN class="operator token">=</SPAN> <SPAN class="token function">FeatureSetByName</SPAN><SPAN class="punctuation token">(</SPAN>$datastore<SPAN class="punctuation token">,</SPAN><SPAN class="string token">"Seawall Inspections"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>

<SPAN class="comment token">//Filter the table using the sql expression</SPAN>
<SPAN class="keyword token">var</SPAN> related_data <SPAN class="operator token">=</SPAN> <SPAN class="token function">Filter</SPAN><SPAN class="punctuation token">(</SPAN>tbl<SPAN class="punctuation token">,</SPAN> sql<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
<SPAN class="comment token">//Count the resulting records</SPAN>
<SPAN class="keyword token">var</SPAN> cnt <SPAN class="operator token">=</SPAN> <SPAN class="token function">Count</SPAN><SPAN class="punctuation token">(</SPAN>related_data<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>

<SPAN class="comment token">//Initiate a variable to hold the result</SPAN>
<SPAN class="keyword token">var</SPAN> result <SPAN class="operator token">=</SPAN> <SPAN class="string token">"No inspections available."</SPAN><SPAN class="punctuation token">;</SPAN>

<SPAN class="comment token">//Check if there are related records found for the current GlobalID</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>
	result <SPAN class="operator token">=</SPAN> <SPAN class="string token">"Inspections: "</SPAN> <SPAN class="operator token">+</SPAN> cnt<SPAN class="punctuation token">;</SPAN>
	<SPAN class="comment token">//Get most recent inspection</SPAN>
	<SPAN class="keyword token">var</SPAN> max_date <SPAN class="operator token">=</SPAN> <SPAN class="token function">Top</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="token function">OrderBy</SPAN><SPAN class="punctuation token">(</SPAN>related_data<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'insp_date DESC'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">1</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
	<SPAN class="keyword token">for</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">var</SPAN> related_data <SPAN class="keyword token">in</SPAN> related_data<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">{</SPAN>
		result <SPAN class="operator token">+</SPAN><SPAN class="operator token">=</SPAN> TextFormatting<SPAN class="punctuation token">.</SPAN>NewLine <SPAN class="operator token">+</SPAN> <SPAN class="string token">"Most recent inspection: "</SPAN> <SPAN class="operator token">+</SPAN> max_date<SPAN class="punctuation token">;</SPAN>
	<SPAN class="punctuation token">}</SPAN>
<SPAN class="punctuation token">}</SPAN>

<SPAN class="keyword token">return</SPAN> result<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>

If you could help me figure out what I am missing, I would be so grateful!

XanderBakker

Hi Lea Harper ,

Can you try changing the SQL to:

<SPAN class="comment token">//Create a sql expression to query on GlobalID</SPAN>
<SPAN class="keyword token">var</SPAN> sql <SPAN class="operator token">=</SPAN> <SPAN class="string token">"wall_id = @id"</SPAN><SPAN class="punctuation token">;</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Too bad that you have Enterprise 10.7.1, otherwise (as from 10.8+) you would be able to use the "FeatureSetByRelationshipName" function to retrieve the related records.

Another thing that will not work is the way you extract the max date. In this case it will be a featureset with one record and not the actual date. You need to change it to this (no loop, just take the first record after sorting and extract the information for the correct attribute):

<SPAN class="keyword token">var</SPAN> inspection <SPAN class="operator token">=</SPAN> <SPAN class="token function">First</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="token function">OrderBy</SPAN><SPAN class="punctuation token">(</SPAN>related_data<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'insp_date DESC'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
result <SPAN class="operator token">+</SPAN><SPAN class="operator token">=</SPAN> TextFormatting<SPAN class="punctuation token">.</SPAN>NewLine <SPAN class="operator token">+</SPAN> <SPAN class="string token">"Most recent inspection: "</SPAN> <SPAN class="operator token">+</SPAN> inspection<SPAN class="punctuation token">[</SPAN><SPAN class="string token">"insp_date"</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">;</SPAN>
     <SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Anonymous User

Thanks for getting back to me so quickly, Xander Bakker‌!

I swapped out the sql variable for what you suggested, but I am still getting that error "Execution Error:Cannot read property 'parse' of undefined" when I try returning related_data. I'm not really sure what to make of this since I can't get a more detailed error message...

Also, I see what you mean about the max date and made that correction in my draft (thank you!)... I will very much look forward to having the "FeatureSetByRelationshipName" function available when we upgrade to 10.8.

XanderBakker

Hi lharper_manateegis ,

You will probably need to check if the globalid is empty (function IsEmpty). If so, just return nothing and check your data, since that should not be the case.

utenalarosa

Hi @XanderBakker,

I have a feature that and a table that doesn't have a relate but has a common ID called PARCEL_ID from the table and that same id can be obtain by using concatenate please look at code below.

I am getting an error message on the count

Please advise what am I doing wrong?

XanderBakker

Hi @utenalarosa ,

I noticed that you wrote to me directly. Please have a look at the SQL query. It should follow the syntaxis of "Fieldname = value"

utenalarosa

@XanderBakker

In my case the unique identifier is two field merge together from the feature which is corresponding to one field in the stand alone table, is that possible to use.

The console is printing out the right merge of the 2 fields. Is the last Console print right "relate data: Object, featureSet"?

XanderBakker

Hi @utenalarosa ,

Good to know that the concatenation of the two values results in a correct id. However, the SQL needs to be changed.

On line 7 change it to this:

// change "Fieldname" by the correct name of the field
var sql = "Fieldname = @id"; 

// or use (assuming the field is string)
var sql = "Fieldname = '" + id + "'";

utenalarosa

@XanderBakker

One more question now I wanted a field of the related record to be a hyperlink but I am only getting the string.

and this is the pop-up window

as you can see in the appraisal district it is a hyperlink.

Thanks

FluGGSWupperverband

Hallo @XanderBakker

I had similar problem but somehow I manage to relate the table data with layer data and able to prepare bar and pie charts in the Dashboard. But Now I have faced another challange. I have wanted to make this pic or bar chart Dynamic with layer file. For example you clik one of pic chart then map will be automaticaly show that area or location. is there any way to make it with arccade expression.

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

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