+ Start a Discussion
Aron SchorAron Schor 

Help me write a SOQL Query showing rep director of accounts and quantities of select items sold from sales orders of their accounts.

(Note: I also posted in the Success forums but was told to post here.)

Hi, I am trying to create a SOQL query that shows Rep directors and quantities sold of select item from sales orders, which are somehow tied to accounts and are tied to rep directors.  I am not quite sure and that's the problem!  I want to see how good of a job the reps are in selling select items.   I can't run a Report because the data types don't seem to be connected so I am hoping I can use SOQL Query on Work Bench.

Note: I only know the basics with SOQL and I am not a Sales Force Admin.  Feel free to dumb down any replies!

I have tried three queries that I would think might work but don't (see below.)
I included hopefully more information that is necessary below and hopes someone can provide a query that would work. (see further below.)

These don't work.

1
SELECT Rep_Director__C, ( SELECT Description__c, Order_Quantity__c, Stock_Code__c FROM Order_Detail__C WHERE Stock_Code__C = ‘6195’ ) FROM Order_Header__r.Account__c
Error-“First SObject of the FROM statement must be a valid SObject type.”

2
SELECT Rep_Director__C, ( SELECT Description__c, Order_Quantity__c, Stock_Code__c FROM Order_Detail__C WHERE Stock_Code__C LIKE = ‘6195’ ) from Order_Header__C
Error-“Bind variables only allowed in Apex code”

3
SELECT Description__c, Order_Header__C, Order_Quantity__c, Stock_Code__c, ( SELECT Rep_Director__C from Order_Detail__c.Order_Header__c ) FROM Order_Detail__C WHERE Stock_Code__C LIKE ‘6195’

“Due to issues rendering query results, parent relationship queries are disabled by default. If you understand these limitations, parent relationship queries can be enabled under Settings. Alternatively, parent relationship queries can be run with REST Explorer under the Utilities menu without issue.”

When I change the settings it says
“Didn’t understand relationship ‘Order_Detail__c.Order_Header__c’ in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the ‘__r’ after the custom relationship name. Please reference your

->Some information that might be helfpul.

I In SalesForce

1 If I go to Build-Create-Objects-Sales Order, under Custom Fields & Relationships it says
Field Name-Account
API Name-Account C
Data Type-Master-Detail(Account)

Field Name-Rep Director
API Name-Rep_Director__c
Data Type-Text(3)

It also says Account is Master Object for Sales Order

2 If I go to Build-Create-Objects-Sales Order Line, under Custom Fields & Relationships it says
Field Label-Order Header
API Name-Order_Header__C
Data Type-Master-Detail(Sales Order)

It also says Sales Order is Master Object for Sales Order Line

II In Workbench, if I go to Standard & Custom Objects

1 Account-it says this under Child Relationships:
Order_Header__c.Account__c
-cascadeDelete: true
-childSObject: Order_Header__c
-deprecatedAndHidden: false
-field: Account__c
-relationshipName: Orders__r

For Account-it says this under Fields:
Rep_Director__c

2 Order_Header__c-it says this under Child Relationships:
Order_Detail__c.Order_Header__c
-cascadeDelete: true
-childSObject: Order_Detail__c
-deprecatedAndHidden: false
-field: Order_Header__c
-relationshipName: Order_Detail__r

3 Order_Detail__C-It says this under Fields
Order_Header__c

Thanks!
Best Answer chosen by Aron Schor
Suneel#8Suneel#8
Can you try below SOQL

SELECT Description__c, Order_Header__C, Order_Quantity__c, Stock_Code__c,Order_Header__r.Rep_Director__C FROM Order_Detail__C WHERE Stock_Code__C LIKE '%6195%'

All Answers

Suneel#8Suneel#8
Can you try below SOQL

SELECT Description__c, Order_Header__C, Order_Quantity__c, Stock_Code__c,Order_Header__r.Rep_Director__C FROM Order_Detail__C WHERE Stock_Code__C LIKE '%6195%'
This was selected as the best answer
Aron SchorAron Schor
Wow, this is exactly what I needed Suneel!  I never would have figured it out.  I had never see components with ',' in them (Stock_Code__c,Order_Header__r.Rep_Director__C).  I actually didn't think it was possible based on my replies in the other forums.  https://success.salesforce.com/answers?id=90630000000CmaZ  Thanks so much!