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

[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:

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

[ChelseyAiton2]

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

[XanderBakker]

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:

[ChelseyAiton2]

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

[XanderBakker]

My pleasure!

[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

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

[XanderBakker]

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

[joerodmey]

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