topic Accessing M-N and 1-M related table records in Portal Pop-ups in Developers Questions

Accessing M-N and 1-M related table records in Portal Pop-ups

MattLashendock — Sat, 11 Dec 2021 18:28:08 GMT

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.

Re: Accessing M-N and 1-M related table records in Portal Pop-ups

RogerCleaves1 — Fri, 10 Apr 2020 23:20:26 GMT

Hey Matt,

Literally was working on this today! Here's some code that looks through a related table then returns the field.

var relatedrecords = OrderBy(FeatureSetByRelationshipName($feature,"YourRelatedTableHere"), "YourUniqueIdentifier");
var cnt = Count(relatedrecords);

var relatedinfo = "";
if (cnt > 0) {
var info = First(relatedrecords);
relatedinfo = info.fieldyouwanttodisplay;
}

return relatedinfo;

Let me know if this works for you!

Re: Accessing M-N and 1-M related table records in Portal Pop-ups

Islandgroup — Mon, 18 May 2020 13:17:00 GMT

Hi Roger, Many thanks for this thread. We used this to code to show a pop up using a 1toM relatationship between a point feature and a related table. However, it does not show all the related records, only the first. I cannot use the actual data but have mocked up a similar table structure to the one we are working with. Is there something obvious we are missing. Thanks Paul

Resolved...

var allIngredients = FeatureSetByName($datastore, "Ingredients_all2");

var ingredients = Filter(allIngredients, "Vendor_Nam = '"+$feature["FIRST_Vendor_Nam"]+"'");

var materials = OrderBy(Distinct(ingredients, "Material_N"), "Material_N ASC");

var cnt = Count(materials);

var ingredientList = "";

if (cnt > 0) {

for(var material in materials){

ingredientList += material.Material_N + '\n';

}

return ingredientList;

Re: Accessing M-N and 1-M related table records in Portal Pop-ups

MattLashendock — Mon, 18 May 2020 16:51:41 GMT

Lashendock, Matthew

Mon 4/20/2020 11:54 AM

Hi Roger,

Thank you for the reply and the scripting.

Tried it with this, just attempting to get the first bit of info, The PI Number field, out of the table:

var relatedrecords = OrderBy(FeatureSetByRelationshipName($feature,"TL_Parcels_Activities - TL_PINumbers"), "TL_PINumber");

var cnt = Count(relatedrecords);

var relatedinfo = "";

if (cnt > 0) {

var info = First(relatedrecords);

relatedinfo = info.PI_NUMBER;

}

return relatedinfo;

… and got the Execution Error: Runtime Error: Function Not Found: FeatureSetByRelationshipName

That sounds like the function is not usable for that task or in the Portal environment.

Part of what I’m wondering is if the related table and identifier info should be listed as it actually is in the data or as it shows in the web map.

This is the way the service shows up with its tables in our portal:

The TL PINumbers table is actually called TL_PINumbers in our data. Not sure if that matters.

I’m hoping that if I can get the PI Number from the this table to appear in the popup, then all I would have to do is then list those other fields as well, even though they reside in that next level relationship table. But I’m not sure if I shouldn’t be using one of the other tables listed above or not.

I’m not sure if the presence of the tables for the Relationship Classes that are set up for this (TL RS ToPINumbers) confuses things at all or not. Or if that should be the table that is used instead. This just has the two items that are compared.

Then there is the SiteID Composite table which shows the connection to the relationship [Related PI Number(s)].

The TL Activities table is the second level 1:M relate that shows all the additional info in the fields above that we also want to have listed ultimately.

Hope that isn’t too confusing! Below is a sketched diagram of how the tables are connected:

The other table referring to SiteID is a separate second level relationship branch that is not anything we are worrying about showing.

Thanks for your help!

Matt

Re: Accessing M-N and 1-M related table records in Portal Pop-ups

XanderBakker — Tue, 19 May 2020 19:17:34 GMT

Hi Matt Lashendock ,

The error you obtained:

Execution Error: Runtime Error: Function Not Found: FeatureSetByRelationshipName

... is most likely related to the version of Enterprise that you are using. FeatureSetByRelationshipName was released in version 1.8 and available as of 10.8 in Enterprise. See:

Edit: There are ways to get the related records using a Filter and the field the relationship is based upon.

Re: Accessing M-N and 1-M related table records in Portal Pop-ups

RogerCleaves1 — Thu, 11 Jun 2020 15:19:17 GMT

Hi all,

I just found this blog article today, this seems to solve the 1-to-many iteration issue!

https://community.esri.com/community/gis/web-gis/arcgisonline/blog/2020/06/09/show-related-data-or-tables-in-pop-ups-with-arcade-featuresets

Re: Accessing M-N and 1-M related table records in Portal Pop-ups

XanderBakker — Thu, 11 Jun 2020 21:24:34 GMT

Hi Roger Cleaves ,

Thanks for sharing the blog, which is indeed a nice example of processing related data but this question is related to a specific version of ArcGIS Enterprise and I think the post is based on AGOL.

Re: Accessing M-N and 1-M related table records in Portal Pop-ups

RickeyFight — Mon, 05 Oct 2020 22:58:16 GMT

Xander Bakker‌

I am running 10.6.1 and was wondering if you can expand on your edit: " There are ways to get the related records using a Filter and the field the relationship is based upon."

Thanks!

Re: Accessing M-N and 1-M related table records in Portal Pop-ups

XanderBakker — Tue, 06 Oct 2020 14:10:50 GMT

Hi Rickey Fite ,

The FeatureSetBy* functions and the Filter functions were introduced at Enterprise version 10.7 in Arcade version 1.5 (see: Version Matrix | ArcGIS for Developers ). Unfortunately, with Enterprise 10.6.1 you have no possibility to do this.

Re: Accessing M-N and 1-M related table records in Portal Pop-ups

RickeyFight — Tue, 06 Oct 2020 14:32:17 GMT

Xander Bakker‌ I thought that was the case. Thank you for confirming that.

Re: Accessing M-N and 1-M related table records in Portal Pop-ups

PaulBarr — Wed, 13 Oct 2021 21:08:48 GMT

I had the same issue as you while trying to follow the online examples:

My initial attempts kept giving me the error:
"Execution Error:Runtime Error: Identifier Not Found. $map"

I managed to trace this back to whether I had added the MapService or FeatureService to my map.

MapService: $map was not found.
FeatureService: $map exists.

I was still unable to get the FeatureSetByRelationshipName to work. I am currently applying the relate using ArcGISPro when publishing the service definition. So it is not enforced by a relationship class in the underlying enterprise geodatabase, so that may be related. But I haven't got around to trialing this yet.

Re: Accessing M-N and 1-M related table records in Portal Pop-ups

MattLashendock — Tue, 22 Mar 2022 16:06:25 GMT

Our organization has since updated to Enterprise server 10.8, allowing us to access the FeatureSetByName and FeatureSetByID functions. Re-visiting this, we've been able to write code for both the M-N primary relationship and the 1-M secondary relationship, each residing in separate popups.

The following is the code that functions to return the primary relationship data for the "PI Number" identifier residing in the first table related to the primary feature class. The key in the first relationship is the "PAMS_PIN" field. The "PI_Number" field in the related table acts as the key to the second relationship.

//Read out ID of the feature var ID=$feature["PAMS_PIN"]; //Access related table var tbl1=FeatureSetByName($map,"Cadastral - Tidelands PI Numbers Composite"); //Create an SQL expression to query on ID var sql="PAMS_PIN= '" + ID + "'"; //Filter the table using the SQL expression var reldata=Filter(tbl1,sql); //Count the resulting records var cnt=Count(reldata); //Initiate a variable to hold the result var result=0; //Check for records found for ID if (cnt>0){ //Loop through related records for (var row in reldata){ //Read the ID from the related data result += row.PI_NUMBER + TextFormatting.NewLine; } } else { result = "No Activities listed"; } return result; reldata

Of note in line 24 is the use of "+=" for the result to allow for multiple records to be returned, if present, instead of only one (using only "=").

After saving the expression, it was inserted into the popup's HTML in a specific table location (See attached files).

Re: Accessing M-N and 1-M related table records in Portal Pop-ups

MagdaUsarek-Witek — Tue, 22 Mar 2022 17:45:41 GMT

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.

You can place the expression by selecting a description from one field or by selecting custom configuration and adding the expression: