Skip to main content
Topics#Salesforce DeveloperLisa Schutter の質問
Lisa Schutter が「#Salesforce Developer」で質問
2017年1月6日 12:57

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'
3 件の回答
  1. James Loghry
    2017年1月6日 13:42
    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'

     
回答を記入...
0/9000