Show related data (or tables) in pop ups with Arcade FeatureSets

33301
23
06-09-2020 06:41 AM
GeeFernando
Occasional Contributor
28 23 33.3K

Since it was introduced in December 2018, Arcade FeatureSets have made it possible to include data from several layers and tables in one ArcGIS Online pop up. However, like some of our distant relatives, it’s difficult to see the connection between the layers and tables. Today we’ll look at how we can use Arcade FeatureSets to connect a separate layer and table through common attributes, and bring all relatives to our Arcade family pop up.

For this example we'll use Groundwater level measurements published by the CALIFORNIA natural resources AGENCY.

  1. Groundwater well station locations (Feature Layer)

  2. Groundwater levels - monthly mean (Related Data - Table)

    • itemId: 426460a6ae7c47b5acb9a64294bd3dcb layerId: 0

Access 'Groundwater levels - monthly mean' table as a FeatureSet

Before we get started, I suggest you take a look at the following web map to familiarise yourself with the end result we're working towards.

click on features to see related data fetched from the Groundwater levels - monthly mean table.

If you want to follow along, save a copy of the web map to your account (you'll have the option to open and save the web map in Map Viewer Beta or Map Viewer Classic). Afterwards, add an Arcade attribute expression to the Groundwater well station locations layer and use the following code snippet to bring in all features from the Groundwater levels - monthly mean (Related Data - Table).

Note: if you'd prefer to copy and paste the code, the entire code snippet can be found at the end of this blog post.

Refer to this blog if you'd like to learn more about FeatureSetByPortalItem() and how to bring in features from another layer or table by using its itemId and layerId.

Filter related features by using a common attribute

Whether you like it or not, we all share common attributes with our relatives. This is also the case with related data. In our example the common attribute is the Station Id, where the field names are called STATION for both the feature layer and related table. Use the following code snippet to filter relatedData.

Refer to this blog if you'd like to learn more about the Filter() function and how to filter related records by using a common attribute.

Pro tip - Make sure to take advantage of the   Test   button to check related features for different Station Ids.

Note: if you're working with related tables in  Survey123  the 2 common field names are likely to be globalid for the feature layer, and parentglobalid for the related table. Attached below is an example filterStatement.

var globalid = $feature.globalid
var filterStatement = 'parentglobalid = @globalid'

Sort related features by oldest to newest

Just like organising table arrangements for a family event, it is important that related data in popups are sorted in some order. Use the OrderBy() function to achieve this in Arcade FeatureSets.

Build the pop-up string by iterating through all related features

Use the following For loop to iterate through all features in the FeatureSet and build the popup string. Again, take advantage of the  Test  button to check popupString results for different Station Ids.

Refer to this blog if you'd like to learn more about working with data inside FeatureSets.

Dealing with empty attributes

Finally, use the DefaultValue() function to replace any empty attributes with a default value or text.

Putting it all together

Here is the entire code to get your party started.

// Acess 'Groundwater Levels Monthly Mean' table as a FeatureSet
var portal = Portal("https://www.arcgis.com")
var waterLevels = FeatureSetByPortalItem(portal,
    "426460a6ae7c47b5acb9a64294bd3dcb", 0, ['STATION', 'MSMT_DATE',
    'RPE_WSE', 'GSE_WSE', 'WSE'])

// Filter related features by using a common attribute
var STATION = $feature.STATION
var filterStatement = 'STATION = @STATION'

// Related features as a variable
var relatedData = Filter(waterLevels, filterStatement)

// Sort related features by oldest to newest
var relatedDataSorted = OrderBy(relatedData, 'MSMT_DATE ASC')

// Build the pop-up string by iterating through all related features
var popupString = ''
for (var f in relatedDataSorted){
    
    popupString += Text(f.MSMT_DATE, 'MMMM Y') + TextFormatting.NewLine +
    
        "Depth to water surface (ft): " +
        DefaultValue(f.RPE_WSE, 'no data') + TextFormatting.NewLine +
        
        "Depth below ground surface (ft): " +
        DefaultValue(f.GSE_WSE, 'no data') + TextFormatting.NewLine +
        
        "Water Surface Elevation (ft): " +
        DefaultValue(f.WSE, 'no data') + TextFormatting.NewLine +
        TextFormatting.NewLine
}

DefaultValue(popupString, 'No measurements to show')

Who will you invite to your Arcade family pop up party?

Challenges

Please use the comments section below to post answers to the following questions.

  1. How would you structure your expression to only show information from the last reading?

  2. How would you convert the measurements from feet to metres?

  3. Bonus round: How would you structure your code to show Quality Codes along with your Groundwater level readings?

Cheers! - Gee Fernando

23 Comments
Ying_BoWang
New Contributor II

2. How would you convert the measurements from feet to metres?

Multiply by each WSE value by 0.3048. Change the text to reflect the new unit.

// Build the pop-up string by iterating through all related features
var popupString = ''
for (var f in relatedDataSorted){
    
    popupString += Text(f.MSMT_DATE, 'MMMM Y') + TextFormatting.NewLine +
    
        "Depth to water surface (m): " +
        DefaultValue(f.RPE_WSE*0.3048, 'no data') + TextFormatting.NewLine +
        
        "Depth below ground surface (m): " +
        DefaultValue(f.GSE_WSE*0.3048, 'no data') + TextFormatting.NewLine +
        
        "Water Surface Elevation (m): " +
        DefaultValue(f.WSE*0.3048, 'no data') + TextFormatting.NewLine +
        TextFormatting.NewLine
}

Return DefaultValue(popupString, 'No measurements to show')

3. Bonus round: How would you structure your code to show Quality Codes along with your Groundwater level readings?

Use When function before the for loop

// Build the pop-up string by iterating through all related features
var popupString = ''
for (var f in relatedDataSorted){
    
    var RPE_WSEQualityCode = f.RPE_WSE_QC;
    var RPE_WSEQualityCodeDescrip = When(RPE_WSEQualityCode == 1, 'Good data', RPE_WSEQualityCode == 2, 'Good quality edited data', RPE_WSEQualityCode == 70, 'Estimated Data', RPE_WSEQualityCode == 151, 'Data Missing', 'Quality Code description not found');
    
    popupString += Text(f.MSMT_DATE, 'MMMM Y') + TextFormatting.NewLine +
    
        "Depth to water surface (ft): " +
        DefaultValue(f.RPE_WSE, 'no data') + ", Quality Code: " + RPE_WSEQualityCodeDescrip + TextFormatting.NewLine +
        
        "Depth below ground surface (ft): " +
        DefaultValue(f.GSE_WSE, 'no data') + TextFormatting.NewLine +
        
        "Water Surface Elevation (ft): " +
        DefaultValue(f.WSE, 'no data') + TextFormatting.NewLine +
        TextFormatting.NewLine
}

return DefaultValue(popupString, 'No measurements to show')
GeeFernando
Occasional Contributor

Thanks Ying Bo Wang‌ for sharing your answers with the GeoNet community. I really appreciate it.

Any ideas on question 1

Gee

KenBuja
MVP Esteemed Contributor

Here's one way, using the new Template Literals

var relatedDataSorted = OrderBy(relatedData, 'MSMT_DATE DESC')

// Build the pop-up string by iterating through all related features
var popupString = ''
if (Count(relatedDataSorted) > 0) {
    var f = relatedDataSorted[0]
    
    popupString = `${Text($f.MSMT_DATE, 'MMMM Y')}
Depth to water surface (ft): ${DefaultValue($f.RPE_WSE, 'no data')}        
Depth below ground surface (ft): ${DefaultValue($f.GSE_WSE, 'no data')}
Water Surface Elevation (ft): ${DefaultValue($f.WSE, 'no data')}`

}

DefaultValue(popupString, 'No measurements to show')‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
JoeStefanoni
New Contributor III

I was able to set everything up similar to how you did using the following code specific to my datasets:

var substances = FeatureSetByName($map,"Environmental_NJEMS - Unknown Source Contaminants")
return substances
// Filter related features by using a common attribute
var INCIDENT_ID = $feature["INCIDENT_ID"]
var filterStatement = 'INCIDENT_ID = @SUBST_IMPACT_ID'

// Related features as a variable
var relatedData = Filter(substances, filterStatement)

// Sort related features by oldest to newest
var relatedDataSorted = OrderBy(relatedData, 'SUBST_IMPACT_ID')

// Build the pop-up string by iterating through all related features
var popupString = ''
for (var f in relatedDataSorted){

popupString += Text(f.INCIDENT_ID) + TextFormatting.NewLine +

DefaultValue(f.SUBST_DESC, 'no data') + TextFormatting.NewLine +

DefaultValue(f.SUBST_QTY, 'no data') + TextFormatting.NewLine +

DefaultValue(f.UNIT, 'no data') + TextFormatting.NewLine +
TextFormatting.NewLine
}

DefaultValue(popupString, 'No measurements to show')

and when I test it I get the following (which seems correct):

ResultValueResult

OBJECTIDSUBST_IMPACT_IDSUBST_STATUSSUBST_DESCCAS_NUMSUBST_QTYUNITSUBST_QTY_TYPEIMPACTSUBST_STATE
160255KnownTETRACHLOROETHANE2532220712ppbActualLandLiquid
2243563KnownODORS PETROLEUM0unknownUnknownAirGas
3325919KnownWATER (GROUND WATER)0unknownUnknownLandLiquid
4332493KnownLEAD743992146.5ppbActualLandLiquid
5334419KnownTETRACHLOROETHYLENE12718422.6ppbActualLandLiquid
6334815KnownPERCHLOROETHYLENE127184UnknownLandLiquid
7339869KnownTETRACHLOROETHANE253222074ppbActualLandSolid
8340360KnownVOC'SUnknownLandLiquid
9344195KnownVOC'SUnknownLandLiquid
10351494KnownTETRACHLOROETHYLENE127184UnknownLandLiquid
11352755KnownTRICHLOROETHYLENE790161.8ppbActualLandLiquid
12352755KnownTETRACHLOROETHYLENE1271842.2ppbActualLandLiquid
13353476KnownTETRACHLOROETHANE253222070unknownUnknownLandLiquid
14379271KnownUNKNOWN LIQUIDUnknownLandLiquid
15380589KnownPERCHLOROETHYLENE1271840unknownUnknownLandLiquid
16381255KnownVOC'SUnknownLandLiquid
17382651KnownOIL OTHERUnknownLandLiquid
18386939KnownTRICHLOROETHYLENE79016587ppbActualLandLiquid
19386939KnownTETRACHLOROETHYLENE12718424500ppbActualLandLiquid
20390719KnownUNKNOWN LIQUIDUnknownLandLiquid
21393537KnownOIL FUEL #2UnknownLandLiquid
22393561KnownCARBON TETRACHLORIDE562351.1ppbActualLandLiquid
23393633KnownVOC'S0unknownUnknownLandLiquid
24396455KnownPETROLEUM PRODUCTS0unknownUnknownLandSolid
25402883KnownPETROLEUM PRODUCTSUnknownLandLiquid
26402893KnownTETRACHLOROETHYLENE1271848.55ug/lActualLandLiquid
27402893KnownTRICHLOROETHYLENE790160.9400000000000001ug/lActualLandLiquid
28405583KnownVOC'SUnknownLandLiquid
29409239KnownTRICHLOROETHYLENE790162.4ppbActualLandLiquid
30409239KnownTETRACHLOROETHYLENE12718451ppbActualLandLiquid

What I don't know how to do is incorporate it into my customized pop-up box?  When I add the expression to my pop-up, I end up losing all the pop-up display information.  Please advise.

GeeFernando
Occasional Contributor

Hi Joe Stefanoni‌,

Delete or Comment out - Line 2

   // return substances

Hopefully, that'll do the trick

MatejVrtich
Esri Contributor

Hi Gee,

Thank you for sharing your work.

It's a shame that charts inside a popup cannot reference an Arcade expression with a FeatureSet output.

If anybody else is missing this, please vote for https://community.esri.com/ideas/18611 .

Thanks,

Matej

WTCCWyGISC
New Contributor

Hi Gee - I am having the same problem as described above, but have deleted my "return popupString".  Here is my code, which works fine in test mode but my popup only shows the expression label and then no value:

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

// 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)@gee

var portal = Portal("https://services.wygisc.org/portal")
var speciesRelate = FeatureSetByPortalItem(portal,
"6a0c8bcd1d164d99b76f162f140212f8", 0, ['Species', 'Taxa',
'Occurrence_Potential', 'BLM_Status',
'Texas_________________Status', 'Federal_Status', 'Habitat',
'Code'])

// Filter related features by using a common attribute
var HabCode = $feature.HabCode
var codeStatement = 'Code = @HabCode'

//Related features as a variable
var relateData = Filter(speciesRelate, codeStatement)

//build popupString by iterating through all related features
var popupString = ''
for (var f in relateData){

popupString += "Species: " + Text(f.Species) + TextFormatting.NewLine +

"Taxa: " + f.Taxa + TextFormatting.NewLine +

"Occurrence Potential: " + f.Occurrence_Potential + TextFormatting.NewLine +
TextFormatting.NewLine 
}

Thoughts?

Thanks,

Shawn

NurkuisaRametov1
New Contributor II

How to create dynamic line chart with date and surface level in popup?

VirginiaMVanderVeen
New Contributor

The output of my related table field is a path to a sewer inspection document. When I click a feature in my feature class, everything displays great, but as expected, my related table field just looks like "https://location1/location2.pdf." Is there a way to make that a hyperlink?

StephenKaranja
New Contributor III

1. How would you structure your expression to only show information from the last reading?

// Sort related features in descending order (newest to oldest)
var relatedDataSorted = OrderBy(relatedData, 'MSMT_DATE DESC');

var popupString = '';

if (Count(relatedDataSorted) > 0) {
  // Get the first feature in the sorted FeatureSet
  var f = First(relatedDataSorted);

  // Build the pop-up string
  popupString = Text(f.MSMT_DATE, 'MMMM Y') + TextFormatting.NewLine +

    "Depth to water surface (ft): " +
    DefaultValue(f.RPE_WSE, 'no data') + TextFormatting.NewLine +

    "Depth below ground surface (ft): " +
    DefaultValue(f.GSE_WSE, 'no data') + TextFormatting.NewLine +

    "Water Surface Elevation (ft): " +
    DefaultValue(f.WSE, 'no data') + TextFormatting.NewLine +
    TextFormatting.NewLine;
}

DefaultValue(popupString, 'No measurements to show');
RyanTucker
New Contributor III

Is there anyway to make fields from the  related table display conditionally?
I only want the Legal Description fields to appear in the pop-up if they are not null. Is it even possible?

I've got the code working:

// Access 'Parcels related AssessorSBF' table as a FeatureSet 
var relatedrecords = (FeatureSetByRelationshipName($feature, 'AssessorSBF', ['PARCELID','SA_FullAddress','SA_CityStateZip','First_Owner_Assee_Name','First_Owner_Name_Overflow','MA_FullAddress','MA_CityStateZip','Legal_Description_Line1','Legal_Description_Line2','Legal_Description_Line3','Legal_Description_Line4','Legal_Description_Line5','Legal_Description_Last']));

// Build the pop-up string by iterating through all related features
 var popupString = '' 
for (var f in relatedrecords){ 

popupString += 
TextFormatting.NewLine + "APN: " + Text(f.PARCELID) + TextFormatting.NewLine + 
DefaultValue(f.SA_FullAddress, '') + TextFormatting.NewLine + 
DefaultValue(f.SA_CityStateZip, '') + TextFormatting.NewLine + TextFormatting.NewLine + 
"Owner Info: " + TextFormatting.NewLine + DefaultValue(f.First_Owner_Assee_Name, '') + TextFormatting.NewLine + 
DefaultValue(f.First_Owner_Name_Overflow, '') + TextFormatting.NewLine + DefaultValue(f.MA_FullAddress, '') + TextFormatting.NewLine + 
DefaultValue(f.MA_CityStateZip, '') + TextFormatting.NewLine + TextFormatting.NewLine + "Legal Info: " + TextFormatting.NewLine + 
DefaultValue(f.Legal_Description_Line1, '') + TextFormatting.NewLine + 
DefaultValue(f.Legal_Description_Line2, '') + TextFormatting.NewLine + 
DefaultValue(f.Legal_Description_Line3, '') + TextFormatting.NewLine + 
DefaultValue(f.Legal_Description_Line4, '') + TextFormatting.NewLine + 
DefaultValue(f.Legal_Description_Line5, '') + TextFormatting.NewLine + 
DefaultValue(f.Legal_Description_Last, '') + TextFormatting.NewLine + 
TextFormatting.NewLine 
} 
DefaultValue(popupString, 'No Addess to show')
RyanBohan
Occasional Contributor III

Is there a way to add bold or color tags in the string return?

bdewitt_olsson
New Contributor III

I used a modified version of this and it works great in map viewer but the fields don't populate in Field Maps. It just shows a dash. I have another simpler attribute expression in the same feature that populates in Field Maps just fine. Anyone know why this particular expression doesn't work in Field Maps?

JasonCyphers
Occasional Contributor III

I modified the form to suit my data, but cannot get the numbers to format correctly.  Any ideas?:

JasonCyphers_0-1652454552921.png

 

 

bdewitt_olsson
New Contributor III

@JasonCyphers The second parameter in the DefaultValue function sets what will appear if there is no data. It is not used for formatting. Try replacing

DefaultValue(f.mech_uncor_reading, '#,###')

with

DefaultValue(Text(f.mech_uncor_reading, '#,###'))

JasonCyphers
Occasional Contributor III

@bdewitt_olsson perfect, thanks.  

I got an error when using "DefaultValue(Text....))", but using "Text(f.mech......)" worked.

JasonCyphers
Occasional Contributor III

Next question...  How would you filter the related records to only show X number of related records (if related table records are obtained every month, but I only wanted to show the past two months, what expression would I need to use to filter that?)

bdewitt_olsson
New Contributor III
var filterStatement = 'STATION = @STATION'

You would need to add that to the filterStatement string (above), which is just a SQL expression. Not sure off the top of my head how to grab the past 2 months in SQL but I'm sure it can be done.

EOSEcology
New Contributor

This was epic!!  Thank you @GeeFernando 

Blevins_Mark
Occasional Contributor

 @GeeFernando  This is great. If one of my popup items is a website url, how can i get that recognized as a clickable hyperlink in the popup? I used your example above and everything worked great, except that I have a url field that is just being read as unformatted text, instead of a hyperlink. Thanks!

ZachBodenner
MVP Regular Contributor

This is such a super helpful article and really fills in a gap that ESRI hasn't seen fit to address properly. Thanks for your hard work and guidance Gee! I'm running into one problem though that I hope someone here can help me with: When I tart to build the popup string, I have no problems with the code running properly, but it seems to return a maximum of four attribute fields. I copy and paste several more instances of the attributes in the popup string but they don't show. I've tried re-ordering them as a test, and whatever order they are in, only the first four show. See example code block and resulting screenshot below:

 

// 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)
//set variables. First variable is the unique ID that is shared by the primary and related data. Second variable is calling the related forms as a FeatureSet
 
var codeGlobal = $feature.GlobalID;
var relatedForms = FeatureSetById($map, "189ffea4bbf-layer-175")
 
//create a filter statement where the maintenance form GUID = the Tree Inventory GlobalID
 
var filterStatement = "CompRel = @codeGlobal"

//a maintenance form equals the table run through the filter
 
var inspForm = Filter(relatedForms, filterStatement)
 
// Sort related features by oldest to newest
var relatedDataSorted = OrderBy(inspForm, 'InspDate ASC')
 
// Build the pop-up string by iterating through all related features. Add or subtract new lines in the popupString as needed from the related form.
var popupString = ''
for (var f in relatedDataSorted){
    
    popupString += Text(f.InspDate, 'MMMM DD Y') + TextFormatting.NewLine +
    
        "Inspection Type: " +
        DefaultValue(f.InspType, '') + TextFormatting.NewLine +
        
        "Inspection by: " +
        DefaultValue(f.InspBy, '') + TextFormatting.NewLine +
        
        "Findings: " +
        DefaultValue(f.Findings, '') + TextFormatting.NewLine +
        TextFormatting.NewLine

        "Investigation forwarded to: " +
        DefaultValue(f.FwdTo, 'Not forwarded') + TextFormatting.NewLine +
        TextFormatting.NewLine
 
        "Forward date: " +
        DefaultValue(text(f.FwdDate, 'MMMM DD Y'), '') + TextFormatting.NewLine +
        TextFormatting.NewLine
 
        "Owner action required: " +
        DefaultValue(f.OwnAction, 'None at this time') + TextFormatting.NewLine +
        TextFormatting.NewLine

        "Owner action deadline: " +
        DefaultValue(text(f.ActDate, 'MMMM DD Y'), 'None at this time') + TextFormatting.NewLine +
        TextFormatting.NewLine

        "Date letter sent: " +
        DefaultValue(text(f.LetterDate, 'MMMM DD Y'), 'Letter not yet sent') + TextFormatting.NewLine +
        TextFormatting.NewLine

        "Date of final inspection: " +
        DefaultValue(text(f.FinalDate, 'MMMM DD Y'), 'Final inspection not yet conducted') + TextFormatting.NewLine +
        TextFormatting.NewLine

        "In compliance?: " +
        DefaultValue(f.Comply, '') + TextFormatting.NewLine +
        TextFormatting.NewLine

        "Action taken by city: " +
        DefaultValue(f.CityAct, 'None') + TextFormatting.NewLine +
        TextFormatting.NewLine

        "Date forwarded to city attorney: " +
        DefaultValue(text(f.AttyDate, 'MMMM DD Y'), '') + TextFormatting.NewLine +
        TextFormatting.NewLine    
}
return popupString

//the default return is "Norelated forms"

 

ZachBodenner_0-1692279186712.png

 

Any ideas why that might be?

KeFAnda
New Contributor II

@ZachBodenner I believe this is happening to you because you're placing the Arcade expression within the Fields List content. You can place this expression within a text content and have all the inspections shown. hope this helps!

PeterTQFES
Occasional Contributor

In Oct 2023 is this article relevant?  Can you not create a chart in a feature layer using content from a related table? Do I still have to still use Arcade?