Popup: Iterate through multiple fields in a Related Table

[ArianaToth]

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

[MikieKelly]

I'm probably over simplifying this, but could you not concatenate what are currently return statements into a single variable and then return it outside of the loops?

[XanderBakker]

Hi atothgwe ,

Maybe something like this will work:

// define engineering and planning clients
var Eclient = $feature.ENGINEERING;
// var Pclient = $feature.PLANNING;  // not being used

var result = "";
if (Eclient == "Y") {
    // Engineering client
    result = "Engineering Services: ";
    var flds_engi = ["PLANREVIEW", "WATER", "SANITARY", "STORM", "ROAD", "WATERTREATMENT", "GIS", "ASNEEDED"];
    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?)
        Efeat = First(Eserv);
        for (var i in flds_engi) {
            var fld = flds_engi[i];
            var data = Efeat[fld];
            if (data == "Y") {
                if (result == "Engineering Services: ") {
                    result += fld;
                } else {
                    result += ", " + fld;
                }
            }
        }
    } else {
        // no related records
        result += "None";
    }

} else {
    // Planning client (is this true?)
    result = "Planning Services: ";
    var flds_plan = ["CodeBook", "DevelopmentGuidebook", "PublicFacilitation", "MasterPlan", "RecPlan_Grants_Other", "DistrictStudies_Plans", "TIFPlan", "RetainerService", "HourlyServices", "Website", "ZoningAmendments", "StreetScapeDesign", "Wayfinding", "FormBasedCodes"];
    var Pserv = FeatureSetByRelationshipName($feature, "Planning_2020", flds_plan, false);
    var cntp = Count(Eserv);
    if (cntp > 0) {
        // there are related records, take first (or do you need all?)
        Pfeat = First(Pserv);
        for (var i in flds_plan) {
            var fld = flds_plan[i];
            var data = Pfeat[fld];
            if (data == "Y") {
                if (result == "Planning Services: ") {
                    result += fld;
                } else {
                    result += ", " + fld;
                }
            }
        }
    } else {
        // no related records
        result += "None";
    }
}

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

Don't return a result inside a loop or condition if you need the expression to continue.

[ArianaToth]

Thanks Xander Bakker.‌

But I do need all services to be returned for both Engineering and Planning. Some client communities are clients of both departments.

[XanderBakker]

Hi atothgwe ,

In that case you can change the code to this:

// define engineering and planning clients
var Eclient = $feature.ENGINEERING;
var Pclient = $feature.PLANNING;

var result = "Engineering Services: ";
if (Eclient == "Y") {
    // Engineering client
    var flds_engi = ["PLANREVIEW", "WATER", "SANITARY", "STORM", "ROAD", "WATERTREATMENT", "GIS", "ASNEEDED"];
    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?)
        Efeat = First(Eserv);
        for (var i in flds_engi) {
            var fld = flds_engi[i];
            var data = Efeat[fld];
            if (data == "Y") {
                if (result == "Engineering Services: ") {
                    result += fld;
                } else {
                    result += ", " + fld;
                }
            }
        }
    } else {
        // no related records
        result += "None";
    }

} else {
    result += "None";
}

result += TextFormatting.NewLine + "Planning Services: "
if (Pclient == "Y") {
    // Planning client (is this true?)
    var flds_plan = ["CodeBook", "DevelopmentGuidebook", "PublicFacilitation", "MasterPlan", "RecPlan_Grants_Other", "DistrictStudies_Plans", "TIFPlan", "RetainerService", "HourlyServices", "Website", "ZoningAmendments", "StreetScapeDesign", "Wayfinding", "FormBasedCodes"];
    var Pserv = FeatureSetByRelationshipName($feature, "Planning_2020", flds_plan, false);
    var cntp = Count(Eserv);
    if (cntp > 0) {
        // there are related records, take first (or do you need all?)
        Pfeat = First(Pserv);
        for (var i in flds_plan) {
            var fld = flds_plan[i];
            var data = Pfeat[fld];
            if (data == "Y") {
                if (result == "Planning Services: ") {
                    result += fld;
                } else {
                    result += ", " + fld;
                }
            }
        }
    } else {
        // no related records
        result += "None";
    }
} else {
    result += "None";
}

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

[ArianaToth]

That looks good but it's not returning any result for me. When I test the script in the popup configuration, I get this:

But when I click on features in the map, the popup is blank - no text at all.

[XanderBakker]

Hi Ariana Toth ,

Without access to data it will be very hard to detect where things go wrong. However, the fact that there is no result in the pop-up (no text at all) confirmes that there is something wrong with the syntax of the expression. 

[ArianaToth]

Xander Bakker, I'm taking a closer look at your two scripts and they appear the same. How do I get it to return a list of all services == "Y" and not just the first?

Also, and this could just be because I'm a n00b and don't understand, but can I really define flds using just ["field_name"] without naming a source when those fields are in a related table? (See lines 8 & 37 above.)

[XanderBakker]

Hi Ariana Toth ,

The two script do have a lot in common, but the second one, should take into that a client could have engineering and planning services. I do notice an error on line 39 where I use "Eserv" which should be "Pserv". In theory, by using a list of relevant fields per featureservice, you can loop through the list and extract the values a feature has in each field. I do wonder if this works when using the syntaxis like "Pserv[fld]" since normally you extract them like Pserv.fieldname. I could check this if I had access to actual data.

Using variables "flds_plan" and "flds_engi" to specify the list of fields, is to be able to use that same list of fields on lines 14-15 and 43-44 to loop through the fields and extract the value of that field and if it is "Y" te return the name of the field as engineering or planning service.

I also included a question in the code. How many related records do you normally have per feature? Is it 1:1 or 1:n. In caseo of 1:n what would you like to do with these multiple records?

To help you further I will really need to have access to data so I can see what is going wrong with the code.

[ArianaToth]

Thank you for continuing to assist with this problem. The relationship with each table is 1:1. 

How can I send you the data? It is not public data but since you work for Esri, I can probably share it with you for support.