function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Tom AsselmanTom Asselman 

Querying relationship fields with the Office Toolkit

Hi,

I am currently writing an Excel macro that does, for example the following query:

select Id, Owned_by__c, Owned_by__r.FirstName from Vehicle__c where Owned_by__r.IsPersonAccount=true

Vehicle__c is a custom object with a lookup relationship to Account. We have Person Accounts enabled.

In the resulting QueryResultSet3 object, I can access all fields from the Vehicle__c object, but not those from the related Account object I asked for in the query (e.g. Owned_by__r.FirstName).

Can anyone point me in the right direction on this one?

Thanks in advance,

T.
Best Answer chosen by Admin (Salesforce Developers) 
SuperfellSuperfell
http://britishboyindc.com/?p=16

All Answers

SnowMoreSnowMore

I'm pretty much a newby myself, so take this answer FWIW. 

 

You cannot perform a join query using SOQL.  Since Vehicle__c and Accounts are two separate tables, you can't get everything you want in a single query.  You're going to have to get the AccountID from Vehicle__c and use that to perform a seond query, on the Account table, to get the other info you want.
Tom AsselmanTom Asselman
Sorry dude but that is incorrect. It is possible to join in SOQL.
SnowMoreSnowMore

 

Here's the quote from the documentation, dude:

 

 

SOQL does not support all advanced features of the SQL SELECT command. For example, you cannot use SOQL to perform join operations, use wildcards in field lists, use calculation expressions, or specify an ORDER BY clause to sort rows in the result set.

 

So if you know how to do a join, please post some sample code.
SuperfellSuperfell

They're not SQL style joins, but it is possible to query related tables, see

http://www.salesforce.com/us/developer/docs/api/index_Left.htm#StartTopic=Content/sforce_api_calls_soql_relationships.htm 
VarunCVarunC

Any suggestion how to read Contact__r.Name column if I fire this query:

 

g_sfApi.Query("select ID, Name, Contact__c, Contact__r.Name, Contact__r.Type__c from matter__c"

 

 

Reading the values like this:

 

For Each v In qr
    s = v
    cname = s("Contact__r.Name").Value
    ctype = s("Contact__r.Type__c").Value
Next
 
is causing runtime error :( 


SuperfellSuperfell
You get the contact__r object first, then get Name from it. Not sure exactly how you do this in the office toolkit.


VarunCVarunC
:( ... I needed this to work in office tookit. THERE is NO EXAMPLE on How to Read Relationship Fields from a Query executed using Toolkit Library.
 


SuperfellSuperfell
Looking at the docs, i'm guessing you'd use the GetJoinResults method.


VarunCVarunC
Yes, but it Isn't mentioned anywhere in docs What and How exactly we cna use this function :(.
 
GetJoinResults(__MIDL_0016 As String) As SForceOfficeToolkitLib4.SObject4
 
I mean how can we figure out what is this Parameter [ __MIDL_0016 As String ] here stands for ... and also it is executed with an instance of SObject4 and also returns SObject4 value. I'm really out of ideas on how to make this function work :(
 
Can you please provide me some example of how to use this function ... ? plz


SuperfellSuperfell
http://britishboyindc.com/?p=16
This was selected as the best answer
VarunCVarunC
SimonF wrote: 
http://britishboyindc.com/?p=16 
 
gr8 ... a life saver post ... :) ... how did you managed to find this post ... 
 
I think this must get me started ... Thanks .. I will revert back for help if i couldn't make it to work .. 


SuperfellSuperfell
I used the search box at the top left corner of the page and searched for GetJoinResults.


VarunCVarunC
Anyways thanks. for confirmation. I got it to work. Thanks. :)


suresh.csksuresh.csk
Hi Simon.
 
I'm sorry to initiate the same topic.
I check your blog "http://britishboyindc.com/?p=16" not able to get the answer.
 
Trying to get query the following Object A and Object B.
Object B has a lookup relationship with ObjectA.
 
The below  doesnt work in Excel using Office Tool Kit.
 
Select Id, Name, (Select Id, Name From TestB__r) From TestA__c
 
Error:
sobject type testb__r  is not supported
 
 
I really appreciate your help
cheers
suresh