Programatically identifying Joined Field in ArcMap?

Question

Is it possible to programmatically identify the Join Field that is being used to Table Join two datasets together in ArcMap? I am currently using ArcGIS 10.0, SP5 and would prefer an ArcPy solution, however I wouldn't be opposed to other solutions, if an ArcPy solution is not available.

One method I tried was looping through all of the fields and look for a matching "baseName", but that is just an "educated guess" where you are hoping that the fieldnames in both databases are the same.

For a graphic representation of what I am after, I basically want to identify the "Input Join Field" and "Output Join Field" as seen in the "Add Join" dialog, but after-the-fact, of course.

This is a tag-on question to Can a “Join” be detected programmatically?, but in this case I want to extend the functionality to identify the FIELD(s) being used to join the two (or more) datasets together.

Answer 1

Here is an ArcObjects approach, based off this example, to enumerate all joins on a layer and list their destination and source table names and primary and foreign keys:

1. Get a reference to an ILayer that has one or more joins
2. Cast the ILayer to IDisplayTable
3. Cast the IDisplayTable.DisplayTable property to IRelQueryTable
4. While the current table is an IRelQueryTable:
   1. Inspect the RelQueryTable's DestinationTable and SourceTable properties
   2. Inspect the OriginPrimaryKey and OriginForeignKey properties of the IRelQueryTable.RelationshipClass property.
   3. Set the current table to the current RelQueryTable's SourceTable property

This Python script (using comtypes and this helper module) will go through all of the joins, from latest to earliest, and print the destination and source table names, origin primary key and origin foreign key for each join:

from ESRICOMHelpers import * # helper module from https://gis.stackexchange.com/a/5082/753
esriArcMapUI = GetESRIModule("esriArcMapUI")
esriCarto = GetESRIModule("esriCarto")
esriGeoDatabase = GetESRIModule("esriGeoDatabase")

def listJoins(table):
    while CType(table, esriGeoDatabase.IRelQueryTable):
        relQueryTable = CType(table, esriGeoDatabase.IRelQueryTable)
        destTable = relQueryTable.DestinationTable
        sourceTable = relQueryTable.SourceTable
        destDataset = CType(destTable, esriGeoDatabase.IDataset)
        sourceDataset = CType(sourceTable, esriGeoDatabase.IDataset)
        relClass = relQueryTable.RelationshipClass
        print destDataset.Name, sourceDataset.Name, relClass.OriginPrimaryKey, relClass.OriginForeignKey
        table = sourceTable

if __name__ == "__main__":
    #app = GetCurrentApp() # Use if run in-process
    app = GetApp("ArcMap") # Use if run in a standalone script
    mxd = CType(app.Document, esriArcMapUI.IMxDocument)

    # Gets the first layer in the active data frame
    map = mxd.FocusMap
    lyr = map.Layer[0]

    # Need to get the "display table" to access the joins
    displayTable = CType(lyr, esriCarto.IDisplayTable).DisplayTable

    # List the layer's joined tables
    listJoins(displayTable)

Example output, given a source layer with three joins:

join_table_3    master_fc_join_table_1_join_table_2    JOIN_ID_3    master_fc.MASTER_ID
join_table_2    master_fc_join_table_1                 JOIN_ID_2    master_fc.MASTER_ID
join_table_1    master_fc                              JOIN_ID_1    MASTER_ID

For more info, see How do I access ArcObjects from Python?

Answer 2

Put all data of the fields in strings, (after having ordered them) compare them with a fuzzycompare function and select those who gave best match or match beyound a certain precision.

This solution is when some data would not fit. If you think both column would always fit, then just order and compare for a perfect match with an ordinary compare function.

Answer 3

Try this:

• Use the XSLT Transformation tool from the Metadata toolset to write out an xml/html metadata file for the dataset in question.

  arcpy.XSLTransform_conversion(r'X:\temp\Scratch.gdb\fc_FeatureToPoint',"C:\Program Files\ArcGIS\Desktop10.1\Metadata\Stylesheets\ArcGIS.xsl", r'X:\temp\Metadata.html')
  

• Use an HTML parser to read in the metadata file and search for the join field from the Join Field tool's geoprocessing history

• Sample output from the XSLT Transformation tool

Answer 4

Joined table names are in the IFeatureLayer - IFeatureLayerDefinition object as a string.. which I think probably contains the join SQL and thus the field names.

http://edndoc.esri.com/arcobjects/8.3/diagrams/Map%20Layer%20Object%20Model.pdf

Or do you mean if you can't access that object?

Answer 5

to find matching fields regardless of field name, you could do something like this:

import arcpy

fc = r"temp/RiversJoined.shp"

fldList1 = [f.name for f in arcpy.ListFields(fc)]
fldList2 =[g.name for g in arcpy.ListFields(fc)]

for f in fldList1:
    values1 = [f_row[0] for f_row in arcpy.da.SearchCursor(fc, (f))]
    for g in fldList2:
        values2 = [g_row[0] for g_row in arcpy.da.SearchCursor(fc,(g))]
        #compare field values
        #get names of matching fields
        if (fldList2.index(g) != fldList1.index(f) and values1 == values2):
            print "match: " + str(g) + " match: "+ str(f)