Accessing M-N and 1-M related table records in Portal Pop-ups
Hello, I'm an Arcade newbie trying to modify the pop up for the parcels in a web map that serves as the basis for a Portal web application. The parcels feature layer in question has a pairing of relationship classes set up to other tables in the published Map Service. This Service, called "TL_Parcels_Activities" is added to the web map and called "Tidelands Activities" in the Contents.
It contains six different items with a different definition query on the parcels source data, a feature class in our geodatabase called "TL_Parcels".
TL_Parcels has a Many-to-Many relationship to the PI Numbers table, which, in turn, has a One-to-Many relationship to the Tidelands Activities table.There is also an additional 1-M relationship set up to a Site ID table. All of these tables and relationship class tables were included in the Map Service and show up in the web map.
The Related Records show up in the pop-up to open the related data in tabular form well enough...
... but I was hoping to use Arcade to get the related info to appear in the pop-up directly, without having to click and read in the attribute tables themselves.
I found some good examples of script suggestions on GeoNet for some 1-M situations that I tried out. I think folks were using these for AGO, which I understand has some quirky differences from the Portal environment. I started with this sample: How do you reference another field from a related table in Arcade?
My initial attempts kept giving me the error:
"Execution Error:Runtime Error: Identifier Not Found. $map"
I found an example that focused on the GlobalID that I decided to run with and still got the same problem.
Get 1:M related objects using Arcade
// read the GlobalID of the feature
var globalid = $feature.GlobalID;
// create a sql expression to filter the asset table
var sql = "gis_id = '" + globalid + "'";
// Access the Activities based on the name in the map
var ACTIV_view = FeatureSetByName($map, "TL_PINumbers");
// filter the assets using the sql expression
var ACTIV = Filter(ACTIV, sql);
// get the count of the related Activities
var cnt = Count(Activities);
// create a variable to hold the resulting text
var ACTIV_info = "";
// validate if there are any related records and handle accordingly
if (cnt > 0) {
// there is at least 1 related record, create activity info
ACTIV_info = cnt + " Activities:";
// loop through related records
for (var activity in Activities) {
// create text using name and description (you should modify this to match your needs)
var ACTIV_text = " - " + Activity.name + TextFormatting.NewLine + Activity.description;
// add the text for this activity to the activity info
ACTIV_info += TextFormatting.NewLine + ACTIV_text;
}
} else {
// no related records (or the sql query did not work)
ACTIV_info = "No related Activities";
}
return ACTIV_info;With these I could never access the $map from the Globals tab. Despite the fact that all of these other things were in the same web map, all I could see were the $feature for all of the fields in the Parcels feature class.
As a test, I separately published a Hosted Feature Layer of the parcels/relationships which shows the two related tables, but not the relationship classes themselves, within the service.
Then I added that to my web map and tried to use the same Arcade expression on that layer. $map was now accessible to me in the Globals and the code seemed to get past that sticking point, but gave me an Execution Error: Filter cannot accept this parameter type.
I'm not always exactly sure what the sample scripts are asking for everywhere, so I'm not fully sure I'm putting the correct info where it belongs. I also know that I will probably need to do more to this to add all the attribute info I want to display and probably to get the script to go to the next level for the other 1-M relationship between the two tables.
Thanks.
We also worked on a solution to extract data from a one to many relationship. Below is the result of the pop-up that shows the PI record (the related table information to the GIS Layer (parcels)) and the subsequent file numbers and status of the files (fields in a related table to the PI record table)
This was the code for the pop-up. We used FeatureSetByID instead of FeatureSetbyName.
var tbl_pi = FeatureSetById($map, /* Cadastral - Tidelands PI Numbers Composite */ "Cadastral_2933")
var tbl_act = FeatureSetById($map, /* Cadastral - Tidelands Activities */ "Cadastral_5847")
var PAMS_PIN = $feature["PAMS_PIN"];
var sql = "PAMS_PIN = @PAMS_PIN";
var Pam_Records = Filter(tbl_pi, sql);
var cnt = Count(Pam_Records);
var Record_List = "";
if (cnt>0)
{
for (var Pam_Record in Pam_Records){
var pi = Pam_Record.PI_NUMBER;
Record_List += TextFormatting.NewLine + "PI Number: " + pi + TextFormatting.NewLine;
// filter tbl_act
var file_no = Pam_Record.PI_NUMBER
var activities = Filter(tbl_act, "PI_NUMBER = @file_no")
var tableresults = ""
for(var act in activities) {
var txt_FileNumber = act.FILE_NUMBER;
var status = act.DOCUMENT_STATUS;
var pi = act.PI_NUMBER;
var Doc_Status_Date = "Doc Status Date: " + Text(act.DOCUMENT_STATUS_DATE, 'MM/DD/Y');
tableresults += "File # "+ txt_FileNumber + TextFormatting.NewLine + Doc_Status_Date + TextFormatting.NewLine + "Status: " + status + TextFormatting.NewLine + TextFormatting.NewLine;
}
Record_List += TextFormatting.NewLine + tableresults;
}
} else {
Record_List = "No Records";
}
return Record_List;You can place the expression by selecting a description from one field or by selecting custom configuration and adding the expression:
