Popup: Iterate through multiple fields in a Related Table

ArianaToth · ‎05-28-2020

Disclaimer: I'm fairly new to Arcade.

Data: I have three data layers in an AGO web map - a polygon layer of municipalities, and two related tables. There's two fields in the municipality table indicating whether or not that municipality is a certain type of client (EngineeringClient and PlanningClient.) There is a corresponding related table for each client type with binary (Y/N) fields for each type of service that could potentially be offered.

I currently have the popup configured to read each of those two fields in the polygon layer and return a statement that the community is a client if either returns true.

Problem: I want my code to identify municipalities for which a client type is true, then iterate through all of the service fields and return the field name for all that have a Y for that record. I have defined a FeatureSet for each related table to narrow the fields down to just the services.

What I would like to do:

Code:

// define engineering and planning clients

var Eclient = $feature.ENGINEERING;

var Pclient = $feature.PLANNING;

//define a variable

var name = $feature.NAME

// define a table of engineering services

var Eserv = (FeatureSetByRelationshipName($feature,"Engineering_2020", ["PLANREVIEW", "WATER", "SANITARY", "STORM", "ROAD", "WATERTREATMENT", "GIS", "ASNEEDED"], false);

// define a table of planning services

var Pserv = FeatureSetByRelationshipName($feature,"Planning_2020", ["CodeBook", "DevelopmentGuidebook", "PublicFacilitation", "MasterPlan", "RecPlan_Grants_Other", "DistrictStudies_Plans", "TIFPlan", "RetainerService", "HourlyServices", "Website", "ZoningAmendments", "StreetScapeDesign", "Wayfinding", "FormBasedCodes"], false);

// iterate through engineering services and print result

if (Eclient == "Y"){

for (var i in eServ){

if (i.PLANREVIEW == "Y"){

return 'Plan Review'

} else {

return ""

}

if (i.Field_Name == "Y") {

return "Field Name"

} else {

return ""

}

};

With the code repeating IF statements for all service fields. Then it would run through all of the Planning services as well once we get the syntax right on the first part.

The problem is that multiple IF statements require using a CONTINUE statement, which breaks the function when I specify a RETURN.

So the workaround I'm currently using requires multiple expressions.

What I'm actually doing:

Code in use:

// define variable
var name = $feature.NAME
//filter to record
var eService = Filter(FeatureSetByName($datastore,"Engineering_2020"),"NAME LIKE '%"+name+"%'")
// iterate through engineering services and print result
for (var i in eService){
    if (i.PLANREVIEW == "Y"){
        return TextFormatting.NewLine + 'Plan Review'
    }
}

This is then repeated as a separate expression for each service type, and concatenated through the custom attribute display.

Engineering Services:{expression/expr1}{expression/expr2}{expression/expr3}{expression/expr4}{expression/expr5}{expression/expr6}{expression/expr7}{expression/expr8}
Planning Services: {expression/expr10}{expression/expr11}{expression/expr12}{expression/expr13}{expression/expr14}{expression/expr15}{expression/expr16}{expression/expr17}{expression/expr18}{expression/expr19}{expression/expr20}{expression/expr21}{expression/expr22}{expression/expr23}{expression/expr24}

Does anyone know how to write this more efficiently?

TIA

--Ariana

XanderBakker · ‎06-02-2020

Hi Ariana Toth ,

Just to clarify, I work for Esri Colombia (not Esri USA), but I am happy to help. The easiest way to share the data without making it public, is to create a group and share the map and data with that group and invite my user "xbakker.spx" to that group. That way I will have access to your data and it wont be shared publicly. Any changes to the map that I will make will be saved locally (in my organization), but I will share them with you through this post. Once we solved the problem, you can stop sharing the data and map and I will loose access to the data.

Another way of sharing the data is to export the data to a FGDB, download it and email it to xbakker [at] esri.co. But this would requiere me to publish the data again: the first option is easier.

XanderBakker · ‎06-02-2020

Hi Ariana Toth ,

Thanks for sharing the map and data. See below a screenshot of a situation where you have both engineering and planning services:

I ended up using 2 expressions so you can format the text color for each list separately. You will also find a dictionary (line 8 to 11 for engineering and 8 to 14 for planning) with the actual text that will be displayed in the list. It will use the name of the field (before the ":") to retrieve the description (behind the ":").

Engineering Arcade expression (27):

var Eclient = $feature.ENGINEERING;

var result = "";
if (Eclient == "Yes") {
    // Engineering client
    var flds_engi = ["PLANREVIEW", "WATER", "SANITARY", "STORM", "ROAD", "WATERTREATMENT", "GIS", "ASNEEDED"];
    // remap dictionary for text to display in pop-up
    var dct_engi = {"PLANREVIEW": "Plan Review", "WATER": "Water Systems", 
                    "SANITARY": "Sanitary Systems", "STORM": "Stormwater Systems",
                    "ROAD": "Roads", "WATERTREATMENT": "Water Treatment", 
                    "GIS": "GIS", "ASNEEDED": "As Needed"};
    var Eserv = FeatureSetByRelationshipName($feature, "Engineering_2020", flds_engi, false);
    var cnte = Count(Eserv);
    if (cnte > 0) {
        // there are related records, take first (or do you need all?)
        var Efeat = First(Eserv);
        for (var i in flds_engi) {
            var fld = flds_engi[i];
            var data = Efeat[fld];
            if (data == "Y") {
                if (result == "") {
                    result = dct_engi[fld];
                } else {
                    result += TextFormatting.NewLine + dct_engi[fld];
                }
            }
        }
    } else {
        // no related records
        result += "None";
    }

} else {
    // Eclient == No
    result += "None";
}

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

Planning Arcade expression (28):

var Pclient = $feature.PLANNING;

var result = "";
if (Pclient == "Yes") {
    // Planning client
    var flds_plan = ["CodeBook", "DevelopmentGuidebook", "PublicFacilitation", "MasterPlan", "RecPlan_Grants_Other", "DistrictStudies_Plans", "TIFPlan", "RetainerService", "HourlyServices", "Website", "ZoningAmendments", "StreetScapeDesign", "Wayfinding", "FormBasedCodes"];
    // remap dictionary for text to display in pop-up
    var dct_plan = {"CodeBook": "Code Book", "DevelopmentGuidebook": "Development Guidebook", 
                    "PublicFacilitation": "Public Facilitation", "MasterPlan": "Master Plan", 
                    "RecPlan_Grants_Other": "RecPlan Grants (Other)", "DistrictStudies_Plans": "District Studies Plans", 
                    "TIFPlan": "TIF Plan", "RetainerService": "Retainer Service", 
                    "HourlyServices": "Hourly Services", "Website": "Website", 
                    "ZoningAmendments": "Zoning Amendments", "StreetScapeDesign": "Street Scape Design", 
                    "Wayfinding": "Wayfinding", "FormBasedCodes": "Form Based Codes"};

    var Pserv = FeatureSetByRelationshipName($feature, "Planning_2020", flds_plan, false);
    var cntp = Count(Pserv);
    if (cntp > 0) {
        // there are related records, take first (or do you need all?)
        var Pfeat = First(Pserv);
        for (var i in flds_plan) {
            var fld = flds_plan[i];
            var data = Pfeat[fld];
            if (data == "Y") {
                if (result == "") {
                    result = dct_plan[fld];
                } else {
                    result += TextFormatting.NewLine + dct_plan[fld];
                }
            }
        }
    } else {
        // no related records
        result += "None";
    }

} else {
    // Pclient == No
    result += "None";
}

return result;

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The pop-up was configured like this (I removed the other information for simplicity):

ArianaToth · ‎06-02-2020

Thank you!!! This works perfectly!

--Ariana

XanderBakker · ‎06-02-2020

Hi atothgwe ,

I'm glad to hear that it works. If you have any question about the expression just post back here.