0

While both Quote Line Item and Order Product have a lookup fields to Product, no relationship name is shown in Workbench.

Further digging indicates a somewhat hidden PriceBookEntry object exists in between.

OK, but since we can't query down more than one level, is there any easy way to make a single SOQL query to list ALL Products with ALL Quote Line Items & ALL Order Products?

It seems like it would be a basic thing, but I am stumped. Schoolboy error?

For example - the following simply does not work, even though there are lookup fields from both OrderItem and QuoteLineItem to Product:

Select id, (Select id from OrderItems), (Select id from QuoteLineItems) FROM Product2
2
  • It's not an error on your end, there actually is no relationship name (at least for OrderItem). You can't pull everything with a single query.
    – Adrian Larson
    Jul 23 '20 at 17:11
  • Thanks for letting me know I'm not crazy, and further testing indicates this is an intentional SF design decision to force the use of the intended price book entry / currency. But in this case it's not relevant and I just want a list of Quote Line Items /Order Products for an outside use case via - ideally one - API query. Any ideas on if this is possible and best way to achieve? Appreciate any thoughts as this is not my expertise. Jul 24 '20 at 5:58
1

As you can see from Workbench, there are no relationshipNames for Product2 to OrderItem and Product2 to QuoteLineItem. Yet you can query from Product2 to PricebookEntry

enter image description here

However, you can query from PricebookEntry to both OrderItem and QuoteLineItem

so you could do:

SELECT id, Product2.Name, (select Id from OrderItems), (select Id from QuoteLineItems) 
  FROM PricebookEntry 

or

SELECT id, Product2.Name, (select id from OrderItems), (select id from QuoteLineItems) 
  FROM PricebookEntry 
  WHERE Product2.ProductCode IN ('foo','bar')

and you can add whatever lookup fields you need to get Order details or Quote details

If in a multicurrency org, add CurrencyIsoCode to the returned fields or include in Where clause to get a single currency. If in a multi-pricebook org , add Pricebook2.Name to returned fields or WHERE clause

1
  • Wow, thanks very much for taking the time to put this together and yes, that approach should work! Jul 26 '20 at 11:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.