Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Sailor67Sailor67 

Need help with relationship Query..

Hi,
 
Is there a way to modify the following script to also include User?
 

SELECT Account.Name, Account.OwnerId, Name, Description, (SELECT Id, Quantity, PriceBookEntry.Product2.Name FROM OpportunityLineItems) FROM Opportunity

 

 

I need to access some User fields such as User.FirstName etc

 

Thanks for any hints

 

 

SuperfellSuperfell
Which user? the user that created the row, the user who owns it, the user who last updated it?
Sailor67Sailor67

Sorry, I should have been more specific..

With the current script I can get: Account.OwnerId and Opportunity.OwnerId.

Since these fields have relationship with User.Id, I want to be able to access the User table in order to map a user name with a specific Account and Opportunity.

I hope this will make more sense

 
SuperfellSuperfell
Account.Owner.Name and Opportunity.Owner.Name see the examples in the SOQL-R docs.
Sailor67Sailor67
Thanks Simon,
 
I can't believe I missed that.. ->  it solved half my problem.
 
But I still need to access a few custom fields in User (e.g User.MyField__c) is there a way to include this in the same Query or do I need to split it up?
 
I can't find a child-parent relation between Account and User (or vice verse via Opportunity)
 
Thanks
Lars
 
 
 
SuperfellSuperfell
select owner.region__c from account

I'd recommend you try out some of the schema/query builder tools, they make this kinda stuff easy.
Sailor67Sailor67

Thanks again,

Yes it is possible to access Account.Region__c, but I am afriad I can't se how this will help me access two custom fields in table User if I am using my original script above.

My task is to setup a scheduled batch job via the API using ASP.NET/C#  and query a significant number of records.

Although the overall  performance is not critical, I would really prefer to modify the original script to get my last two fields (rather than splitting it up..).

Thanks 

 
SuperfellSuperfell
select owner.region__c from account selects the region__c field from the user object row that is the owner of the account. Would highly recommend you go read the SOQL-R section of the API docs.
Sailor67Sailor67

Hi Simon,

Well, I got it wrong so many thanks for pointing me in the right direction!

I did read the documentation, but I admit I am bit of a novice to SOQL.

Everything seems to work fine now and here is the modified script in case it would help someone else.
 
SELECT Account.Name, Account.Owner.NameName, Account.Owner.Region__c, Description, Owner.Name, (SELECT Id, Quantity, PriceBookEntry.Product2.Name FROM OpportunityLineItems) FROM Opportunity
 
Many thanks
Lars