An Arcpy method for dealing with related records

10-18-2013 07:55 AM
Status: Open
Labels (1)
New Contributor III

Relationship classes are great because they allow relationships between two datasets to persist between ArcMap sessions.  

When dealing with relationships in scripts generally I create two separate loops in order to retrieve related records.  The first loop iterates a spatial file's primary key, while a second, nested loop iterates on the tabular file's foreign key.  A list of related records can be created where these two keys match.

An arcpy method that notes the relationship between the two datasets and allows the user to pull out related records more easily would be helpful.  Perhaps the relationship class could be used as a parameter along with the two datasets since the relationship class already contains the logic for how the two datasets are related.

Hi Roy
Can you describe how you picture the function being used in your scripts?  For example do you want a recordset object being returned by the function?  That could be converted into an in memory table.  Is your workaround not doing what you need?

Having to reconstruct the relationship class programmatically in Python is a lot of overhead (two nested search cursors? yipes) to get a layer that I can then do spatial queries on. The described workaround works, but not efficiently. This is a good idea.
ArcObjects provides functions to quickly access related records in another table when selecting some in one table, through existing relationship classes. Cannot find equivalent in ArcPy. Cannot understand why we have to reinvent the wheel since the relationship is already there. It would be great if that's exposed via ArcPy. 

Still miss RESELECT KEYFILE in ArcPlot which would do a similar thing. Select records in table 1 and migrate that selection to another table through a pair of key files in the source and destination table, regardless of 1-M,M-1,or M-N cardinality. Workflows exist in geoprocessing but most are clunky.

Implementing this with relates would be very consistent and elegant and also allow the use of stored relationship classes and on the fly relationships created with Add Relate in Pro.

cc: management:

Subject: maybe y'all need to hire some more devs...

It's been six years since this idea was posted. It says it's been "reviewed" ... How is this not already a thing? It would be so useful and would be one of the first things I would think of when designing the CONCEPT of relationship classes.

...And come to think of it, I sincerely believe any reasonable person would want the ability to pull all related records with an arcpy function. 

Case in point, I am building out our entire water treatment plant in ArcMap 10.6. We have well over a 1000 assets that need to be tracked. Some of the relationships are up to four levels deep, possibly more.


TreatmentSystem [Polygon]  -> ChemSystem [Table] -> NaOh [Table] -> AuxTank [Table] -> Analyzer

What should the programmer do, write loops on loops on loops -- as the OP mentioned? I couldn't possibly think of a more redundant and inefficient query if I tried.