Use hosted feature service repeat "related table" data for labels/popups.

738
11
02-14-2021 11:43 PM
MichaelAshelford
New Contributor II

Data (environmental) is collected in S123 using "repeats" to identify samples (multiple) and their attributes. Data is held as an AGOL hosted feature service.

Aim: To display some attributes in the related table as a label (and/or popup) for the parent feature service, noting that the parent has the location data (dot on the map) and the related table has the desired attributes to display. The aim is to display this in Arcmap and/or ArcPro, noting that complex labels aren't supported in AGOL. 

So far, the furthest i've got is displaying related data in a popup in AGOL. The following Arcade code (which I modified from here) works in the AGOL environment (note: i'm no coding wiz! Just painfully and slowly modified existing code!)
_______________________________________________________
// Write a script to return a value to show in the pop-up.
// For example, get the average of 4 fields:
// Average($feature.SalesQ1, $feature.SalesQ2, $feature.SalesQ3, $feature.SalesQ4)
var related_table = FeatureSetById($datastore, "2");
var filter_query = "parentglobalid = '" + $feature["globalid"] + "'";
var related_data_filtered = Filter(related_table, filter_query);
var related_data_filtered_count = Count(related_data_filtered);

var output = "";
if (related_data_filtered_count > 0) {

for (var relatead_data_row in related_data_filtered) {
output += "Sum of PFAS: " + relatead_data_row.Sum_PFAS + " (mg/kg)" + TextFormatting.NewLine
+ "PFOS: " + relatead_data_row.PFOS + " (mg/kg)" + TextFormatting.NewLine
+ "PFHxS: " + relatead_data_row.PFHxS + " (mg/kg)" + TextFormatting.NewLine
+ "PFOA: " + relatead_data_row.PFOA + " (mg/kg)" + TextFormatting.NewLine
+ "6:2 FTS: " + relatead_data_row.FTS_6_2 + " (mg/kg)";
}
} else {
output = "No Related Records...";
}

return output;

________________________

This gives me these nice popups as seen below: 

MichaelAshelford_0-1613373469544.png

Now, if I open this map in ArcPro, not only does the popup now not show anything, but if I copy this code to the label preferences in Arcade language I get the following error: 

MichaelAshelford_2-1613374654370.png

I assume this is something to do with it not referencing the location, but I really have no idea?

I've tried some other things too..following the ESRI article Label Related Table  and article showing this working on c-drive hosted data I gave this python code a crack replacing the source with the URL Source of the related table (prosperities in Arc Pro) but I get an error saying that it can't open the location, see code and error below: 
______________________

import arcpy
def FindLabel ([globalid], [Field_ID]):
key1 = [globalid] # Key field in feature class
key2 = "[parentglobalid]" # Key field in related table
L = [Field_ID] # Label field in feature class
L2 = "Sum_PFAS" # Label field in related table
myDataTable = r"https://services6.arcgis.com/m84fmcvTQusi2jIj/arcgis/rest/services/service_f1f4c7601263461a9615bd85e..."
cur = arcpy.da.SearchCursor(myDataTable, [key2, L2])
for row in cur:
if str(key1) == str(row[0]):
L = L + "\n" + str(row[1])
del cur
return L
______________________

MichaelAshelford_1-1613374504409.png

Basically, I'm all out of ideas! I wouldn't think something so simple, would be so difficult! Am I on the right track? Is there a simpler way of doing this? Thanks in advance!!!

0 Kudos
11 Replies
XanderBakker
Esri Esteemed Contributor

Hi @MichaelAshelford ,

In your explanation, you mention that you want to show this text as a label. In ArcGIS Online with Arcade in the label profile, you can only access the data of the current feature and not of related data. The error message indicating that $datastore is not found is due to that. You can find more information about profiles and the functionality available in each profile here: https://developers.arcgis.com/arcade/guide/profiles/

I would also use FeatureSetByRelationshipName to access related information.

With the amount of information to display it seems to me that using a pop-up (where this functionality does work) is the logical option. If you configure this with Arcade it will only be available in ArcGIS Pro and ArcGIS Online (and Enterprise) but not in ArcMap. 

0 Kudos
MichaelAshelford
New Contributor II

Gday @XanderBakker, thanks for your response! 

Yes, I would like to/need to show related data as a label in ArcGIS Pro. The reason for this is that I need to produce static figures (PDF maps - not popups) for a report that show the locations of a sample and attributes from both the parent and related data collected in Survey123. e.g: 
Sample 10 Sample name and location taken from parent record. 
pH: 7.3 (pH units) Units taken from related table. 
Conductivity: 100.3 (ms/cm) Units taken from related table. 

As there is no way (that i'm aware of) to have visual data tables present on a figure related to points, i'm trying to create the table as a label. 

I have looked into using FeatureSetByRelationshipName, thanks. Unfortunately I still run into problems. 
When attempting to use FeatureSetByRelationshipName in ArcGIS Pro as a label I get the following error, so it looks like Arcade Label profiles don't support related data in Online or Pro :( 

MichaelAshelford_0-1613427331775.png

As a side note, the above code works in a popup in AGOL:

MichaelAshelford_1-1613427862346.png

But does not work in popups in ArcGIS Pro: 

MichaelAshelford_2-1613427929374.png

 

I'm still so surprised this isn't easy, but i'm open to all possible options. If Arcade cant do it, can Python? Surely there has to be a way to display related data on a map!!

0 Kudos
XanderBakker
Esri Esteemed Contributor

Hi @MichaelAshelford ,

Arcade in the label profile only offers access to the attribute of the feature itself and not to other features or related data. The only way to have this information in the label, it to create another field and use a field calculation to extract the data you want to show in the label. If the data is dynamic, you will have to recalculate the field to update the information or implement an Attribute Rule to take care of this.

0 Kudos
MichaelAshelford
New Contributor II

Thanks @XanderBakker and @DougBrowning - hmm, bummer :( 
I can't add fields and run calculations etc. as there are multiple related records (repeat data) related to each parent record. I'll have a bit more of a think.

I had hopes that I would be able to create table-like labels which use the location and field attributes from the AGOL/Survery123 database, and by using common job numbers, field IDs and in some cases depth, show related laboratory data in a EQuis or ESdat database. The end result would be: 
Sample 1  (taken from S123)
0.2 m | Soil type         | Fill - silty CLAY (taken from S123)
            | Sum of PFAS | 0.0023 mg/kg (taken from an external linked database, or related table)
0.4 m  | Soil type         | Fill - sandy CLAY (taken from S123)
            | Sum of PFAS | 0.0011 mg/kg (taken from an external linked database, or related table)


Looks like this is too much and i'll have to go back to making tables in excel and pasting them over the figure in adobe illustrator once the figure is in PDF format. I see ArcGIS Pro now has dynamic tables, but I don't think it is capable capable of this. 

Thanks again for your help, and i'll be on the lookout for any developments. 

 

0 Kudos
XanderBakker
Esri Esteemed Contributor

Hi @MichaelAshelford ,

That is a lot of information to show in a label. It is perfectly feasible to include this information in a pop-up though. 

0 Kudos
MichaelAshelford
New Contributor II

Hi @XanderBakker
Yes it is a lot, but unfortunately PDFs aren't dynamic so popups don't help too much :p 
PDF export is required for all environmental reports. Popups would be useful on a portal or webmap - i'll have to cross that bridge when I get to it. 

0 Kudos
XanderBakker
Esri Esteemed Contributor

Hi @MichaelAshelford ,

 

Have you thought about including the text in the layout (as a text box) and use a number to refer from the location to the corresponding text?

0 Kudos
MichaelAshelford
New Contributor II

Hi @XanderBakker , 
The Layout profile has the same globals as the Label profile, (only $feature), so I haven't looked into this any further as I can't take data from more than one table. 

0 Kudos
XanderBakker
Esri Esteemed Contributor

Hi @MichaelAshelford ,

I wasn't thinking of using Arcade in the layout profile (sorry for the confusion). If this is a single map you could generate the text in a static way and past it in the layout and use numbers as labels of the features that should point to elements in the text. If you work with a map series, this will become more complex and require coding.

0 Kudos