topic Re: How do you reference another field from a related table in Arcade? in Nonprofit Program Questions

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

deleted-user-TRj-mR_qEmwV — Tue, 26 Feb 2019 14:19:37 GMT

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

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

XanderBakker — Fri, 10 Dec 2021 20:11:47 GMT

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:

// first read out the ID of the Feature1
var id = $feature.ID;
// access the table Feature2
var tbl = FeatureSetByName($map, 'Feature2');
// create a sql expression to query on ID
var sql = "ID = '" + ID + "'";
// filter the table using the sql expression
var related_data = Filter(tbl, sql);
// count the resulting records
var cnt = Count(related_data);

// initiate a variable to hold the result
var result = cnt + " related records";

// check if there are related records found for the current ID
if (cnt > 0) {
    // loop through related records
    for (var row in related_data) {
        // read some data and create the line you want
        var line = TextFormatting.NewLine + " - " + row.SomeFieldOfInterest;
        // add the line to the result
        result += line;
    }
} else {
    result = "No related records:";
}

// return the result
return result;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Some more examples and explanation can be found here:

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

deleted-user-TRj-mR_qEmwV — Fri, 10 Dec 2021 20:11:49 GMT

Hey Xander,

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

var cnt = Count(related_data);

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.

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

XanderBakker — Wed, 27 Feb 2019 22:30:08 GMT

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?

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

deleted-user-TRj-mR_qEmwV — Thu, 28 Feb 2019 15:26:57 GMT

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

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

KenMorefield — Mon, 15 Apr 2019 21:10:00 GMT

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!

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

XanderBakker — Tue, 16 Apr 2019 13:36:50 GMT

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.

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

ChelseyAiton2 — Wed, 08 May 2019 22:14:15 GMT

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.

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

XanderBakker — Thu, 09 May 2019 12:51:35 GMT

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.

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

ChelseyAiton2 — Thu, 09 May 2019 15:50:03 GMT

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

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

XanderBakker — Fri, 10 Dec 2021 20:11:52 GMT

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:

var tbl = FeatureSetByName($map,"MS_table2 - ms_table2");
Console(Count(tbl));
var pt = $feature.TAXACCT;
Console(pt);
var sql = "TAXACCT = '" + pt + "'";
Console(sql);
var msaccts = Filter(tbl, sql);
var cnt = Count(msaccts);

var result = "There are no related records...";
if (cnt > 0) {
    result = "Related records: " + cnt;
    for (var msacct in msaccts) {
        result += TextFormatting.NewLine + " - " + msacct.PARK + " (" + msacct.PARKNUM + ")"; 
    }
}

return result;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

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

ChelseyAiton2 — Thu, 09 May 2019 17:22:48 GMT

Thank you so much for spending the time assisting me! I was able to get it to work based upon your above script. I believe two things in combination were happening. My original script used $datastore in the first line, which now I know represents a collection of layers in the same feature service. My layers are in different feature services, so I needed to use $map. Also, when I had tried variations of the layer name, which I also didn't have exactly correct, I was still using $datastore, which gives you the same error message as when you have just the layer name incorrect, using $map. (I thought FeatureSetByName would use the layer name given to the service rather than the name given to it in the web map. If you rename the layer in the map, this breaks the expression).

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

XanderBakker — Thu, 09 May 2019 19:08:12 GMT

That's a very important thing you are mentioning: the difference between using the $datastore or the $map to access data and the name of the layer in the map or in the hosted feature service. Please note that the navigation provided by the Arcade expression window allows you to have access to:

content in the datastore
content in the map
the exact names of the fields

So, use the larger than sign ">" to the right to access in this case the content of the $map:

Once in the $map, you can see every available layer, or navigate the fields in the layer using once again the larger than sign:

and you will see the fields:

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

ChelseyAiton2 — Thu, 09 May 2019 19:23:29 GMT

Xander, this navigation is exactly what I was missing. Thanks for providing this.

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

XanderBakker — Thu, 09 May 2019 19:35:58 GMT

My pleasure!

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

joerodmey — Thu, 15 Aug 2019 14:09:41 GMT

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

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

XanderBakker — Thu, 15 Aug 2019 14:47:27 GMT

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.

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

joerodmey — Fri, 10 Dec 2021 20:11:55 GMT

Xander Bakker This I what I have so far

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

// initiate a variable to hold the result
var result = cnt + " related records";

// check if there are related records found for the current ID
if (cnt > 0) {
    // loop through related records
    for (var row in related_data) {
        // read some data and create the line you want
        var line = TextFormatting.NewLine + " - " + row.Item;
        // add the line to the result
        result += line;
    }
} else {
    result = "No related records:";
}

// return the result
return result;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

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

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

XanderBakker — Fri, 10 Dec 2021 20:11:57 GMT

Hi joe rodmey

You can try something like this:

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

// initiate a variable to hold the result
var result = 0;

// check if there are related records found for the current ID
if (cnt > 0) {
    // loop through related records
    for (var row in related_data) {
        // add the value  to the result
        result += row.item; 
    }
}

// return the result
return result * 2.5;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

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

joerodmey — Thu, 15 Aug 2019 19:29:28 GMT

Hi Xander Bakker‌,

I get the following error: "Execution Error: Error"

Am I pulling the ID properly here: "var id = $feature.objectid;"

This objected reflect the object ID from the point feature service and not the related table