topic Re: Get a value from a joined field in ArcObjects SDK Questions

Get a value from a joined field

MikeLouwrens — Thu, 05 Aug 2010 19:19:42 GMT

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.

Re: Get a value from a joined field

DanielTuracek — Fri, 06 Aug 2010 04:59:29 GMT

Hi
Im beginner in VBA , but i think you could start thinking about ifeature interface
http://edndoc.esri.com/arcobjects/9.2/ComponentHelp/esriGeoDatabase/IFeature.htm

there you can get value
maybe this help for start

have a great day 🙂

Re: Get a value from a joined field

JanDuske — Mon, 09 Aug 2010 09:00:36 GMT

You could use a IQueryDef object to perform your own join. I'm not familiar with VB, but the general aproach is to call IFeatureWorkspace.CreateQueryDef to get a QueryDef object, where you can set the table names to join in a comma seperated string (like pQueryDef.Tables = "Table1, Table2")
define join and filter attributes with the where clause (like pQueryDef.WhereClause = "Table1.key = 'KeyOfCurrentRow' and Table1.key = Table2.key") and maybe set the IQueryDef.Subfields to only get the values you need.
After doing so, a call to pQueryDef.Evaluate returns an ICursor with the resulting values of both tables. In other words: You define the parameters for a simple SQL Select-statement and get the result.

Re: Get a value from a joined field

BrianBottoms — Mon, 09 Aug 2010 15:58:44 GMT

Use:

For i = 0 To pDisplayTable.DisplayTable.Fields.FieldCount - 1
Debug.Print(pDisplayTable.DisplayTable.Fields.Field(i).Name)
Next

Brian

Re: Get a value from a joined field

MikeLouwrens — Thu, 02 Sep 2010 18:47:34 GMT

For i = 0 To pDisplayTable.DisplayTable.Fields.FieldCount - 1
Debug.Print(pDisplayTable.DisplayTable.Fields.Field(i).Name)
Next

Thanks for the suggestion Brian, but this does the same as the script in my original post - it just returns the field names, not the value for the selected record in each of those fields.

Lets say I have a field DIAM that stores the diameter of a pipe. The script I gave tells me there is a field DIAM, but what I want is to know that for the selected field, the value of DIAM is 200 - I can do this on a GDB field, but I can't figure out how to do it on a joined field, all I ever get is the field name, and not the specific value.

Cheers,
Mike.

Re: Get a value from a joined field

JeffMatson — Sat, 11 Dec 2021 15:38:55 GMT

Try looping through the rows in the table to get the values. Just change "FeatureClass.FieldName" to match the table and field you want to see:

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
    
    Dim pTableCursor As ICursor
    Dim pRow As IRow
    
    Set pTableCursor = pTable.Search(Nothing, True)
    Set pRow = pTableCursor.NextRow
    
    Do Until pRow Is Nothing
        Debug.Print pRow.Value(pTable.Fields.FindField("FeatureClass.FieldName"))  'fully qualified field name from joined table
        Set pRow = pTableCursor.NextRow
    Loop
End Sub

Re: Get a value from a joined field

MikeLouwrens — Sat, 11 Dec 2021 15:38:58 GMT

    Dim pTableCursor As ICursor
    Dim pRow As IRow
 
    Set pTableCursor = pTable.Search(Nothing, True)
    Set pRow = pTableCursor.NextRow
 
    Do Until pRow Is Nothing
        Debug.Print pRow.Value(pTable.Fields.FindField("FeatureClass.FieldName"))  'fully qualified field name from joined table
        Set pRow = pTableCursor.NextRow
    Loop
End Sub

Wow Jeff that gets me a LOT closer, thanks! 😄

Do you have any suggestions on how to get it to report for only the selected record (either using the Set pFeat = pFeatureLayer.FeatureClass.GetFeature(12) from in my code, or an actual selected feature), rather than looping through all records? I know I could probably put a Query filter into the cursor, but is there a quicker/easier way that you know of?

Cheers,
Mike.

Re: Get a value from a joined field

JeffMatson — Sat, 11 Dec 2021 15:39:01 GMT

Here is a sample that loops through the selected features from a particular layer (in this case the selected layer in the table of contents):

Public Sub GetLayerSelection()
'allows enumeration through all features selected from a particular layer
'to get all features regardless of what layer they are in, use IMap.SelectedFeatures
    Dim mxDoc As IMxDocument
    Set mxDoc = ThisDocument
    Dim fLayer As IFeatureLayer
    Set fLayer = mxDoc.SelectedLayer
    Dim fSel As IFeatureSelection
    Set fSel = fLayer
    Dim ss As ISelectionSet
    Set ss = fSel.SelectionSet
    Dim fCur As IFeatureCursor
    ss.Search Nothing, True, fCur
    Dim feat As IFeature
    Set feat = fCur.NextFeature
    Do Until feat Is Nothing
        Debug.Print feat.OID
        Set feat = fCur.NextFeature
    Loop
End Sub

Re: Get a value from a joined field

MikeLouwrens — Thu, 02 Sep 2010 22:29:10 GMT

of course, thanks Jeff

Cheers,
Mike.

Re: Get a value from a joined field

RichardFairhurst — Sat, 11 Dec 2021 15:39:03 GMT

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.

Re: Get a value from a joined field

JeffMatson — Sat, 11 Dec 2021 15:39:06 GMT

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.

Re: Get a value from a joined field

MikeLouwrens — Sat, 11 Dec 2021 15:39:09 GMT

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.

Re: Get a value from a joined field

RichardFairhurst — Sat, 11 Dec 2021 15:39:11 GMT

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

Re: Get a value from a joined field

RichardFairhurst — Tue, 05 Oct 2010 05:14:22 GMT

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.

Re: Get a value from a joined field

RichardFairhurst — Sat, 11 Dec 2021 15:39:14 GMT

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

Re: Get a value from a joined field

Anonymous User — Tue, 05 Oct 2010 17:03:11 GMT

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.

Re: Get a value from a joined field

RichardFairhurst — Tue, 05 Oct 2010 17:29:52 GMT

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.

Re: Get a value from a joined field

Anonymous User — Tue, 05 Oct 2010 18:16:14 GMT

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

Re: Get a value from a joined field

MikeLouwrens — Tue, 05 Oct 2010 18:41:56 GMT

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.

Re: Get a value from a joined field

RichardFairhurst — Tue, 05 Oct 2010 19:00:13 GMT

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