Accessibility of related records for symbology (similar to pop-ups)

[nadja]

We have a hosted feature layer in ArcGIS Online with a related table. We would like to access these related records in an attribute expression for the symbology. For the Pop-ups, it is possible to access these records, but not for the symbology.

For the Pop-ups, it is possible to run the following code, click done and to use the result of the expression in the popup. The exact same code can be run for the symbology using F5, but it’s not possible to click Done to run it "properly" and the reference to the related records is marked as an error (line 2).

We'd like to suggest, that esri extends the function from the pop-up to the symbology to enhance the systems consistency.

[KarynaB]

I was just about to post an idea like this, it would be a big help to those trying to symbolize related record information in field maps (where using a dynamic joined layer view is not an option)!

[ChristopherCounsell]

https://developers.arcgis.com/arcade/guide/profiles/

Esri is aware of this but highlights that symbolising or labeling all features from a related table would be incredibly resource intensive, hence why FeatureSets are not available for these profiles.

It's a great idea but technically it would not go down well.

[TamT]

Would be such a help to access the related record info for symbolization. Thanks, Esri, for pushing this!

[Kevin_MacLeod]

It would be possible in theory, provided reasonable business logic. For example logic choosing which record to symbolize. You can even symbolize them all. I join the points to their related table and publish and then all the related fields show in the popup like if they were a real field. The one downside I discovered is attachments like photos don't come through from a joined layer. Kind of a separate issue. And with HTML/CSS in ExB/WAB or Arcade (if and when it can ever render custom HTML/CSS) there could be a way around that. Nevertheless, the workaround of joining data to its related table is rather inelegant and not everyone can do that for various reasons, such as if data is hosted.

Related data continues to be a spot needing further product development. I hope 2024 it can be a highlight for development resources. Field apps and related data are the backbone of GIS, and where a lot of vital data come from. However, while simple conceptually; displaying related datasets in web apps and visualizing the data remains cumbersome in practice.

[aaron_tob]

Thanks everyone for the valuable feedback on this. While the full capability of symbolizing / labeling on related records appears to be technically challenging, it would be nice to see partial implementation of this in the symbology expression builder in AGOL. Symbolizing on the total number of related records for each feature could be a useful visual, or symbolizing on the oldest or newest related record for example.

[SerjStol]

This is such a bummer that I can't access an attribute value from another layer to drive symbology... Maybe just let us have a query of one feature by value? Or just get one feature returned from geometry operations? 

[LinzMess]

@aaron_tob I agree! I have had several instances where I would like to symbolize features in a web map by information contained in related tables. I admit I do not understand the technical challenges with implementing this type of feature, but if these challenges would be reduced by filtering for the most recent record in a related table and using that record for symbology for features, this would satisfy my needs perfectly! A joined hosted feature layer will not work for me as I need the symbolized layer to be offline capable for use in FieldMaps.

[aaron_tob]

@LinzMess , thanks for the note. I've had luck in the past with the something like this:

To achieve a 'symbolize by most recent related record' workaround, the best approach is to use a Python script (with the ArcGIS API for Python or ArcPy) that updates a field in your parent feature layer based on values from its related table (avoids limitations of ArcGIS Online joins / joined view layers).

A script would:

1. Connect to ArcGIS Online
2. Access your hosted feature layer and its related table
3. Loop through records in the related table
4. Group by the parent feature's Global ID
5. Find the most recent related record per feature (based on a timestamp)
6. Update a field in the parent layer with a value (e.g., status or condition) from the latest record

You can symbolize your parent layer using this calculated field. And because it's just a Python script, you can set it to run by itself with Windows Task Scheduler or similar. No need to open ArcGIS Pro to do things manually.

Requirements for this to work:

1. Proper GlobalID relationship between parent layer and related table
2. A field in the parent layer to store the value (e.g. LatestCondition)
3. Ability to edit the hosted feature layer (ideally, you're the owner of it)

Example script:

from arcgis.gis import GIS
from datetime import datetime

################################################################
#### FEATURE LAYER, TABLE, FIELDS ##############################
################################################################

# Replace with your actual item ID and indexes from REST page
# Indexes will likely just be 0 or 1 or 2.. just check to make sure you're pointing to the correct layer and table
item_id = '123456789abcdefg'
parent_layer_index = 0  # Check your layer's REST page under "Layers:" section
related_table_index = 0  # Check your layer's REST page under "Tables:" section

# Field names
parent_globalid_field = "GlobalID"
related_parent_globalid_field = "ParentGlobalID"
related_date_field = "Date"
related_condition_field = "Condition"
parent_latest_condition_field = "LatestCondition"

################################################################
#### ACCESS AGOL / FEATURE LAYER ###############################
################################################################

# Connect to AGOL
gis = GIS('home')  # make sure you're signed into Pro and set python interpreter in vscode to use the ArcGIS Pro Python environment (or ideally a clone of it)
feature_collection = gis.content.get(item_id)  # your hosted feature layer
parent_layer = feature_collection.layers[parent_layer_index]  # Get layer by index
related_table = feature_collection.tables[related_table_index]  # Get table by index

################################################################
#### ACCESS RELATED RECORDS ####################################
################################################################

# Query related table and store most recent value per parent
related_features = related_table.query(where="1=1", out_fields=f"{related_parent_globalid_field},{related_date_field},{related_condition_field}").features
latest_by_parent = {}  # stores entries in this form -> {parent_globalid: {"date": <datetime>, "value": <object>}}

for feature in related_features:
    attrs = feature.attributes
    parent_globalid = attrs[related_parent_globalid_field]
    date_str = attrs[related_date_field]
    condition_value = attrs[related_condition_field]
    
    # Handle potential None values
    if parent_globalid is None or date_str is None:
        continue
        
    date_obj = datetime.fromisoformat(date_str.replace("Z", "+00:00"))
    if parent_globalid not in latest_by_parent or date_obj > latest_by_parent[parent_globalid]["date"]:
        latest_by_parent[parent_globalid] = {"date": date_obj, "value": condition_value}

################################################################
#### UPDATE PARENT RECORDS #####################################
################################################################

# Query parent layer to identify updates needed
parent_features = parent_layer.query(where="1=1", out_fields=f"{parent_globalid_field},{parent_latest_condition_field}").features
updates = []

for parent_feature in parent_features:
    parent_globalid = parent_feature.attributes[parent_globalid_field]
    current_condition = parent_feature.attributes.get(parent_latest_condition_field)
    
    if parent_globalid in latest_by_parent and current_condition != latest_by_parent[parent_globalid]["value"]:
        parent_feature.attributes[parent_latest_condition_field] = latest_by_parent[parent_globalid]["value"]
        updates.append(parent_feature)

# Push updates back to the service
if updates:
    result = parent_layer.edit_features(updates=updates)
    print(f"Updated {len(result['updateResults'])} records.")
else:
    print("No updates needed.")

To scale it up a bit, you could wrap this in a Tkinter GUI with entry fields for the variables (Item ID, Layer and Table Index, Field Names), or make it a script tool and run it from ArcGIS Pro. Or store all layer / field info in a JSON file and read it into the script if you want to run it on multiple layers.  So many possibilities...

Cloning Python Environment in ArcGIS Pro 

Setting a Python Interpreter in VSCode 

Hope this helps!

[rzinsight]

@nadja 

I ran into the same issue since I also need to collect data offline in Field Maps. What worked for me was creating a new field in the parent layer and using Calculate Field to bring in data from the related table. You can easily do this with an Arcade expression.

Here’s the Arcade expression I used to pull the Last Inspection Date from the related table:

var relatedRecords = FeatureSetByRelationshipName($feature, "Your_RelatedTable_Name_Here"); var latestRecord = First(OrderBy(relatedRecords, "Field_Name_Here")); return latestRecord.Field_Name_Here;

[apgis16]

@rzinsight That only works for static data though, correct? If a user changes the value in the related table in the field, the symbology won't automatially change until you run that calculation again?