topic Re: No Arcpy Method do Determine a Layer's Joins in ArcGIS API for Python Questions

No Arcpy Method do Determine a Layer's Joins

RobertStevens — Sat, 21 Dec 2019 23:13:45 GMT

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.

Re: No Arcpy Method do Determine a Layer's Joins

DanPatterson_Retired — Sun, 22 Dec 2019 01:04:21 GMT

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.

Re: No Arcpy Method do Determine a Layer's Joins

RobertStevens — Sun, 22 Dec 2019 02:05:26 GMT

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.

Re: No Arcpy Method do Determine a Layer's Joins

DanPatterson_Retired — Sun, 22 Dec 2019 04:51:39 GMT

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.

Re: No Arcpy Method do Determine a Layer's Joins

RobertStevens — Mon, 23 Dec 2019 18:29:35 GMT

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?

Re: No Arcpy Method do Determine a Layer's Joins

DanPatterson_Retired — Mon, 23 Dec 2019 19:52:49 GMT

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&collection=help

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.

Re: No Arcpy Method do Determine a Layer's Joins

JoshuaBixby — Mon, 23 Dec 2019 22:49:22 GMT

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.

Re: No Arcpy Method do Determine a Layer's Joins

JeffMoulds — Sun, 12 Dec 2021 07:24:08 GMT

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.

Re: No Arcpy Method do Determine a Layer's Joins

RobertStevens — Sat, 25 Jan 2020 19:22:53 GMT

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

Re: No Arcpy Method do Determine a Layer's Joins

JoshuaBixby — Sun, 12 Dec 2021 07:24:11 GMT

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.

Re: No Arcpy Method do Determine a Layer's Joins

RobertStevens — Wed, 29 Jan 2020 13:12:53 GMT

Joshua

Thanks for this posting. Wow!, that really is exploring the deep state of arcgis! I fear it isn't quite what I want because the context requires (according to the try..except clause in the code) that it be used within an interactive python window, whereas my need was a non-interactive bookkeeping use. But your example certainly gives me a window into the deep underpinning of esri/com.

Thanks!

Re: No Arcpy Method do Determine a Layer's Joins

JoshuaBixby — Wed, 29 Jan 2020 18:55:23 GMT

Robert Stevens‌, a pretty easy change to run the way you want. Instead of re-posting code, I updated the code in my earlier post to run outside of an ArcGIS Desktop application. Just remember to set the MXD path at the start of the code.