Apex Code Development You need to sign in to do that
Don't have an account?
drk how to query products related to an opportunity
I am having trouble figuring out what data I want to query from the API. For instance, I want to get all products realted to an opportunity. I looked at the documentation and it says that opportunityLineItem is related to Product via ProductID. I do not have a ProductID in the product table. In the big picture, I am trying to dump a lot of the data into a sql database (real time, on demand) so that we can write reports against it. I'm assuing the WSDL is the way to go. Is the database schema denomalized for use in the API?
What is product verses product2 object? pricebook verses pricebook2
Thank you.
Drk,
Product and Pricebook are the older (pre-spring 04 release) objects.
I'd suggest you ignore them and concentrate on the newer Product2 and Pricebook2 entities.
For the relationships, check out the Entity Relationship Diagrams in Chapter 6 of the API document. In particular, page 166 shows the relationships between
Opportunity
Pricebook2
OpportunityLineItem
PricebookEntry
Product2
Opportunity is linked to a pricebook:
Opportunity.Pricebook2Id -> Pricebook2.Id
Opportunity line items are linked to the opportunity, and to the pricebookentry:
OpportunityLineItem.OpportunityId -> Opportunity.Id
OpportunityLineItem.PricebookEntryId -> PricebookEntry.Id
PricebookEntry values are linked to the products and the pricebook:
PricebookEntry.Product2Id -> Product2.Id
PricebookEntry.Pricebook2Id -> Pricebook2.Id
Scot
Hi Scot,
do you know if there is a link directly from the OpportunityLineItem.ProductId to the Product2.Id?
Cheers
Nick
Nick,
Nope - the field "Product ID" in the OpportunityLineItem is a link to the deprecated "Product", not to Product2. As such, it would really only be useful if you were working with the old model ... and only the old model.
This is described in a little more detail in the 4.0 API document, Page 133.
Scot
I'm in the process of building out an application that requires me to export all Opportunities, along with their product detail.
I cam across this thread, and wanted to ensure I'm following along correctly.
We are still using the 3.0 API as our end point, as an FYI.
I've done some initial testing, and am getting mixed results with Product/Pricebook and Product2/Pricebook2.
It *appears* I should be using the Product2 object just based on the results, but I wanted to confirm this.
As i'm a bit confused as to why I would get query results for both objects, if only one is really in use.
Can you help me better understand this?
Thanks!
Hi sash,
You should only use the "2" entities (product2 and pricebook2).
The ralationship is
(1)Opportunity --> (n)OpportunityLineItems
(1)OpportunityLineItems --> (1)PriceBookEntry
(1)PriceBookEntry --> (1)Product2Id
So for an opportunity,
1) Query all OpportunityLineItems where the OpportunityID = the id of your opportunity "Select Quantity, ServiceDate, UnitPrice, PriceBookEntryId From OpportuntityLineItems Where OpportunityId = 'an id value'". (You can retrieve more detail items as necessary).
2) Using the Retrieve call and the ids of the PriceBookEntryIds from the previous query, get the details for each pricebook entry Retrieve("Name, ProductCode, UnitPrice Product2Id", "PriceBookEntry", {ids}).
3) With the product2ids get any additional details about the products that may be on the product2 table. Retrieve("Description, Family, ...", "Product2", {ids}).
I think I have a similar situation.
I want to find all unique opportunities that contain a certain PricebookEntryId in the OpportunityLineItems. Not only that, there is custom fields in Opportunity and line item objects that needs to be factored in.
So I want something like,
Select Id, Name, x, y, z, a,b, c, (Select Id from OpportunityLineItem), (Select ContactId from OpportunityContactRole)
from Opportunity
where Active__c=true and Opportunity.OpportunityLineItem.code__c='S' and Opportunity.OpportunityLineItem.PricebookEntryId='xxxxxxxxxxxxx'
Can something like this be done. I need to retrieve the data and export it to excel.
Furthermore, I want to clone all the opportunities I get from this query by just changing the PricebookEntryId in the OpportunityLineItem object. What is the best way of achieving this?
Thanks.