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

3492
8
06-09-2020 06:41 AM
Occasional Contributor
12 8 3,492

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

8 Comments
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')
Occasional Contributor

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

Any ideas on question 1

Gee

MVP Honored 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')‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
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.

Occasional Contributor

Hi Joe Stefanoni‌,

Delete or Comment out - Line 2

   // return substances

Hopefully, that'll do the trick

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  .

Thanks,

Matej

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

New Contributor II

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

Labels