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

[joerodmey]

Or would it be easier to always default the values to zero and just overwrite them if a related record exists and grab that value? Basically I want to do this as the hours are blank until a user enters a value. If its blank the calculation I have (all based in Arcade and pulling from the related data) don't work properly as its pulling and returning blank values. Have any ideas how to achieve this?

[XanderBakker]

Hi Joerodney ,

Wouldn't it be possible to include logic in the Arcade expression to detect those situations and act accordingly?

[joerodmey]

Hi Xander Bakker‌

How would I accomplish this in Arcade?

Thanks

[XanderBakker]

Hi Joerodney ,

Can you share the Arcade expression that you have so far?

[AlexandreSantos2]

Hi Xander Bakker

Thank you a lot for all the info you've been making available on these Arcade expressions.

I have a very similar problem that I can't put to work.

I want to prepopulate fields on the child table based on the info from the parent table.

I'm starting in the field calculation in ArcGIS Pro, to create a functional Attribute rule, and in the end publish the FGDB in AGOL, so users have their data entry easier in an App.

Trying this:

var results = FeatureSetByRelationshipName($feature, "parcelas_socio2018_oper_culturais", ['N_SOCIO'], false)
return results

The expression is valid and the calculation runs but nothing gets written. I wonder if it's because there's fields in both tables with the exact same name...?

Can you point me in the right direction?

[XanderBakker]

Hi GeosenseAgol ,

If your end goal is to use the attribute rule in ArcGIS Online, then I am afraid that ArcGIS Online does not support this yet. Attribute rules are executed at the database level and at this momento only available in Enterprise Geodatabases and File Geodatabases.

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

//Read out the GlobalID of the seawall feature layer
var id = $feature["globalid"];

//Create a sql expression to query on GlobalID
var sql = "wall_id = '" + Upper(id) + "'";

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

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

//check filter
return related_data‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

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.

//Create a sql expression to query on GlobalID
var sql = "wall_id =" + id + "";‍‍‍‍‍‍

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:

//Read out the GlobalID of the seawall feature layer
var id = $feature["globalid"];

//Create a sql expression to query on GlobalID
var sql = "wall_id = '" + Upper(id) + "'";

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

//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 = "No inspections available.";

//Check if there are related records found for the current GlobalID
if (cnt > 0) {
	result = "Inspections: " + cnt;
	//Get most recent inspection
	var max_date = Top(OrderBy(related_data, 'insp_date DESC'), 1);
	for (var related_data in related_data){
		result += TextFormatting.NewLine + "Most recent inspection: " + max_date;
	}
}

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

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:

//Create a sql expression to query on GlobalID
var sql = "wall_id = @id";‍‍

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

var inspection = First(OrderBy(related_data, 'insp_date DESC'));
result += TextFormatting.NewLine + "Most recent inspection: " + inspection["insp_date"];
     ‍‍‍

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