Ask Search:
Matthew LambMatthew Lamb 

How to get an export of Opportunities, but only include one per Account?

I've got an existing system with approx. 500K Accounts and 1.6M Opportunities. We've written an Opportunity trigger to take several values from the most recent Opp (per the Close Date) and copy those values to the Account. It will always keep the values from whatever the most recent Opp is on the Account level. My challenge is how to get the trigger to fire and update the Accounts initially. I want to avoid pushing an update to the entire Opportunity table, since that will eat lots of unnecessary API calls.

Anyone have some bright ideas about how to pull a report with only the most recent Opportunity for each Account?
Javier GonzalezJavier Gonzalez
Export opportunities to a SQL DB table then run a query....SQL may be something like this

select accountid, max(closedate)
from oppportunitydumptable
group by accountid

This will give you the opportunity record with the max close date per accountid.
Matthew LambMatthew Lamb
I don't have access to a SQL environment, really hoping to do this all in SFDC. What about this? It's kinda hacky, but what issues would I come up with going this route?
  1. Create temporary rollup summary field on Accounts that summarizes MAX(CloseDate) from Opportunities
  2. Create a cross-object formula to display that field on the Opportunities
  3. Create a formula field on Opportunities to compare Close Date on Opps to the cross-object field shows the max close date from Accounts.
    1. Should result in a True (the two dates match) / False (the two dates don’t match)
  4. Pull an Opportunity report where the custom field = True to yield the most recent Opportunities for each Account
Javier GonzalezJavier Gonzalez
Close Date is a Date. You can potentially have multiple opps closing on the same date, leading to multiple "true" values for an account. 
Matthew LambMatthew Lamb
True, need to analyize how many Opps I have closed on the same date in a given account if I went that route. 
Javier GonzalezJavier Gonzalez
What is you normal API usage and how how  much do you normally have left over? Using the Bulk API  you may be able to do it. Another option is to request a temporary API increase via support. I have done so. Let thiem know it is an emergency and that you will only need it for a day.
Matthew LambMatthew Lamb
Normal API limit is quite high and has already been increased (5 million), but this will be going on during a big go-live push with a bunch of other stuff going on, so I'm trying to minimize the API impact this one job has. Bulk API is a good call, I think my goofy extra field workaround plus bulk API will do the trick. And actually, since the trigger always users the most recent Opp, pushing two Opps under one Account will still result in the same data going to the Account.

Thanks Javier!
Javier GonzalezJavier Gonzalez
Sweet. Good luck bud.
Anthony VictorioAnthony Victorio
If you already have the trigger, you simply need to modify it to accept the Account Id's instead and build it as a scheduable class (or maybe use the "system log" function to execute the code).  But rather than copy the values of the opportunity and add them to the account, why not use a lookup relationship field?

So this way you have something like

Account.Recent_Opportunity__c (Lookup)

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:

set<Id> AccountIds = new set<Id>();
//Update for your own criteria
for(Account a : [Select Id from Account where Id = '0016000000JATF6']) {
map<Id,list<String>> RecentOppMap = new map<Id,list<String>>();
for(Opportunity o : [Select Id, AccountId from Opportunity where AccountId IN :AccountIds order by CloseDate asc]) {
RecentOppMap.put(o.AccountId, new list<string> {o.Id});
list<Account> AccountsToUpdate = new list<Account>();
for(Account a : [Select Id,Recent_Opportunity__c  from Account where Id IN :AccountIds]) {
list<String> OppIdList = RecentOppMap.get(a.Id);
//Use [0] to retrieve the first record of the list
string RecentOppId = OppIdList[0];
a.Recent_Opportunity__c = RecentOppId;
if(AccountsToUpdate.size()>0) {
update AccountsToUpdate;
Matthew LambMatthew Lamb
Anthony - Are you saying add a Lookup on Accounts that lookup up to Opportunities, then stuff the ID of the most recent Opp (still identified via the trigger) in that field? That's an awesome idea! 

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!
Anthony VictorioAnthony Victorio
Precisely, a lookup field on the Account record that looks up Opportunities.  :)

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 :\

User-added image

Maybe you can get the guys at Appirio to build an export option to their SOQL query tool? =P

User-added image

Matthew LambMatthew Lamb
Cool re: the system log, good to know. Probably not going to give a half million records in a brand new production environment a shot out of the gate, but glad to know that's an option, I've never used that feature.

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.