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.

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.

• blah238: Which version of ArcGIS are you working with? And I assume based on the tags that you are looking specifically for a way to do this with arcpy and not ArcObjects?
• RyanKDalton: I am currently using ArcGIS 10.0, SP5. And yes, I am looking/hoping for an ArcPy solution, however I wouldn't be opposed to an ArcObjects solution, if that is the only alternative.
• lewis: Here is some possibly revelant documentation:edndoc.esri.com/arcobjects/9.2/ComponentHelp/esriGeoDatabase/… It involves the pRelClass This is the RelationshipClass used to define the join tables and join fields as well as the cardinality. The Open method either creates a new RelQueryTable or returns a reference to an existing RelQueryTable if that class has already been created. You could invoke this method and find the reference involving thepRelClass
• blah238: @lewis, you do not need to use the factory object to get a reference to an existing RelQueryTable -- see my answer.

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?

• RyanKDalton: This looks very promising. I've got the comtypes package installed and the helper function code added, but I get the error "global name 'esriGeoDatabase' is not defined". Where/How should it be defined in the code preceding the line while CType(table, esriGeoDatabase.IRelQueryTable)?
• blah238: I didn't include it, but at some point you have to import the comtypes wrappers around the specific ESRI object libraries you need. Using my helper module it's as simple as esriGeoDatabase = GetESRIModule("esriGeoDatabase").
• RyanKDalton: Got it, thanks. Will this work for layers in ArcMap as well? I am passing each layer from layerList = arcpy.mapping.ListLayers(mxd) into the listJoins(table) code, but it skips out at the while statement.
• blah238: I don't think you can cast between arcpy objects and comtypes objects, so you need to obtain the ILayer reference through ArcObjects. I've updated the code to include a more complete example. This should be able to be used both in and out of process by commenting/uncommenting the relevant lines.
• RyanKDalton: Getting closer, thanks for the walk-through patience... now, how do you sent the actual map document file (*.mxd) that you want to look at? app.Document comes back with 'NoneType' object has no attribute 'Document'

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

Output from the XSLT Transformation tool

• RyanKDalton: That's a really clever idea that I thought had real promise, but from my testing, it appears as though this will only work if the file was joined using the GP Tool called "JoinField", as this is written as part of the GP process history for that layer. If the user has created a join through the UI, the JoinField process line does not exist in the output file. Great idea though!
• blah238: I wouldn't rely on GP history for this anyways. We try to delete it as soon as possible because for recurring processes it mounts up quickly into a huge amount of data that renders a feature class nearly unusable.

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?

• blah238: IFeatureLayerDefinition does not contain the "join SQL", it only has a DefinitionExpression property exposing the feature layer's definition query, if set, which is a WHERE clause that limits what rows are displayed.
• blah238: It does have a RelationshipClass property, however, but I think this only exposes the most recent join. You would need to use IRelQueryTable instead to get them all.

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)

• mwil: hey to whomever knocked my answer: can you tell me what's wrong with my answer? thanks.
• blah238: This doesn't appear to answer the question as asked. Same with the fuzzy compare answer. Fields being identical (or even fuzzily similar) has no bearing on whether or not they have actually been used in a join.