+ Start a Discussion
David ChurchillDavid Churchill 

Getting Related Object Name from a Query Result

I'm very new to SF development and am working on a small integration project using a VBA macro in Outlook.  I want to fill an array with 3 items from a custom object (Vehicle__c) which has a lookup relationship with another custom object (Device__c), I want the Vehicle__c.Id, Device__c.ID and Device__c.Name__c.   I'm using the following SOQL which when I test in the Force.com Explorer returns the 3 fields queried but the Name of the related object is resturned as a hyperlink that I have to click on to see the name.

SOQL: SELECT Id, Device__c, Device__r.Name__c FROM Vehicle__c where IsDeleted = false

My VBA code below never finds the device__r.Name__c from the query result.  In fact when I watch the variables in the debugger I can see every field name in every object in the Query Result (actually the Fields[] array of the SObject4), why don't I just get back the 3 fields I asked for in the query?  Any help regarding retrieving the Name of the related record would be greatly appreciated.

    Dim qr As QueryResultSet4
    Dim v, r As Variant
    Dim f As Field4
    Dim s As SObject4

    If loggedIn Then
        Set qr = g_sfApi.Query("SELECT Id, Device__c, Device__r.Name__c FROM Vehicle__c where IsDeleted = false", False)
        i = 0
        ReDim vehDeviceMap(qr.Size - 1, 2)
        For Each v In qr
            Set s = v
            For Each r In s.Fields
                Set f = r
                If f.Name = "Id" Then
                    vehDeviceMap(x, 0) = CStr(f.value)
                ElseIf f.Name = "ttdevtest__Device__c" Then
                    vehDeviceMap(x, 1) = CStr(f.value)
                ElseIf f.Name Like "ttdevtest__Device__r.ttdevtest__Name__c" Then 'This ElseIF Statement is never true
                    vehDeviceMap(x, 2) = CStr(f.value)
                End If
            Next r
            i = i + 1
        Next v
    End If
 
Best Answer chosen by David Churchill
David ChurchillDavid Churchill
Turns out what I needed was to use the GetJoinResults method.  The following code got me the results I was looking for:
If f.Name = "Device__c" Then
	vehDeviceMap(i, 1) = CStr(f.value)
	'Device to vehicle lookup is NULLable so account for null
	If s.GetJoinResults("Device__r") Is Nothing Then
		vehDeviceMap(i, 2) = ""
	Else
		vehDeviceMap(i, 2) = s.GetJoinResults("Device__r").Item("Name__c").value
	End If
End If

 

All Answers

Mikola SenykMikola Senyk
Hi David,

It looks like the Lookup relationship has different direction
Device__c --> Vehicle__c (one vehicle many devices)
not Vehicle__c --> Device__c
Use the folowing SOQL:
SELECT Id, Name, Vehicle__c FROM Device__c LIMIT 10
Where
Id - ID of device
Name - Name of device
Vehicle__c - ID of vehicle

Also you can use parent to child variant of query:
SELECT Id, (SELECT Id, Name FROM Device__r) FROM Vehicle__c
Not sure about Name__c field. If it is true, please, make changes accordingly.
David ChurchillDavid Churchill
Turns out what I needed was to use the GetJoinResults method.  The following code got me the results I was looking for:
If f.Name = "Device__c" Then
	vehDeviceMap(i, 1) = CStr(f.value)
	'Device to vehicle lookup is NULLable so account for null
	If s.GetJoinResults("Device__r") Is Nothing Then
		vehDeviceMap(i, 2) = ""
	Else
		vehDeviceMap(i, 2) = s.GetJoinResults("Device__r").Item("Name__c").value
	End If
End If

 
This was selected as the best answer