Apex Code Development You need to sign in to do that
Don't have an account?
David 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
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
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 IfAll Answers
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: Where
Id - ID of device
Name - Name of device
Vehicle__c - ID of vehicle
Also you can use parent to child variant of query: Not sure about Name__c field. If it is true, please, make changes accordingly.
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