Get a value from a joined field

[MikeLouwrens]

Hi,

I have a tool that selects two features (different feature-classes), gets a value from a specific field in feature 1 and puts it into a specific field in feature 2. This mostly does what I want, except I would like to get the value from a field from a joined table, but the tool won't work on a joined field.

How do I get it to use a join field value? I went hunting for some code that looks at joined fields, and found something in the old forums that will give me all the field names, including the joined fields, but I can't find an option to get the value from a field. Is it possible to change this script somehow to give me the values from those fields?

Sub JoinedFields()
Dim pDoc As IMxDocument
Set pDoc = ThisDocument
Dim pFeatureLayer As IFeatureLayer
Set pFeatureLayer = pDoc.FocusMap.Layer(0)
 
Dim pFeat As IFeature
Set pFeat = pFeatureLayer.FeatureClass.GetFeature(12)
Dim pTable As ITable
Dim pDisplayTable As IDisplayTable
Set pDisplayTable = pFeatureLayer
Set pTable = pDisplayTable.DisplayTable
 
Dim pFields As IFields
Dim pField As IField
Set pFields = pTable.Fields
For i = 0 To pFields.FieldCount - 1
  Debug.Print pFields.Field(i).Name
Next
End Sub

I had thought I could do something like "Debug.Print pFields.Field(i).Value" but that's not an option.

thanks,
Mike.

[JeffMatson]

Just a note to improve performance on a portion of the code Jeff provided. Make it a practice to get the index of any field outside of the loops you create and directly use the field index in the loop. The FindField operation does a schema search loop that really only needs to be done once outside of your loop. Repeating loops within loops drags down performance. Using the index value directly inside the loop acts like direct array access to the field and does not cause a secondary hidden loop to occur for every record your loop processes. (From best practices with cursors training at the ESRI conference). So the sample code should be revised as follows:

    Dim pTableCursor As ICursor
    Dim pRow As IRow
 
    Set pTableCursor = pTable.Search(Nothing, True)
    Set pRow = pTableCursor.NextRow
 
    Dim fIndex as Long
    fIndex = pTable.Fields.FindField("FeatureClass.FieldName") 'fully qualified field name from joined table
    Do Until pRow Is Nothing
        Debug.Print pRow.Value(fIndex) ' removes a hidden loop required to find the field
        Set pRow = pTableCursor.NextRow
    Loop

Hope this helps.

Good point Richard.  It might also be worth mentioning to check if fIndex is <> -1 (i.e. the field name is not found in the feature class or table) before using it, to avoid an automation error.

[MikeLouwrens]

OK thanks all for you help, much appreciated 😄

I've got something working now, although is a little slow... can I have done this a bit more efficiently?

Public Sub GetLayerSelection2()
    Dim pMxDoc As IMxDocument
    Set pMxDoc = ThisDocument
    
    Dim pFLayer As IFeatureLayer
    Set pFLayer = pMxDoc.FocusMap.Layer(0) 'Get first layer
    
    Dim pFSel As IFeatureSelection
    Set pFSel = pFLayer
    
    Dim pSelSet As ISelectionSet
    Set pSelSet = pFSel.SelectionSet
    
    Dim pFCursor As IFeatureCursor
    
    pSelSet.Search Nothing, True, pFCursor
    
    Dim pCount As Integer
    pCount = pSelSet.count
    
    If pCount > 5 Then 
        MsgBox "Too many features selected.  Please select 5 or less." 'Is quite slow, so don't run if lots of features
        Exit Sub
    End If
    
    Dim pFeature As IFeature
    Set pFeature = pFCursor.NextFeature
    
    Dim pDisplayTable As IDisplayTable
    Set pDisplayTable = pFLayer
    
    Dim pTable As ITable
    Set pTable = pDisplayTable.DisplayTable
    
    Dim strQuery As String
    
    Dim pQFilter As IQueryFilter
    Set pQFilter = New QueryFilter
    
    Dim pTableCursor As ICursor
    
    Dim pTableIndex As Long
    pTableIndex = -1
    
    Dim pFeatureIndex As Long
    pFeatureIndex = -1
    
    Do Until pFeature Is Nothing
        If pFeatureIndex = -1 Then
            pFeatureIndex = pFeature.Fields.FindField("COMPKEY")
        End If
        
        strQuery = "COMPKEY = " & pFeature.Value(pFeatureIndex) 'Use join field (COMPKEY) to find matching record in table
        pQFilter.WhereClause = strQuery
        Set pTableCursor = pTable.Search(pQFilter, False)
        
        Dim pRow As IRow
        Set pRow = pTableCursor.NextRow
        
        If pTableIndex = -1 Then
            pTableIndex = pTable.Fields.FindField("SERVSTAT") ' Field to be found in join table
        End If
        
        Do Until pRow Is Nothing
            Debug.Print pRow.Value(pTableIndex) ' report value of field in join table
            Set pRow = pTableCursor.NextRow
        Loop
        
        Set pFeature = pFCursor.NextFeature
    Loop
    
End Sub

Thanks,
Mike.

[RichardFairhurst]

You can tighten up the loop a little by taking out the field tests outside of the loops as follows:

Dim pTableIndex As Long
        pTableIndex = pTable.Fields.FindField("SERVSTAT") ' Field to be found in join table
        If pTableIndex = -1 Then
           msgbox "SERVSTAT field not found.  Exiting Sub."
           Exit Sub
        End If
    
    Dim pFeatureIndex As Long
        pFeatureIndex = pFeature.Fields.FindField("COMPKEY")
        If pFeatureIndex = -1 Then
           msgbox "COMPKEY field not found.  Exiting Sub."
           Exit Sub
        End If
    
    Do Until pFeature Is Nothing
        
        strQuery = "COMPKEY = " & pFeature.Value(pFeatureIndex) 'Use join field (COMPKEY) to find matching record in table
        pQFilter.WhereClause = strQuery
        Set pTableCursor = pTable.Search(pQFilter, False)
        
        Dim pRow As IRow
        Set pRow = pTableCursor.NextRow
        
        Do Until pRow Is Nothing
            Debug.Print pRow.Value(pTableIndex) ' report value of field in join table
            Set pRow = pTableCursor.NextRow
        Loop
        
        Set pFeature = pFCursor.NextFeature
    Loop

[RichardFairhurst]

I tried your code and don't understand why it works for you at all.  When I used it on a joined feature layer I got errors when I tried to just use a field name in the join table.  I had to use the fully qualified table name and field name to obtain the display table field index and a correctly formatted query where clause inside of the loop to access the joined field name.  I could not just use an unqualified field name as you seem to be doing.

I am assuming you intend to write data to the parent feature class and not just read the data of the joined table.  Otherwise, if all you wanted to do was read data you should be able to just use the display table without having to use the parent feature class and a pair of embedded loops on the parent feature class and on the display table.  To just  read data you should bypass the FeatureSelection and just set the SelectionSet to the DisplaySelectionSet of the display table and loop through it to get your key and joined field together (using the fully qualified table name and field name of the joined table).  So what is this code really supposed to end up doing for you?  If you need to use it to write to the parent table (or you actually have a 1:M or M:M relationship), then the embedded loops are probably necessary.

[RichardFairhurst]

I have revised your code to make it more efficient.  Your code is slow because it uses repeated queries inside of a loop (it also was repeatedly doing a Dim of the Row variable inside the loop as well, which is a big no no.  Never do a Dim inside of a loop).

I realized that since you are accessing the same SelectionSet for the same parent feature class through the same layer that you do not need to use a queryfilter in the loop to synchronize the reading of the parent feature class and the joined table.  To prove that the cursors on the unjoined featureclass and the joined displaytable are running in synch, I substituted reading the parent table ObjectID feilds in the code below.  You can revise this code back to replacing the fields with your original fields (again if all you want to do is just read the joined data, just use the display table SelectionSet together with either the ParentTableName.FieldName or JoinTableName.FieldName formats for the fields you want to read).

Now ideally this code could be further modified to use an efficient method that would substitute an update cursor for reading through the SelectionSet of the parent feature class instead of using a search cursor.  That way the code would allow reading of data from the joined displaytable and writing to the features of the parent FeatureClass in a single pass, with efficiency that is similar to using a FieldCalculator with a VBA or VB Script expression on a joined table.

Public Sub GetLayerSelection2()
    Dim pMxDoc As IMxDocument
    Set pMxDoc = ThisDocument
    
    Dim pFLayer As IFeatureLayer
    Set pFLayer = pMxDoc.FocusMap.Layer(0) 'Get first layer
    
    Dim pDisplayTable As IDisplayTable
    Set pDisplayTable = pFLayer
    
    If Not TypeOf pDisplayTable.DisplayTable Is IRelQueryTable Then
       MsgBox "Feature Layer is not joined!  Exiting Sub."
       Exit Sub
    End If
    
    Dim pSelSet As ISelectionSet
    Set pSelSet = pDisplayTable.DisplaySelectionSet
    
    Dim pCount As Long ' Use Integer only if this is VB.Net and not VBA.
    pCount = pSelSet.Count
    
    If pCount > 5 Then
        MsgBox "Too many features selected.  Please select 5 or less." 'Was quite slow, but now probably no longer necessary
        Exit Sub
    End If
    
    Dim pTableCursor As ICursor
    pSelSet.Search Nothing, True, pTableCursor
    
    Dim pRow As IRow
    Set pRow = pTableCursor.NextRow
    
    Dim pFSel As IFeatureSelection
    Set pFSel = pFLayer
    
    Dim pSelSet2 As ISelectionSet
    Set pSelSet2 = pFSel.SelectionSet
    
    Dim pFCursor As IFeatureCursor
    pSelSet2.Search Nothing, True, pFCursor
    
    Dim pFeature As IFeature
    Set pFeature = pFCursor.NextFeature
    
    Dim pTableIndex As Long
    pTableIndex = pDisplayTable.DisplayTable.Fields.FindField("ParentTableName.OBJECTID") ' Substitute the actual ParentTableName or use JoinTableName.FieldName
    If pTableIndex = -1 Then
      MsgBox "ParentTableName.OBJECTID Field Not Found! Exiting Sub." ' Substitute the appropriate message.
      Exit Sub
    End If
    
    Dim pFeatureIndex As Long
    pFeatureIndex = pFeature.Fields.FindField("OBJECTID")
    If pFeatureIndex = -1 Then
      MsgBox "OBJECTID Field Not Found! Exiting Sub."
      Exit Sub
    End If
    
    Do Until pFeature Is Nothing ' Keep your loop as tight as possible and do not do Dim statements inside of it.
        Debug.Print pFeature.Value(pFeatureIndex) & " = " & pRow.Value(pTableIndex) ' report value of field in join table
        Set pRow = pTableCursor.NextRow
        Set pFeature = pFCursor.NextFeature
    Loop
End Sub

[Anonymous User]

Take a look at the IGeoFeatureLayer interface for another method to get at the values on joined fields (IGeoFeatureLayer.SearchDisplayFeatures). It might be an easier approach.

[RichardFairhurst]

Take a look at the IGeoFeatureLayer interface for another method to get at the values on joined fields (IGeoFeatureLayer.SearchDisplayFeatures). It might be an easier approach.

Sean:

I looked at that interface, but that method is available already on the IDisplayTable and the help for IGeoFeatureLayer suggest using the IDisplayTable interface since it is more generic (it works with joined featureclasses and standalone tables).

The other disadvantage of the IGeoFeatureLayer is that it requires the construction of a queryfilter and has no direct access to the existing SelectionSet for the layer.  The code as writen is using whatever is already selected and the IDisplayTable provides fairly direct access to the SelectionSet for both the unjoined parent featureclass (or StandaloneTable) and the joined table data.

What would be most useful at this point is a suggestion for the most direct way to transfer the records of a SelectionSet over to an Update Cursor without constructing a potentially huge whereclause.

[Anonymous User]

I think if you give nothing as the queryfilter to ISelectionSet.Search, it will return all the records as a cursor.

[MikeLouwrens]

I tried your code and don't understand why it works for you at all. When I used it on a joined feature layer I got errors when I tried to just use a field name in the join table. I had to use the fully qualified table name and field name to obtain the display table field index and a correctly formatted query where clause inside of the loop to access the joined field name. I could not just use an unqualified field name as you seem to be doing.

My join table is a standalone SQL table in a non-SDE database.  I've found that if I join to a table within an SDE database then I have to use fully qualified table/field names, but not if to a non-SDE table.

I am assuming you intend to write data to the parent feature class and not just read the data of the joined table. Otherwise, if all you wanted to do was read data you should be able to just use the display table without having to use the parent feature class and a pair of embedded loops on the parent feature class and on the display table. To just read data you should bypass the FeatureSelection and just set the SelectionSet to the DisplaySelectionSet of the display table and loop through it to get your key and joined field together (using the fully qualified table name and field name of the joined table). So what is this code really supposed to end up doing for you? If you need to use it to write to the parent table (or you actually have a 1:M or M:M relationship), then the embedded loops are probably necessary.

What I've got is a tool (in .Net) that loops through selected points, creates a line from each point, and then takes some attributes from that point and writes it to the line.  The join is on the point feature class, and I am wanting to take a value from the join table and put it on the line. 

Mike.

[RichardFairhurst]

My join table is a standalone SQL table in a non-SDE database.  I've found that if I join to a table within an SDE database then I have to use fully qualified table/field names, but not if to a non-SDE table.

What I've got is a tool (in .Net) that loops through selected points, creates a line from each point, and then takes some attributes from that point and writes it to the line.  The join is on the point feature class, and I am wanting to take a value from the join table and put it on the line. 

Mike.

I tried your code with a file geodatabase and it requried the fully qualified table name and field name, so the behavior is not limited to an SDE geodatabase.  But whatever works.

Since your line features are not part of the code you have written and all your code accesses currently is the point feature class, you do not need the emedded loops to read the joined table.  You also will not need embedded loops if you are going to simply construct one line from the points, since you would then just be writing to one feature and you can access all of the fields you need including the shape field of the parent table from the ICursor object derived from the IDisplayTable.  However, if you are going to construct more than one line from a set of points based on some attrribute you would then need a set of embedded loops to control the creation of each line feature and the writing of the point attribute.

My latest code is writen with the intent of addressing the broader question that keeps coming up on the forum of how do you use an update cursor on a joined table.  It is very close to doing that if the SelectionSet of the parent Feature Class could be translated to an update cursor instead of a search cursor.  (It can be done by buiilding a where clause from the OID values, but that would entail an extra read of the data, which would be nice to avoid, as well as creation of a potentially very long clause if many features are selected).