topic Re: Popup: Iterate through multiple fields in a Related Table in Developers Questions

Popup: Iterate through multiple fields in a Related Table

ArianaToth — Thu, 28 May 2020 18:37:22 GMT

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

Re: Popup: Iterate through multiple fields in a Related Table

MikieKelly — Thu, 28 May 2020 18:45:30 GMT

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?

Re: Popup: Iterate through multiple fields in a Related Table

XanderBakker — Sat, 11 Dec 2021 18:55:19 GMT

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.

Re: Popup: Iterate through multiple fields in a Related Table

ArianaToth — Fri, 29 May 2020 02:02:28 GMT

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.

Re: Popup: Iterate through multiple fields in a Related Table

XanderBakker — Sat, 11 Dec 2021 18:55:22 GMT

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;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Popup: Iterate through multiple fields in a Related Table

ArianaToth — Fri, 29 May 2020 16:20:00 GMT

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.

Re: Popup: Iterate through multiple fields in a Related Table

XanderBakker — Fri, 29 May 2020 17:48:52 GMT

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.

Re: Popup: Iterate through multiple fields in a Related Table

ArianaToth — Fri, 29 May 2020 19:48:25 GMT

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

Re: Popup: Iterate through multiple fields in a Related Table

XanderBakker — Tue, 02 Jun 2020 00:03:11 GMT

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.

Re: Popup: Iterate through multiple fields in a Related Table

ArianaToth — Tue, 02 Jun 2020 03:43:32 GMT

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.

Re: Popup: Iterate through multiple fields in a Related Table

XanderBakker — Tue, 02 Jun 2020 13:30:32 GMT

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.

Re: Popup: Iterate through multiple fields in a Related Table

XanderBakker — Sat, 11 Dec 2021 18:55:25 GMT

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

Re: Popup: Iterate through multiple fields in a Related Table

ArianaToth — Tue, 02 Jun 2020 16:37:28 GMT

Thank you!!! This works perfectly!

Re: Popup: Iterate through multiple fields in a Related Table

XanderBakker — Tue, 02 Jun 2020 16:38:36 GMT

Hi atothgwe ,

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