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.

[DanielTuracek]

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 🙂

[JanDuske]

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.

[BrianBottoms]

Use:

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

Brian

[MikeLouwrens]

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.

[JeffMatson]

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

[MikeLouwrens]

    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.

[JeffMatson]

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

[MikeLouwrens]

of course, thanks Jeff

Cheers,
Mike.

[RichardFairhurst]

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.