[GIS] Programatically identifying Joined Field in ArcMap

arcgis-desktoparcmaparcpyattribute-joins

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.

How to identify the "Input Join Field" and "Output Join Field"?

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.

Best Answer

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?

Related Question