function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Lisa SchutterLisa Schutter 

Opportunity line items in the Developer Console

Hi all,

I need to write a query which shows opportunity information and opportunitylineitem information. Underneath you find my query so far. I tried putting in OpportunityLineItem.Product2.Name but I got an error. Is there somebody that can help me? I am using the Developer Console to check my query, afterwards I will export the information trough the data loader. 

SELECT Name, StageName, Substage__c, Closedate, Opportunity_number__c, Navision_Order_Number__c, Installer__r.Name, Payment_Account__r.Name, Payment_Account__r.PaymentAccountNumber__c, Payment_Account__r.Billing_Country__c, Shipping_Country__c, Opportunity_Product.Product2.Name, Payment_Account__r.id, id FROM Opportunity WHERE Closedate > 2015-01-01 AND Closedate < 2017-01-01 AND StageName != 'Closed Lost' AND StageName != 'Intake'
Best Answer chosen by Lisa Schutter
James LoghryJames Loghry
Hi Lisa,

Opportunity Line Items are children of Opportunities.  To get both Opportunity and Opportunity Line Item (or Opportunity Product) information, you'll either need to query from Opportunity Line Items and bring in their parent Opportunity information, or use a subquery on the Opportunity that pulls in Opportunity Line Items.

For some examples, see this document: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_query_using.htm#sforce_api_calls_soql_relationships_query_using

Because you're using the developer console to query information, I would query the Opportunity Line Items, and bring in the parent Opp information like so:
  
SELECT Opportunity.Name, Opportunity.StageName, Opportunity.Substage__c, Opportunity.Closedate, Opportunity.Opportunity_number__c, Opportunity.Navision_Order_Number__c, Opportunity.Installer__r.Name, Opportunity.Payment_Account__r.Name, Opportunity.Payment_Account__r.PaymentAccountNumber__c, Opportunity.Payment_Account__r.Billing_Country__c, Opportunity.Shipping_Country__c, Product2.Name, Opportunity.Payment_Account__r.id, id FROM OpportunityLineItem WHERE Opportunity.Closedate > 2015-01-01 AND Opportunity.Closedate < 2017-01-01 AND Opportunity.StageName != 'Closed Lost' AND Opportunity.StageName != 'Intake' Order By Opportunity.CloseDate Desc, Opportunity.Id



The above query will give you an exploded list of all the opportunity line items under any opportunity matching your criteria.  Alternatively, you could do something like the following using a subquery, but digging through the Opportunity Line Items in developer console is kind of a pain:
  
SELECT Name, StageName, Substage__c, Closedate, Opportunity_number__c, Navision_Order_Number__c, Installer__r.Name, Payment_Account__r.Name, Payment_Account__r.PaymentAccountNumber__c, Payment_Account__r.Billing_Country__c, Shipping_Country__c, Payment_Account__r.id, id, (Select Product2.Name From OpportunityLineItems) FROM Opportunity WHERE Closedate > 2015-01-01 AND Closedate < 2017-01-01 AND StageName != 'Closed Lost' AND StageName != 'Intake'

 

All Answers

James LoghryJames Loghry
Hi Lisa,

Opportunity Line Items are children of Opportunities.  To get both Opportunity and Opportunity Line Item (or Opportunity Product) information, you'll either need to query from Opportunity Line Items and bring in their parent Opportunity information, or use a subquery on the Opportunity that pulls in Opportunity Line Items.

For some examples, see this document: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_query_using.htm#sforce_api_calls_soql_relationships_query_using

Because you're using the developer console to query information, I would query the Opportunity Line Items, and bring in the parent Opp information like so:
  
SELECT Opportunity.Name, Opportunity.StageName, Opportunity.Substage__c, Opportunity.Closedate, Opportunity.Opportunity_number__c, Opportunity.Navision_Order_Number__c, Opportunity.Installer__r.Name, Opportunity.Payment_Account__r.Name, Opportunity.Payment_Account__r.PaymentAccountNumber__c, Opportunity.Payment_Account__r.Billing_Country__c, Opportunity.Shipping_Country__c, Product2.Name, Opportunity.Payment_Account__r.id, id FROM OpportunityLineItem WHERE Opportunity.Closedate > 2015-01-01 AND Opportunity.Closedate < 2017-01-01 AND Opportunity.StageName != 'Closed Lost' AND Opportunity.StageName != 'Intake' Order By Opportunity.CloseDate Desc, Opportunity.Id



The above query will give you an exploded list of all the opportunity line items under any opportunity matching your criteria.  Alternatively, you could do something like the following using a subquery, but digging through the Opportunity Line Items in developer console is kind of a pain:
  
SELECT Name, StageName, Substage__c, Closedate, Opportunity_number__c, Navision_Order_Number__c, Installer__r.Name, Payment_Account__r.Name, Payment_Account__r.PaymentAccountNumber__c, Payment_Account__r.Billing_Country__c, Shipping_Country__c, Payment_Account__r.id, id, (Select Product2.Name From OpportunityLineItems) FROM Opportunity WHERE Closedate > 2015-01-01 AND Closedate < 2017-01-01 AND StageName != 'Closed Lost' AND StageName != 'Intake'

 
This was selected as the best answer
Lisa SchutterLisa Schutter
Thank you James! I just tried it, seems to work! The only problem is that I see [object] [object] in my console for ceveral columns. Any idea about that?
User-added image
James LoghryJames Loghry
Yeah, developer console doesn't work well with relationships unfortunately.  You could go through and click into the [object Object], and a window will pop up with the info you're looking for.  Otherwise, you'll want to use something else like workbench like the SOQL Query in (https://workbench.developerforce.com/)

If you go the workbench route, make sure you click the "Workbench" logo in the top left, go to settings, and check the box near the bottom that says "Allow SOQL Parent Relationship Queries" before running your query.