function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
AlexCRMmanagerAlexCRMmanager 

OpportunityLineItems relationship query

I'm having trouble figuring out the right syntax to get the following data using a SOQL relationship query:

Product Code, Quantity, Total Price, Product Name

from Products and Opportunity Line Items

for a specific OpportunityID

Can anyone tell me how I need to write my SOQL query to get this data?

Many thanks.....




sfdcfoxsfdcfox
Alex,

This requires requesting data from a couple of different tables. I got this to work using this:
Select Id, Name,
(Select Id,TotalPrice, PricebookEntry.ProductCode,
PricebookEntry.Name From OpportunityLineItems) From Opportunity
Where Id = '{!Opportunity.Id}'
I added the line breaks for readability, of course.
AlexCRMmanagerAlexCRMmanager
Hi sfdcfox,

Thanks very much for your quick reply to my post!! Your query was the framework that I needed to get my SOQL relationship query to work. Here is the final product:

Code:
Select Id, Name, 
(Select Id, Quantity, TotalPrice,
PricebookEntry.Product2.ProductCode,
PricebookEntry.Product2.Name
From OpportunityLineItems)
From Opportunity Where Id = '{!Opportunity.Id}'
(Once again, line breaks for readability)

This query gets the ProductCode and ProductName from the Product2 object through the relationship in the PricebookEntry object.

The reason I couldn't get it to work initially was that I didn't know the right syntax to use, and I didn't realize that I needed to start the query on the Opportunity object.

Thanks again,

AlexCRMmanager

Message Edited by AlexCRMmanager on 04-05-2007 09:03 PM

sfdcfoxsfdcfox
Both methods are equally correct. You don't need to reference Product2 unless the fields you're looking for aren't in the PricebookEntry. PricebookEntry has read-only fields that reference Name and ProductCode from the Product2 table, so I used those. Before API relationships, you had to use those fields or suffer from an additional API call. I don't know if it's more "efficent" in terms of calls to use one over the other, or if anyone even cares. Oh, although you might need to use the PricebookEntry version of the field if you don't need to otherwise reference Product2 and you have 19 other relationships in one query.