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
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.
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
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
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
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.
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.
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.