4

I've got an MXD with about fifty layers, and I know there is a table that is joined to at least one, possibly more, of those layers. But I don't know which one :)

I know I can easily look at a specific layer to find what (if any) joins it has. However, is there a way to get an MXD-wide overview of joins that can list all layers with joins, and what they are joined to? (I'm always a fan of shortcuts...)

PolyGeo
  • 65,136
  • 29
  • 109
  • 338
Erica
  • 8,974
  • 4
  • 34
  • 79

1 Answers1

9

You would probably like to wrap this code into a Python add-in which you could put into a button in your ArcMap UI.

There is no way to tell using arcpy whether a layer has any joins, however, this will be visible if you explore its fields. After join is established, each field will be prepended with the layer's name in the form layer_name.field_name. If there is a dot (.) in the field name, then the layer has the join.

for lyr in arcpy.mapping.ListLayers(arcpy.mapping.MapDocument('current')):
  if len([f.name for f in arcpy.ListFields(lyr)][0].split('.')) > 1:
    print lyr

Theoretically, even just accessing the OID should suffice (if you have many layers to go through, it could take extra time listing all the fields). So, just limit the field type;

for lyr in arcpy.mapping.ListLayers(arcpy.mapping.MapDocument('current')):
  if len([f.name for f in arcpy.ListFields(lyr, field_type='OID')][0].split('.')) > 1:
    print lyr

To get the name of the joined table (the one you would see under the Joins & Relates tab of the Layer Properties dialog window), you would need to iterate over the fields of each layer that has joins and find unique dataset names in the fields names.

for lyr in arcpy.mapping.ListLayers(arcpy.mapping.MapDocument('current')):
    if len([f.name for f in arcpy.ListFields(lyr, field_type='OID')][0].split('.')) > 1:
        print lyr
        print set([f.name.split('.')[0] for f in arcpy.ListFields(lyr)])
Alex Tereshenkov
  • 29,912
  • 4
  • 54
  • 119
  • This is good! I will need to add some exceptions (it doesn't like annotation layers apparently) and tweak a bit for this particular map, but it's working so far. Phew! – Erica Feb 13 '18 at 16:17
  • 2
    Ah, that's true! Adding the if lyr.isFeatureLayer should suffice I guess. – Alex Tereshenkov Feb 13 '18 at 16:19
  • There are some column names which are dot prefixed, even if only one table is involved in a layer, so there are exceptions to this rule. – Vince Feb 13 '18 at 23:43
  • I have some VB.net code to poll the layers looking for joins if that would help. The object is IDisplayRelationshipClass which is implemented by IFeatureLayer, as a bonus IDisplayRelationshipClass.RelationshipClass.DestinationClass will give you the joined 'to' data as IObjectClass (IClass) which implements ITable and IFeatureClass. That could be handy information to be able to derive but you would need to either write in .net or use ArcObjects in python to access these objects. – Michael Stimson Feb 14 '18 at 01:06
  • @Vince, wow, I did not know this. I don't think you can create a field in a geodatabase that contains a dot using native ArcGIS tools? It is considered to be an illegal character. No system fields such as Shape_Area or Shape_Length or Shape contain a dot. You can use non-ArcGIS tools to add a field .ColumnName to a gdb feature class, but then when you add the feature class to a map, the dot is automatically removed as soon as you add the join, I've just tested this. Could you please share with us how the dot could persist? Thanks! – Alex Tereshenkov Feb 14 '18 at 07:36
  • 1
    @MichaelStimson, that's right. Here https://gis.stackexchange.com/a/67568/14435 is the answer that uses comtypes to get the joined data's data name and optionally data source. – Alex Tereshenkov Feb 14 '18 at 07:38
  • It's been too many password changes ago to say which exact column names exhibited this behavior, but it wasn't an intentional process. I just created a new table in PostgreSQL with the column names which had been used by the client in Sybase, and arcpy chose to return a database.owner.fieldname name for some of the fields of the resulting tables. I think "area" was one of the names, but I ported ninety-something tables, and remember tripping over more than one such pseudo-reserved name. I worked around it by trapping for '.' in field.name and splitting the string when necessary. – Vince Feb 14 '18 at 11:27