Can I find join properties in Python?

[MarkBinder]

I have several mxd files with layers and tables that need to be updated periodically. Is there any way to use Python to see not only if a layer in an mxd has a join but what the join properties are?

I have found a round a bout way to determine if a layer has a join using the field names but I would like to be able to update a join if a layer or table changes.

Thanks,

[XanderBakker]

According to the layer class these properties are not revealed. However, if you look at the field names of a layer that has an active join, you will see names like "Featureclass.FieldName". This might help to retrieve the featureclasses or tables involved in the join, but if the join was created to a dataset that is not in the activate map document, that won't help much.

[MarkBinder]

Right, I'm able to determine if a layer has a join based on the field names; but that only works if I run the script in ArcMap with the mxd open and I can determine the name of the table but not the file path. It looks like I can use that same strategy using an IDE but only if the tables are also in the same mxd with the layers they join to.

When I run the script on a test mxd I get six layers with joins if I run it in the mxd and four if I use an IDE.

It would be nice if I could get the table name, data source, join field, etc.

Thanks

[XanderBakker]

It is strange that you get different results in the Python Window than when running the script from an IDE. I will do some testing here and see if this happens to me too.

[RandyBurton]

Can you share the code you have been testing?  I have not been able do duplicate your situation.  I have been testing the following code both inside and outside ArcMap, and themap has a feature joined with a table that is listed in the map TOC and a second feature joined with a table that is not in the map TOC.  It picks up all joined fields (as feature.field or table.field) of both features.  The table path is only available when the table is in the TOC with ListTableViews. 

import arcpy

mxd = arcpy.mapping.MapDocument(r"C:\Path\To\JoinTest.mxd")

for f in arcpy.mapping.ListLayers(mxd):
    field_info = arcpy.Describe(f).fieldInfo
    for i in xrange(0,field_info.count):
        print field_info.getfieldname(i)
    print '====='
‍‍‍‍‍‍‍‍‍

[RandyBurton]

As a follow-up...

It might be possible to get suggestions as to the identity of the join field by using some dictionaries.  This would look at a row in each feature to see if there are multiple fields with the same value.  Just a thought...

for f in arcpy.mapping.ListLayers(mxd):
    fields = []
    field_info = arcpy.Describe(f).fieldInfo
    for i in xrange(0,field_info.count):
        fields.append(field_info.getfieldname(i))
    with arcpy.da.SearchCursor(f, fields) as cursor:
        values = cursor.next()
    # make dictionary of fields and values
    joinDict = dict(zip(fields, values))
    # swap keys and values
    revDict = {}
    for key, value in joinDict.items():
        revDict.setdefault(value, set()).add(key)
    join_fields = [values for key, values in revDict.items() if len(values) > 1] # field names
    field_value = [key for key, values in revDict.items() if len(values) > 1] # value in common
    print "Possible joins: {}".format(join_fields)
    print "Field value used: {}".format(field_value)

# Output:
# Possible joins: [set([u'Table1.TaID', u'Feature1.OBJECTID', u'Table1.OBJECTID'])]
# Field value used: [1.0]‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[MarkBinder]

Here's the code I have have tried. It's pretty simple: it makes a list of all the layers with joins and then makes a list of the layers with joins and the name of the tables they join with.

mxd=arcpy.mapping.MapDocument("current")
##mxd=arcpy.mapping.MapDocument(r"\\some.mxd")
layers=arcpy.mapping.ListLayers(mxd)
joinList=[]
tableList=[]
# Discover which spatial layers have joins and add to join list
for l in layers:
    if l.isFeatureLayer and not l.isBroken:
        flist=arcpy.Describe(l).fields
        for f in flist:
            if f.name.find(l.datasetName) >-1:
                joinList.append(l)
                break

# Discover the tables that are joined to the spatial layers
for l in joinList:
    flist=arcpy.Describe(l).fields
    for f in flist:
        if f.name.find(l.datasetName) ==-1:
            if [l.name,f.name[0:f.name.rfind(".")]] not in tableList:
                tableList.append([l.name,f.name[0:f.name.rfind(".")]])

# List spatial layer and which tables are joined to it
for t in tableList:
    print t‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Sorry for the formatting; I haven't posted code before. Switch the "mxd=" lines depending if you're using and IDE or the ArcMap python window.

[XanderBakker]

I formatted the code. Instructions on how to do this can be found here: https://community.esri.com/docs/DOC-8691-posting-code-with-syntax-highlighting-on-geonet 

[RandyBurton]

I ran your code both inside and outside ArcMap without missing any joins in my test map.  If you remove the "and not l.isBroken" from line 8, do you get the same results?

[MarkBinder]

After looking through my mxd the problem is that I still have some layers in a personal geodatabase and I have been using a 64-bit IDE. So, that would account for having a difference between the IDE and ArcMap.