Custom Attribute Expression in ArcGIS Online to Show Related Table Information

[KieranClark]

I have a map that shows international submarine cables and their landing points around the world. I have a standalone table added to my map that lists all the landing points and what cables they are associated with like so:

My list of landings is unique City/State/Country names and it's possible for more than one cable system to come to a single landing. What I want to do - and I think I have all the correct tables and data needed for this - is to create an attribute expression so that when someone clicks on a Landing Point it shows all the Cable Systems associated with that landing. Fortaleza, for instance, is associated with 14 cable systems.

I was able to do something similar in ArcGIS Pro using a Relate but I'm trying to figure out how to do the same thing in my web map. Basically what I need to do is match the "Name" field from my Landing Points layer to the "Cable_Landings_Export.City" field in my standalone table and then add all the cable system names from the "All_Cables_Table_Export.SystemName" field as a list, preferably bulleted or something and not a comma separated list.

I'm not a coder so I'm a little lost trying to work with Arcade and not sure where I should even start.

[XanderBakker]

Hi Kieran Clark ,

There are a couple of things you will need to do in the expression. I assume that you have a point featureclass with the landing points and the name of the city:

• Read out the name of the city of the landing point the user clicked on
• Get access to the table of LandingPlusCableTable
• Create a query using the name of the city
• Use a filter to get all the related records from the table
• Loop through the resulting records and construct a string with all the information you want to present

The code could look like this, but you will have to adjust the table name and fields to match your data:

var cityname = $feature["City Field Name"]; // read out the city
var sql = "City_Field_Name_in_table" + " = " + cityname; // create query
var table = FeatureSetByName($map, "Exact name of table as used in the map"); // get access to table
var related_records = Filter(table, sql); // filter the table for relevant records

var cnt = Count(related_records); // determine the number of records found

var result = "";
if (cnt == 0) {
    // no records found
    result = "No cable systems found";
} else {
    // we have related records, let's create the text to return
    result = cnt + "cable system(s) found:";
    for (record in related_records) {
        txt = TextFormatting.NewLine + " - " + record.systemname; // use the correct field name from the table
        result += txt;
    }
}

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

[KieranClark]

Thanks so much for this, this is definitely a great start.

However, I keep getting "Field not found" errors and I'm not really sure what to do as it's not highlighting where things are going wrong. Let me describe the tables/layers I'm using a little more in detail with the field names.

The standalone table that has landings linked to each cable is named "LandingPlusCableTable" and has the following fields:

Cable_Landings_Export_City - This contains the name of the city where cables land.

Cable_Landings_Export_State_Province - The state or province where the city is located, shouldn't be relevant to this

Cable_Landings_Export_Country - The country where the city is located, shouldn't be relevant to this

All_Cables_Table_Export_OBJECTID - This field contains the CableID that is the unique identifier for each record in our submarine cable database.

All_Cables_Table_Export_SystemName - This field contains the name of the cable system that lands at the city

My Landing Points feature is structured as follows:

"STFLandings20180529_JOIN"

The only relevant field here should be the one that is pulled the City name: "STF_Cable_DBO_tblLanding_City" which has the alias "City"

Please let me know if you need additional information.

[XanderBakker]

Hi kclark_SubTelForum ,

Can you also post the code you tried?

[KieranClark]

Yes, the following has returned the field not found error:

var cityname = $feature["STF_Cable_DBO_tblLanding_City"]; // read out the city
var sql = "Cable_Landings_Export_City" + " = " + cityname; // create query
var table = FeatureSetByName($map, "LandingPlusCableTable"); // get access to table
var related_records = Filter(table, sql); // filter the table for relevant records

var cnt = Count(related_records); // determine the number of records found

var result = "";
if (cnt == 0) {
    // no records found
    result = "No cable systems found";
} else {
    // we have related records, let's create the text to return
    result = cnt + "cable system(s) found:";
    for (record in related_records) {
        txt = TextFormatting.NewLine + " - " + All_Cables_Table_Export_SystemName; // use the correct field name from the table
        result += txt;
    }
}

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

Sorry, how do you get it to post with all the nice color coding and such like you had?

[XanderBakker]

Hi Kieran Clark ,

To get the syntax highlighting you can use JavaScript highlighter:

In More, select Syntax Highlighter:

Next select "JavaScript":

Insert your code.

As to your question, I am not sure if your field names are really called this way (it might show the Alias). One thing that is wrong is line  16:

        txt = TextFormatting.NewLine + " - " + All_Cables_Table_Export_SystemName;‍

... should be:

        txt = TextFormatting.NewLine + " - " + record.All_Cables_Table_Export_SystemName;‍

Note the "record." before your field name.

[KieranClark]

var cityname = $feature["STF_Cable_DBO_tblLanding_City"]; // read out the city
var sql = "Cable_Landings_Export_City" + " = " + cityname; // create query
var table = FeatureSetByName($map, "LandingPlusCableTable"); // get access to table
var related_records = Filter(table, sql); // filter the table for relevant records

var cnt = Count(related_records); // determine the number of records found

var result = "";
if (cnt == 0) {
    // no records found
    result = "No cable systems found";
} else {
    // we have related records, let's create the text to return
    result = cnt + "cable system(s) found:";
    for (record in related_records) {
        txt = TextFormatting.NewLine + " - " + record.All_Cables_Table_Export_SystemName; // use the correct field name from the table
        result += txt;
    }
}

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

I made the change to line 16 as you mentioned, but am still getting the error. As for the field names, I was specifically using the actual field name and not the Alias. Should I be using the Alias instead?

[KieranClark]

Ok, I've made some progress. In ArcGIS Online, I manually grabbed what each table/field is named as seen by the online system. This differs a bit from what I'd been seeing in ArcGIS Pro. The new entries with the correct field names are as follows:

var cityname = $feature["STF_Cable_DBO__tblLanding_City"]; // read out the city
var sql = "Cable_Landings_Export_City" + " = " + cityname; // create query
var table = FeatureSetByName($map, "subcablemapbackup_gdb - LandingPlusCableTable"); // get access to table
var related_records = Filter(table, sql); // filter the table for relevant records

var cnt = Count(related_records); // determine the number of records found

var result = "";
if (cnt == 0) {
    // no records found
    result = "No cable systems found";
} else {
    // we have related records, let's create the text to return
    result = cnt + "cable system(s) found:";
    for (record in related_records) {
        txt = TextFormatting.NewLine + " - " + record.All_Cables_Table_Export_SystemN; // use the correct field name from the table
        result += txt;
    }
}

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

However, I am now getting a new error:

"Execution Error:Expected "!=", "(", "*", "+", "-", "/", "<", "<=", "<>", "=", ">", ">=", "AND", "OR", [ \t\n\r], or end of input but "A" found."

Progress! So I think we're close but I'm not sure what this error is referring to. I'm finding most of these errors to be extraordinarily unhelpful and non-specific.

[XanderBakker]

Hi kclark_SubTelForum ;

Sorry my bad. Have a look at line 2. Since the city name is a text it should be included in quotes:

var cityname = $feature["STF_Cable_DBO__tblLanding_City"]; // read out the city
var sql = "Cable_Landings_Export_City" + " = '" + cityname + "'"; // create query
var table = FeatureSetByName($map, "subcablemapbackup_gdb - LandingPlusCableTable"); // get access to table
var related_records = Filter(table, sql); // filter the table for relevant records

var cnt = Count(related_records); // determine the number of records found

var result = "";
if (cnt == 0) {
    // no records found
    result = "No cable systems found";
} else {
    // we have related records, let's create the text to return
    result = cnt + "cable system(s) found:";
    for (record in related_records) {
        txt = TextFormatting.NewLine + " - " + record.All_Cables_Table_Export_SystemN; // use the correct field name from the table
        result += txt;
    }
}

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

[KieranClark]

New error: Execution Error:Runtime Error: Variable must be declared before it is used..

I'm not seeing any variables that aren't declared, though?