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

• Summarize Features in a pop-up with Arcade 
• https://community.esri.com/docs/DOC-12773-using-featuresetby-functions-in-arcade-to-drill-down-to-ot... 

[deleted-user-TRj-mR_qEmwV]

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. 

[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