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
Alaric WimerAlaric Wimer 

How do I create this SOQL query?

I'm trying to come up with a SOQL query that displays the following:


Account Name | Opportunity Closed Date (most recent, won) | Product Purchased | Opportunity Amount

So far I've only been able to get as far as this:
  
SELECT Account.Name, CloseDate, Amount, (SELECT Product2.Name FROM OpportunityLineItems) FROM Opportunity WHERE IsClosed = true AND StageName = 'Closed Won'

 


If this is not possible with SOQL, is it possible with Salesforce Reports? Thank you!

Suraj MakandarSuraj Makandar
Hi Alaric Wimer,

Your SOQL is correct, you just need to add ORDER BY clause to get most recent opportunities first.

Please find updated SOQL below:
  
SELECT Account.Name, CloseDate, Amount, (SELECT Product2.Name FROM OpportunityLineItems) FROM Opportunity WHERE IsClosed = true AND StageName = 'Closed Won' ORDER BY Closedate DESC

Please mark it as best answer if the information is informative.

Thanks,
Suraj
 
Alaric WimerAlaric Wimer
Actually, I’m trying to get only the most recent closed won opportunity to display for each Account. Is this possible?
Xavier MametXavier Mamet
Hi,
a process builder should do the job: after an opportunity is closed/won, update the related Account to set a custom field (lookup to Opportunity) with the Id of the current Opportunity.
This way you have on each account a reference to its latest closed/won opportunity
Alaric WimerAlaric Wimer
Unless I'm mistaken, I could see that working for records moving forward. However, I need this information for all current Account records.