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

[Anonymous User]

Hey Xander Bakker - 

Just did a quick IIF statement to verify the globalIDs and all came back correctly.

It almost seems like the issue is specifically on the Filter function - even when I try simplifying the sql variable or hard coding the filter function to something specific like below, the parse error is still thrown.

var related_data = Filter(tbl, tbl["cap_iss"]=="test");‍‍

Is it possible there is some kind off setting I may have missed with the feature service was published to Portal? I've verified that it supports both standardized and advanced queries.

I don't know if it makes a difference, but here is the webmap we're fiddling in. And thank you - I do really appreciate your help even though this is giving us a good go-around.

[XanderBakker]

Hi Lea Harper ;

I am starting to believe there might be an issue with your Enterprise installation. It is throwing an error with the gisportal/jsapi/jsapi4/esri/core/sql/WhereGrammar.js :

Could you reach out to Esri Support?

Below the Expression I tested (but the moment it tries to do a count on the result of the filter it fails):

//Read out the GlobalID of the seawall feature layer
var GlobID = $feature["globalid"];
Console(GlobID);

//Create a sql expression to query on GlobalID
var sql = "wall_id = @GlobID";
//var sql = "wall_id = '{" + Upper(GlobID) + "}'";
Console(sql);

//Get related table
var tbl = FeatureSetByName($datastore,"Seawall Inspections");
Console(TypeOf(tbl));

//Filter the table using the sql expression
var related_data = Filter(tbl, sql);
Console(TypeOf(related_data));

//Count the resulting records
var cnt = Count(related_data);
Console(cnt);

//Initiate a variable to hold the result
var result = "No inspections available.";

//Check if there are related records found for the current GlobalID
if (cnt > 0) {
    var inspection = First(OrderBy(relateddata, 'insp_date DESC'));
    result += TextFormatting.NewLine + "Most recent inspection: " + inspection["insp_date"];
}

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

[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

[XanderBakker]

Hi @utenalarosa ,

Unfortunately in ArcGIS Online the result is returned as text and as you noticed the text does not support the hyperlinks. This is something that you can do in ArcGIS Pro, since in Pro you can return HTML. It is on the Roadmap to include this in ArcGIS Online too, but I don't know when this will be included.

[utenalarosa]

Is there a link to an example on how to do it in Pro? Thanks