9

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.

  • 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? – blah238 Jul 25 '13 at 16:00
  • 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. – RyanDalton Jul 25 '13 at 16:02
  • 1
    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 – lewis Jul 31 '13 at 14:38
  • @lewis, you do not need to use the factory object to get a reference to an existing RelQueryTable -- see my answer. – blah238 Jul 31 '13 at 23:15
7
+50

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?

  • 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)? – RyanDalton Aug 1 '13 at 18:14
  • 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"). – blah238 Aug 1 '13 at 18:17
  • 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. – RyanDalton Aug 1 '13 at 18:24
  • 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. – blah238 Aug 1 '13 at 19:46
  • 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' – RyanDalton Aug 1 '13 at 20:11
1

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.

0

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

  • 1
    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! – RyanDalton Jul 30 '13 at 14:37
  • 1
    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. – blah238 Jul 31 '13 at 18:15
-1

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?

  • 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 Jul 31 '13 at 23:30
  • 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. – blah238 Jul 31 '13 at 23:33
-2

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)
  • hey to whomever knocked my answer: can you tell me what's wrong with my answer? thanks. – mwil Jul 31 '13 at 21:59
  • 1
    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. – blah238 Jul 31 '13 at 23:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.