No Arcpy Method do Determine a Layer's Joins

[RobertStevens]

The subject says most of it.

Years have gone by, and it is still not possible in using the Python Arcpy classes and functions to determine whether a map layer (or layer file) has any joins, still less what datasets are joined.

Nor is there such a method in the ArcPro equivalent to Arcpy.

[DanPatterson_Retired]

The substitute approaches aren't a substitute but 

Raumpatrouille: ArcPy - find layers with joins 

just recommends adding a RemoveJoin_management in a try/except code block to check to see if one exists or not.

[RobertStevens]

Dan

Ty for your, as always, helpful reply to my posting on GeoNet.

I have been searching the ArcMap documentation.

I cannot find any reference to an API named "RemoveJoin_management"

And someone else referred to some other Python API with a name

ending in_managment which I also don't find.

Do you happen to know where these can be found?

[DanPatterson_Retired]

I search arcgis Pro's help

This a search on Remove Join ... %20 is put in between the two words

https://pro.arcgis.com/search/?q=  Remove%20Join  &p=0&language=en&product=arcgis-pro&version=&n=15&...

When I click on the most likely link, the link changes to

https:  //pro.arcgis.com/en/pro-app/tool-reference/data-management/  remove-join.htm

which shows as this.

Remove Join—Data Management toolbox | ArcGIS Desktop 

All api's which use tools in arctoolbox have similar links, but the best help descriptions are those for ArcGIS Pro, so even if I am using the arcgis 1.7 api for tool information, If I know they are just calling an arcpy/arctoolbox function, I just search for its help.

[RobertStevens]

I don't find that API documented anywhere.

Regardless, it is a really clunk "solution".

I don't understand ESRI.It seems as though so much of their stuff is incomplete. Why would anyone create a python based collection of classes to provided a relatively simple API and then leave out something this basic. Not only leave it out, but leave it out for years.

A continuing problem I have with their stuff is that I have a map layer with a join to some kind of external file. For some reason that file gets moved/renamed/whatever. The join disappears but then so also would the symbology that one had carefully created. Symbology in Arcmap is not especially good. From what I have seen, it is better in Arcpro.

Documentation could be improved also. Sure, they give lsists of arguments, meaning etc. But more is needed. For instance: let's say I have a layer joined to another layer. Offline (ie not within a map) I replace that joined layer's underlying dataset. Question: will new rows added to that dataset now show up in the map? In other words, when is that join accomplished. At creation time? Or after each attempt to draw the layer? Or only when the map is opened? These are important subtle questions. I have never been able to find answers to them except by experimenting.

My overall impression is that ESRI is flush with geographers, but good programmers there are thin on the ground.

[DanPatterson_Retired]

the api largely implements tools that Pro uses or implements arcpy stuff if the user has a license... if not, it tries its best with shapely, hence, the Pro help is sometimes preferable to the api docs.

As for moving this forward, the only monitored option is

ArcGIS Ideas‌ 

maybe Kory Kramer‌ has seen this or similar or can flag the appropriate team.

[JoshuaBixby]

Robert Stevens‌, I am sharing this with Python‌ since this is an ArcPy question and not ArcGIS API for Python.

Starting with ArcGIS Pro 2.4, you can determine whether any layers have joins by using the new Python CIM access—ArcPy | ArcGIS Desktop.  Once you have a reference to a specific layer, you can call getDefinition method to return a CIM object.  From there, check for the existence of a 'joinType' attribute in featureTable.dataConnection.

[JeffMoulds]

Yes, you can use the CIM to get the join properties. However, you can also just use the layer's connectionProperties. The connectionProperties dictionary contains all the information that defines the join(s). Keep in mind that layers can have zero, one or many joins. You can use a recursive function to handle zero, one or many joins:

import arcpy

def ListJoinsConProp(cp, join_count=0):
    if 'source' in cp:
        if 'destination' in cp:
            print('       Join Properties:')
            print('         ', cp['destination']['connection_info'])
            print('         ', cp['destination']['dataset'])
            join_count += 1
            return ListJoinsConProp(cp['source'], join_count)
    else:
        if join_count == 0:
            print('       -> no join')
        
# join inventory (using the layer.connectionProperties dictionary)
aprx = arcpy.mp.ArcGISProject(r"C:\Temp\fgdb_012joins.aprx")
m = aprx.listMaps()[0]
for lyr in m.listLayers():
    print(f"LAYER: {lyr.name}")
    if lyr.supports("dataSource"):
        cp = lyr.connectionProperties
        if cp is not None:
            ListJoinsConProp(cp)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Using the CIM does have one advantage - the CIM contains the join name, whereas the connectionProperties dictionary does not. Here is how to do it with the CIM:

import arcpy

def ListCIMJoins(c, join_count=0):
    if hasattr(c, 'sourceTable'):
        print(f'       Join Name: {c.name}')
        print(f'       Join Properties:')
        print(f'         {c.destinationTable.workspaceConnectionString}')
        print(f'         {c.destinationTable.dataset}')
        join_count += 1
        return ListCIMJoins(c.sourceTable, join_count)
    else:
        if join_count == 0:
            print('       -> no join')

# join inventory (using CIM)
aprx = arcpy.mp.ArcGISProject(r"C:\Temp\fgdb_012joins.aprx")
m = aprx.listMaps()[0]
for lyr in m.listLayers():
    print(f"LAYER: {lyr.name}")
    if lyr.supports("dataSource"):
        lyrCIM = lyr.getDefinition("V2")
        if hasattr(lyrCIM, 'featureTable'):
            ListCIMJoins(lyrCIM.featureTable.dataConnection)
         ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

These examples just handle joins. They can be expanded to also list relates.

[RobertStevens]

Hi Jeff

Thanks for this information. I am not using arcpro, but if/when I do it appears to be just what I need.

Unfortunately arcmap does not support either the connectionProperties attribute or the getDefinition method
(in fact arpy for arcmap does not support CIM in any way at all).

Rob

[JoshuaBixby]

Robert Stevens‌, if you install GitHub - enthought/comtypes: A pure Python, lightweight COM client and server framework , you can access the underlying ArcObjects libraries from within Python and do almost anything.  Coding COM through Python is not very Pythonic, but it is doable if absolutely necessary.

Since I don't work in ArcMap all that much these days, let alone COM programming with ArcMap, it took me a little bit to gin up the following code to demonstrate how you can use comtypes to get the information you need.  Just paste the code into the interactive Python window to start.  Just set the MXD file path and run the code in Python interpreter.

import arcpy
import os
from comtypes.client import CreateObject, GetModule

mxd = # path to MXD file 

# Function for casting COM objects to interfaces
def CType(obj, interface):
    """Casts obj to interface and returns comtypes POINTER or None"""
    try:
        newobj = obj.QueryInterface(interface)
        return newobj
    except:
        return None

# Path to ArcGIS Desktop COM files
comDirectory = os.path.join(  
    os.path.join(arcpy.GetInstallInfo()['InstallDir']), 'com'  
)

# References to needed ArcGIS Desktop COM components
esriSystem = GetModule(os.path.join(comDirectory, 'esriSystem.olb'))
esriFramework = GetModule(os.path.join(comDirectory, 'esriFramework.olb'))
esriArcMapUI = GetModule(os.path.join(comDirectory, 'esriArcMapUI.olb'))
esriCarto = GetModule(os.path.join(comDirectory, 'esriCarto.olb'))
esriGeodatabase = GetModule(os.path.join(comDirectory, 'esriGeodatabase.olb'))

# Create filter for layers that support IGeoFeatureLayer
pUID = CreateObject(esriSystem.UID)
pUID.Value = "{E156D7E5-22AF-11D3-9F99-00C04F6BC78E}"

# # Section for running within ArcGIS Desktop application
# # Confirm code is being run within ArcMap (Interactive, Addin, Toolbox)
# try:
#     pApp = CreateObject(esriFramework.AppRef)
#     assert pApp.Name == 'ArcMap', 'Code must be run within ArcMap'
#     pMxDoc = CType(pApp.Document, esriArcMapUI.IMxDocument)
# except WindowsError:
#     assert False, 'Code must be run within an ArcGIS Desktop application'
#     

# Section for running outside of ArcGIS Desktop application
# Open MXD file
try:
    pMapDoc = CreateObject(esriCarto.MapDocument, interface=esriCarto.IMapDocument)
    assert pMapDoc.IsMapDocument(mxd), 'Valid MXD required'
    pMapDoc.Open(mxd)
    pMap = pMapDoc.Map(0)
except:
    assert False, 'Unable to open MXD and retrieve map'

# Loop over layers that support IGeoFeatureLayer
# pEnumLayer = pMxDoc.FocusMap.Layers(pUID, True)
pEnumLayer = pMap.Layers(pUID, True)

results = ["LayerName, JoinCount, RelateCount"]
pEnumLayer.Reset()
pLayer = pEnumLayer.Next()
while pLayer:
    # Count number of relates
    relateCount = 0
    pRelClassColl = CType(pLayer, esriCarto.IRelationshipClassCollection)
    pEnumRelClasses = pRelClassColl.RelationshipClasses
    pEnumRelClasses.Reset()
    while pEnumRelClasses.Next():
        relateCount += 1
        
    # Count number of joins
    joinCount = 0
    table = CType(pLayer, esriCarto.IDisplayTable).DisplayTable
    while CType(table, esriGeodatabase.IRelQueryTable):
        joinCount += 1
        table = CType(table, esriGeodatabase.IRelQueryTable).SourceTable
        
    # Append results and move to next layer
    results.append("{}, {}, {}".format(pLayer.Name, joinCount, relateCount))
    pLayer = pEnumLayer.Next()

for res in results:
    print(res)
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The code only counts joins and relates, it doesn't list all the data sets, but getting data set information is possible.