Related Record Attributes in Pop-up Display with Arcade Expression

[RyanElvrum2]

My goal is to create a pop-up expression using Arcade to show attribute values from a filtered related table two-relationships deep.

My data is related as follows;

Service Location (Point feature) --> Service Account (Table) --> Meters (Table)

I am able to create an pop-up expression for the Service Location that returns attributes from the related Service Account table, but I would like to go one level deeper and also return attributes from the related Meter table.

Currently my Arcade expression is;

var ServiceLocationGID = $feature.GlobalID
var ServiceAccounts = FeatureSetByName($map,"Service Account - Electric")
var filterstatement = 'ServiceLocationGID = @ServiceLocationGID'

var relatedData = Filter(ServiceAccounts, filterstatement)

var popupString = ''
for ( var f in relatedData){

popupString += "First Name: " + Text(Proper(f.firstname)) + TextFormatting.Newline
+ "Last Name: " + Proper(f.lastname) + TextFormatting.Newline
+ "Service Address 1: " + Proper(f.serviceaddr1) + TextFormatting.Newline
+ "Service Address 2: " + Proper(f.serviceaddr2) + TextFormatting.Newline
+ "Account Number: " + f.AccountNumber + TextFormatting.Newline + TextFormatting.Newline
}
return popupString

I am unsure on how to format the expression to then filter the meter table and return attribute values from the related record to include in my pop-up string.

[RyanElvrum2]

@XanderBakker 

[XanderBakker]

Hi @RyanElvrum2 ,

I would probably use a nested FeatureSetByRelationshipName function like this if you have a nested relationship:

// Service Location (Point feature) --> Service Account (Table) --> Meters (Table)

// get the feature, which is the service location
var servicelocation = $feature;

// use the service location and the relationship to drill down to service accounts
var serviceaccounts = FeatureSetByRelationshipName(servicelocation, "Relationship name for Service Location to Service Account", ['*'], false);

// loop through the service accounts
for (var serviceaccount in serviceaccounts) {
    // include info of the service account?
    // use the service account to drill down to the meter information
    var meters = FeatureSetByRelationshipName(serviceaccount, "Relationship name for Service Account to Meters", ['*'], false);

    // loop through the meters
    for (var meter in meters) {
        // do something with the meter information
    }    
}

[RyanElvrum2]

Thanks for the response Xander. After looking into this a bit, I am assuming the "FeatureSetByRelationshipName" function is not available to me. I am trying to configure this arcade expression in Portal on an ArcGIS Enterprise Server 10.7.1.

Any ideas for a workaround assuming the above function is not available to me?

[XanderBakker]

Hi @RyanElvrum2 ,

I am afraid that is true. This function was introduced at version 1.8 of Arcade and Enterprise 10.7.1 has version 1.7 of Arcade.

Your initial code that uses a Filter function and a SQL query would be the right way to go. And when nesting this twice you should be able to get to the meter information. 

An example of this is shown below: 

// Service Location (Point feature) --> Service Account (Table) --> Meters (Table)

// get the value of the service location feature that connects to the service accounts
var ServiceLocationValueToQueryFor = $feature["FieldNameServiceLocationValue"];

// create SQL expression
var SqlServiceAccounts = "SomeField = @ServiceLocationValueToQueryFor";

// filter the related service accounts
var ServiceAccounts = FeatureSetByName($map, "Name of the ServiceAccounts table in the map");
var RelatedServiceAccounts = Filter(ServiceAccounts, SqlServiceAccounts);

// loop through the service accounts
var Meters = FeatureSetByName($map, "Name of the Meters table in the map");
for (var ServiceAccount in RelatedServiceAccounts) {
    // include info of the service account?
    var MeterValueToQueryFor = ServiceAccount["FieldNameMaterValue"];
    var SqlMeters = "SomeField = @MeterValueToQueryFor";
    // use the service account to drill down to the meter information
    var RelatedMeters = Filter(Meters, SqlMeters);

    // loop through the meters
    for (var Meter in RelatedMeters) {
        // do something with the meter information
    }    
}

[RyanElvrum2]

Xander, again thank you for your reply. I've been working away at this for a few hours now. I am having issues as soon as I get to line 15. I get an error message about an undefined variable (I am assuming the variable is 'ServiceAccount')

I wonder if this issue is related to the relationship types between the features. All relationships are a 1:M, so 1 Service Location can have multiple Service Accounts related to it, and 1 Service Account can have multiple Meters related to it.

Any insight here?

[RyanElvrum2]

@XanderBakker 

***Edit***

This expression was close, but Xander pointed out that it will only return 1 meter number value, when instead I needed all meter numbers related to the account. Xander has the solution below

var ServiceLocationGID = $feature.GlobalID
var SqlServiceAccounts = "ServiceLocationGID = @ServiceLocationGID"

var serviceaccounts = FeatureSetByName($map,"ServiceAccounts")
var RelatedServiceAccounts = Filter(ServiceAccounts, SqlServiceAccounts)

var meters = FeatureSetByName($map,"Meters")
var popupstring = ''
for (var serviceaccount in RelatedServiceAccounts){
    
    var FirstName = "First Name:  " + Text(Proper(serviceaccount.firstname)) + TextFormatting.Newline
    var LastName = "Last Name:  " + Proper(serviceaccount.lastname) + TextFormatting.Newline
    var ServiceAddress1 = "Service Address 1:  " + Proper(serviceaccount.serviceaddr1) + TextFormatting.Newline
    var ServiceAddress2 = "Service Address 2:  " + Proper(serviceaccount.serviceaddr2) + TextFormatting.Newline
    var AccountNumber = "Account Number: " + serviceaccount.AccountNumber + TextFormatting.Newline
    
    var ServiceAccountGID = serviceaccount.GlobalID
    var SqlMeters = "ServiceAccountGID = @ServiceAccountGID"
    
    var RelatedMeters = Filter(Meters, SqlMeters)
    for (var meter in RelatedMeters){
        Var MeterNumber = "Meter Number: " + meter.MeterID + TextFormatting.Newline
    }
    popupstring += Firstname 
    + LastName 
    + ServiceAddress1 
    + ServiceAddress2 
    + AccountNumber 
    + text(MeterNumber) 
    + TextFormatting.Newline
}
Return popupstring

[XanderBakker · Accepted solution]

Hi @RyanElvrum2 ,

Do you have multiple meters for a single service account? If so, you will only get a single meter in the pop-up string. Also, the meter number does not need to be cast to string since this will always be a string when cascading a text with other information. 

Try this: 

var ServiceLocationGID = $feature.GlobalID;
var SqlServiceAccounts = "ServiceLocationGID = @ServiceLocationGID";

var ServiceAccounts = FeatureSetByName($map, "ServiceAccounts");
var RelatedServiceAccounts = Filter(ServiceAccounts, SqlServiceAccounts);

var Meters = FeatureSetByName($map, "Meters");
var popupstring = "";
for (var ServiceAccount in RelatedServiceAccounts){
    
    popupstring += "First Name:  " + Text(Proper(ServiceAccount.firstname)) + TextFormatting.Newline;
    popupstring += "Last Name:  " + Proper(ServiceAccount.lastname) + TextFormatting.Newline;
    popupstring += "Service Address 1:  " + Proper(ServiceAccount.serviceaddr1) + TextFormatting.Newline;
    popupstring += "Service Address 2:  " + Proper(ServiceAccount.serviceaddr2) + TextFormatting.Newline;
    popupstring += "Account Number: " + ServiceAccount.AccountNumber + TextFormatting.Newline;
    
    var ServiceAccountGID = ServiceAccount.GlobalID;
    var SqlMeters = "ServiceAccountGID = @ServiceAccountGID";
    
    var RelatedMeters = Filter(Meters, SqlMeters);
    for (var Meter in RelatedMeters) {
        popupstring += " - Meter Number: " + Meter.MeterID + TextFormatting.Newline;
    }
    popupstring += TextFormatting.Newline;
}

Return popupstring;

[RyanElvrum2]

@XanderBakker  You're the best! Your expression gave me exactly what I was looking for, and I appreciate the extra insight into how these expressions work! Yes, my accounts can have multiple meters related to them, and my expression was only returning 1 value. Your solution returns all of them!

Thank you very much for your help. I've seen your name provide help for multiple different Arcade related questions, so I figured I'd see if you can help me, and you did!

[XanderBakker]

You're welcome @RyanElvrum2 , glad it works