Filtering based off relationship table

Question

Good afternoon. I have set up a map in which I took a feature class and joined it with a relationship table in AGOL. I wrote a bit of arcade code to make sure all data from the relationship table shows. However my issue is I would like to filter out all points that has no relationship table data to join with my points feature. So long story short, I would only like to show points with relationship data and leave out those who don't. Would any of you be able to provide me with a bit of direction on how I should handle such a task. My code is below with certain parts marked out. Thanks.

Capture.JPG

RaeHush_0-1723060770838.png

KimOllivier · Answer

Yes you can! But as pointed out the performance is terrible and limited. So you have to precalculate it to be useful on a map. Even that is painful! I have the same problem. In my case the user wanted to move the visit records to a related child table and have the location table as the parent. But some records do not have a child. Since the foreign key (a horrible GUID) is the link to the GlobalID (a read-only GUID) I needed to add missing records in the location feature layer to the visit related layer. I did this in a prototype using relates in Pro, and then in an arcpy script using dictionaries. Because arcpy does not use relates (or the tools that do are not reliable). So it works a treat in Pro because you have da.Cursors, and georelational tables. But in AGOL you have a giant object that you have to query in single REST requests that have limits on the data returned.

In my Pro example there is still some magic performed in Pandas. It finds the record with the latest date grouped by foreign key all ready to update the feature layer.

My next strategy is to go to the Visit related table with a more direct query in AGOL.You can get all the Visit records slowly by the WeedLocation record using query_related_records(). It is slow and painful and the return is a json nested mess but Pandas can unpack it. This enables me to update the featurelayer attributes that are used for symbology.

# add missing visits
# oh yeah, cannot use relates
# use keyfiles
# Creative Commons NZ 4.0 Kim Ollivier
# 10 Sept 2022
import os
import sys
import collections
import arcpy

try:
    gdb = sys.argv[1]
except IndexError:
    gdb = 'm:/project/econet/source9Sep/CAMS_weed.gdb'

if not arcpy.Exists(gdb):
    raise IOError
arcpy.env.workspace = gdb
arcpy.env.overwriteOutput = True
arcpy.AddMessage(gdb)

# "Visits_Table", "GUID_visits"  "WeedLocations", "ParentGuid" or "GlobalID"
vguid = [row[0] for row in arcpy.da.SearchCursor('Visits_Table',['GUID_visits'], "GUID_visits is not NULL")]
print('vguid:',len(vguid))
# dict of counts by GUID for inspection later
vguid_counts = collections.Counter(vguid)

sql = """GlobalID NOT IN {}""".format(tuple(vguid))
print(sql[0:60], ' ... ',sql[-42:])
arcpy.management.MakeFeatureLayer('Weedlocations','weed_no_visit_lay',sql) # use a fieldinfo to limit fields?
arcpy.management.CopyRows('weed_no_visit_lay','Visit_extra') # this adds in my name as an editor??
print("extras",arcpy.management.GetCount('Visit_extra'))
# maybe extract a dict and insert
# 
# make a new visits table to allow repeated tests
arcpy.management.Merge(['Visits_Table','Visit_extra'], 'Visits_Table_new',add_source="ADD_SOURCE_INFO")
max_dates = {}
for key, value in {r[0]:r[1] for r in 'Visits_Table'}.items():
    max_dates.setdefault(value, set()).add(key)

with arcpy.da.UpdateCursor('WeedLocations',['GlobalID','VisitCount']) as cur:
    for row in cur:
        row[1] = vguid_counts.get(row[0],None)
        cur.updateRow(row)

RussRoberts · Answer

@jcarlson is correct, currently you cannot style or filter off of a related value. This would be a VERY expensive task and would not scale.

jcarlson · Answer

I don't believe you'll be able to . Filtering in a map uses a SQL statement, and can only be done based on the selected layer's own attributes, not a related table. If you managed to "bake in" the related attributes by creating a hosted view layer, for instance, you'd then be able to access the joined attributes, but otherwise you won't be able to do what you're aiming for.