function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
JD2010JD2010 

Trigger to find max amount in Opportunity

I wrote a trigger to find the largest amount value for all Opportunities under a particular Account and then update a custom field on Account with the name of the Opportunity.  It works fine with single records but as you can see it doesn't take bulk processing into account and fails because it calls too many SOQL queries.

 

I've tried putting the queries into maps but I generally end up with null values.  If someone could point me in the right direction, I think I could figure it out, but I'm kind of stuck right now.

 

Thanks

 

trigger UpdateLoanName on Opportunity (before update) {
  for (Opportunity opp : Trigger.new){
    if(opp.AccountId != null){
      Opportunity oppname = [SELECT name, amount FROM Opportunity WHERE accountid = :opp.AccountId ORDER BY amount DESC LIMIT 1];
      Account acc = [SELECT loan_name__c FROM Account WHERE id = :opp.AccountId];
      acc.loan_name__c = oppname.name;
      if(acc.loan_name__c != null){
        update acc;  
      }
    }
  }
}
Best Answer chosen by Admin (Salesforce Developers) 
dmchengdmcheng

Your trigger should do the following:
* Loop through the trigger opps and create a set of account IDs.
* Use a SQL relational query to get a list of Accounts with related opps based on the account ID set you just created:
Account[] accts = [select Id, Loan_Name__c, (select Name, Amount from Opportunities order by Amount DESC limit 1) from Account where Id in :acctIdSet];

* Loop through the account list and set your custom field to the opp name using this syntax:
for(Account acct : accts) {
    acct.Loan_Name__c = acct.Opportunities[0].Name;
}

* Perform a DML Update using the list of Accounts that you've modified.

For info on SOQL relational queries:
http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_relationships.htm

All Answers

dmchengdmcheng

Your trigger should do the following:
* Loop through the trigger opps and create a set of account IDs.
* Use a SQL relational query to get a list of Accounts with related opps based on the account ID set you just created:
Account[] accts = [select Id, Loan_Name__c, (select Name, Amount from Opportunities order by Amount DESC limit 1) from Account where Id in :acctIdSet];

* Loop through the account list and set your custom field to the opp name using this syntax:
for(Account acct : accts) {
    acct.Loan_Name__c = acct.Opportunities[0].Name;
}

* Perform a DML Update using the list of Accounts that you've modified.

For info on SOQL relational queries:
http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_relationships.htm

This was selected as the best answer
JD2010JD2010

Thanks!  I'm going to give that a shot and see what I can come up with.

JD2010JD2010

I rewrote the trigger with the information you gave me and that really seemed to do the trick.  Thanks again!

SteveBowerSteveBower

Just in case you hadn't thought of it...

 

Remember that one form of an update to an Opportunity is to "reparent" it.  E.g. If Opportunity A was part of Account A, but now Opportunity A is edited to belong to Account B.

 

It's not enough to just update Account B with the new Max Opportunity.   You must also do Account A which may have just lost it's largest Opportunity.

 

So, when you cruise through the Opportunities in the Trigger building a list of Accounts to recheck, for an update you have to take the Accounts from the Opportunities in both Trigger.new and Trigger.old, not just one of them.

 

 

Also, your trigger was just for update.  Don't forget insert, delete, and undelete.

 

Best, Steve.

 

 

JD2010JD2010

I didn't think of the re-parenting issue.  I'll have to integrate that into the trigger.  Thanks for pointing that out.  

sfdcfoxsfdcfox

Just my two cents here, but you might as well also make the field a lookup instead of a text field. That way you can click through to the relevant opportunity on the detail page and reports, and you don't have to worry about what happens when someone decides to rename the opportunity...