How to get an export of Opportunities, but only include one per Account?
Anyone have some bright ideas about how to pull a report with only the most recent Opportunity for each Account?
This will give you the opportunity record with the max close date per accountid.
- Create temporary rollup summary field on Accounts that summarizes MAX(CloseDate) from Opportunities
- Create a cross-object formula to display that field on the Opportunities
- Create a formula field on Opportunities to compare Close Date on Opps to the cross-object field shows the max close date from Accounts.
- Should result in a True (the two dates match) / False (the two dates don’t match)
- Pull an Opportunity report where the custom field = True to yield the most recent Opportunities for each Account
So this way you have something like
And then you can create custom formulas for any of the values you wish to pull from the Opportunity (without having to update the trigger), such as:
Account.Recent_Opportunity__r.Name (Opportunity Name)
The following code will update the accounts for you based on what I described above:
Executing the following code from the "System Log" window will work based on what I described above:
Unfortunately we're in code lock in the org, so can't change anything at this point, even a scheduled Apex class, that's why I needed the data loader solution.
But I wish I'd thought of that lookup idea sooner. Version 2 feature for sure. Thanks!
BTW, you can execute apex code using the System Log on your production org as a workaround (though I haven't tried it with anything more than may be 15K records). Otherwise it looks like you may be stuck with running a vanity update on 1.6 mil Opportunities :\
Maybe you can get the guys at Appirio to build an export option to their SOQL query tool? =P
Luckily this is a one-time need, the trigger will maintain the data appropriately going forward. Tis the reality with last minute requirements I suppose.
That idea has certainly been put out there, and is being considered. I'd love to see that available.