Help me write a SOQL Query showing rep director of accounts and quantities of select items sold from sales orders of their accounts. - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
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.

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
Deepak AnandDeepak Anand
I see. Can you also try:
SELECT 
        Rep_Director__c, 
        ( 
            SELECT 
                    Description__c, 
                    Order_Quantity__c, 
                    Stock_Code__c  
            FROM    Order_Detail__r 
            WHERE   Stock_Code__c LIKE '%6195%' 
        ) 

FROM    Order_Header__c

All Answers

Amber BoazAmber Boaz
You'll probably get better responses if you post this question in the developer forums (https://developer.salesforce.com/forums/#!/feedtype=RECENT&criteria=ALLQUESTIONS) or on Stack exchange: http://salesforce.stackexchange.com/
Best of luck!
Deepak AnandDeepak Anand
Let me try, so the Relationship is like this (Correct ??!!??) : 
User-added imageSo the SOQL should be kinda like this:
SELECT 
        Rep_Director__c, 
        ( 
            SELECT 
                    Description__c, 
                    Order_Quantity__c, 
                    Stock_Code__c  
            FROM    Order_Details__r 
            WHERE   Stock_Code__c LIKE '%6195%' 
        ) 

FROM    Order_Header__C

 
Jeff MayJeff May
This Answers Community is focused on configuration and design questions. Programmatic questions are best submitted to the developer forums at https://developer.salesforce.com where the forums and participants are geared toward programming troubleshooting and support.
Aron SchorAron Schor
Thanks, I posted there just now and will do so going forward for these questions.  Deepak, I get this message when I try that.  "INVALID_TYPE:
Order_Quantity__c, Stock_Code__c FROM Order_Details__r WHERE Stock_Code__c
^
ERROR at Row:1:Column:88
Didn't understand relationship 'Order_Details__r' 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 WSDL or the describe call for the appropriate names."
Deepak AnandDeepak Anand
I see. Can you also try:
SELECT 
        Rep_Director__c, 
        ( 
            SELECT 
                    Description__c, 
                    Order_Quantity__c, 
                    Stock_Code__c  
            FROM    Order_Detail__r 
            WHERE   Stock_Code__c LIKE '%6195%' 
        ) 

FROM    Order_Header__c
This was selected as the best answer
Aron SchorAron Schor
Same error: INVALID_TYPE:
FROM Order_Details__r
^
ERROR at Row:8:Column:21
Didn't understand relationship 'Order_Details__r' 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 WSDL or the describe call for the appropriate names.
Deepak AnandDeepak Anand
Please look at the SOQL carefully! I have changed Order_Details__r to Order_Detail__r :-)
Aron SchorAron Schor
Thanks.  Two other things.  How do I not show the blank data.  I tried DECRIPTION__C !=null with no luck.  Also, how can I export to excel?  I tried adding "LIMIT 1000" and then view as Bulk CSV and it says.
Failed: InvalidBatch : Failed to process query: FUNCTIONALITY_NOT_ENABLED: Aggregate Relationships not supported in Bulk Query

User-added image
Deepak AnandDeepak Anand
Oh! If it is for Exporting, then I guess you can try creating a Report Type where:
  1. Primary Object: Order Header
  2. Second Level Object: Order Detail
You can then create a Report on this RT with a Cross Filter that says:
Order Header with Order Detail
And then you can Export it as a CSV!
Aron SchorAron Schor
I don't have access to create a report and our admin won't be able to this for awhile.  If its not possible with Workbench, if you could tell me how to remove the blank lines that would help,  !=null doesn't seem to work.
Deepak AnandDeepak Anand
I am sorry you cannot since the NULL is not on a single field rather is on the LIST OF ORDER DETAILS that is NULL here and that cannot be done on the SOQL!
Aron SchorAron Schor
Ah that's too bad.  I guess I will have to bug our admin to give me reporting access.  Thanks Deepak, greatly appreciated!
Aron SchorAron Schor
Actually someone in the Developer forums figured this out,
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%'
https://developer.salesforce.com/forums/ForumsMain?id=906F0000000AtSlIAK