I need help writing a SOQL query to see Accounts that purchase only products associated with Product Family "Clauss" and/or "Camillus."

I am guessing I need to do a special query that utilizes roll ups which is a bit over my head.  Below are some details I assume would be useful in terms of the relationships between fields.

I can see the Account info from Order_Header__c
SELECT Account__C, Customer_Number__c FROM Order_Header__c LIMIT 10

If I go to Order_Header__C and Child Relatonships I see
Order_Detail__c.Order_Header__c

I can see Product Family if I do this query.
SELECT Family FROM Product2 LIMIT 10

If I go to Product2 for Child Relationships it says
Order_Detail__c.Product__c

I can see the base stock code here
SELECT Product__c, Base_Stock_Code__C FROM Order_Detail__c LIMIT 10

Thanks, Aron.

Hi,

I am not sure if this is possible, but I would like to see a list of products a certain company has purchased, and the quantity purchased over a period of time.
ie
General Co Item A 25
General Co Item B 50

I have done some things on my own but can't quite get this.  I am still a bit new to SOQL.

This query works: (No rollup)
SELECT Description__c, Order_Quantity__c, Stock_Code__c, Order_Header__r.Customer_Name__C FROM Order_Detail__C WHERE Stock_Code__C = '6195'

This query works: (With Rollup, but no products) (it essentially shows how many Sales Orders we have in the system per Account.)
SELECT customer_Name__C, COUNT(id) total
    FROM Order_header__c
    GROUP BY ROLLUP(customer_Name__C) LIMIT 25

This query doesn't work (MALFORMED_QUERY: Field must be grouped or aggregated: Customer_Name__c)
SELECT Description__c, Order_Quantity__c, Stock_Code__c, Order_header__r.Order_Date__C, Order_Header__r.Customer_Name__C, COUNT(id) total
 FROM Order_Detail__C
 WHERE Order_Header__r.Customer_Name__C LIKE 'Conney' AND Order_header__r.Order_Date__C >= 2015-01-01
 GROUP BY ROLLUP(Stock_Code__C)

I have done some digging and found some possible terms to use but not sure which to use here or how.
count(id)
sum(pid= )
union all

Any help would be appreciated

Aron

I am having problems with this query, which I believe to be similar to the second basic one.

SELECT Order_Value__C
    ( SELECT Warehouse__C FROM Order_Detail__C )    FROM Order_header__c

I assume the relationship here is the same?
SELECT Id, Name, Industry, AnnualRevenue,
    ( SELECT Name, Email, BirthDate FROM Contacts )    FROM Account

It says

MALFORMED_QUERY:
( SELECT Warehouse__C FROM Order_Detail__C
^ ERROR at Row:2:Column:6
unexpected token: 'SELECT'

In our System API Name Order_Details__c show
Field Label Order Header
API NAME Order_Header__c
Data Type Master-Detail(Sales Order) 

I assume the relationship would be the same and it would work?

Individually both of these work
This works SELECT Order_Value__C FROM Order_header__c
This works SELECT Warehouse__C FROM ORDER_Detail__C

(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!

I need help writing a SOQL query to see Accounts that purchase only products associated with Product Family "Clauss" and/or "Camillus."

I am guessing I need to do a special query that utilizes roll ups which is a bit over my head.  Below are some details I assume would be useful in terms of the relationships between fields.

I can see the Account info from Order_Header__c
SELECT Account__C, Customer_Number__c FROM Order_Header__c LIMIT 10

If I go to Order_Header__C and Child Relatonships I see
Order_Detail__c.Order_Header__c

I can see Product Family if I do this query.
SELECT Family FROM Product2 LIMIT 10

If I go to Product2 for Child Relationships it says
Order_Detail__c.Product__c

I can see the base stock code here
SELECT Product__c, Base_Stock_Code__C FROM Order_Detail__c LIMIT 10

Thanks, Aron.

Hi,

I am not sure if this is possible, but I would like to see a list of products a certain company has purchased, and the quantity purchased over a period of time.
ie
General Co Item A 25
General Co Item B 50

I have done some things on my own but can't quite get this.  I am still a bit new to SOQL.

This query works: (No rollup)
SELECT Description__c, Order_Quantity__c, Stock_Code__c, Order_Header__r.Customer_Name__C FROM Order_Detail__C WHERE Stock_Code__C = '6195'

This query works: (With Rollup, but no products) (it essentially shows how many Sales Orders we have in the system per Account.)
SELECT customer_Name__C, COUNT(id) total
    FROM Order_header__c
    GROUP BY ROLLUP(customer_Name__C) LIMIT 25

This query doesn't work (MALFORMED_QUERY: Field must be grouped or aggregated: Customer_Name__c)
SELECT Description__c, Order_Quantity__c, Stock_Code__c, Order_header__r.Order_Date__C, Order_Header__r.Customer_Name__C, COUNT(id) total
 FROM Order_Detail__C
 WHERE Order_Header__r.Customer_Name__C LIKE 'Conney' AND Order_header__r.Order_Date__C >= 2015-01-01
 GROUP BY ROLLUP(Stock_Code__C)

I have done some digging and found some possible terms to use but not sure which to use here or how.
count(id)
sum(pid= )
union all

Any help would be appreciated

Aron

I am having problems with this query, which I believe to be similar to the second basic one.

SELECT Order_Value__C
    ( SELECT Warehouse__C FROM Order_Detail__C )    FROM Order_header__c

I assume the relationship here is the same?
SELECT Id, Name, Industry, AnnualRevenue,
    ( SELECT Name, Email, BirthDate FROM Contacts )    FROM Account

It says

MALFORMED_QUERY:
( SELECT Warehouse__C FROM Order_Detail__C
^ ERROR at Row:2:Column:6
unexpected token: 'SELECT'

In our System API Name Order_Details__c show
Field Label Order Header
API NAME Order_Header__c
Data Type Master-Detail(Sales Order) 

I assume the relationship would be the same and it would work?

Individually both of these work
This works SELECT Order_Value__C FROM Order_header__c
This works SELECT Warehouse__C FROM ORDER_Detail__C

(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!