4

I am new to Programming and want to update count of opportunities grouped by final stage on Account whenever the final stage is updated. I want to achieve it using Trigger instead of RollUp summary fields.

trigger CalcuSummary on Account (after Update, after Insert){
 integer won,lost,nodeal;
  id aid;
  account a;
for(opportunity o : Trigger.new) {
   if(Trigger.newMap.get(o.Id).final_Stage__c != Trigger.oldMap.get(o.Id).final_Stage__c){
      aid = o.accountid;
      won = [select count() from opportunity where accountid=: aid and final_Stage__c='Won'];
      lost = [select count() from opportunity where accountid=: aid and final_Stage__c='Lost'];
      nodeal = [select count() from opportunity where accountid=: aid and final_Stage__c='No Deal'];
      a = new account(id=aid,Number_Won_Opty__c = won,Number_Lost_Opty__c =   lost,Number_No_Deal_Opty__c = nodeal);
}
  update a;
 }
}
  • Thanks @Mohith, are you going to put your two cents too ? – Jarvis Dec 13 '14 at 9:23
5

Since you're interested in rollup summaries, I'd take this approach:

trigger rollup on Opportunity(after insert, after update, after delete, after undelete) {
    // Records we want to update
    Map<Id, Account> accounts = new Map<Id, Account>();
    // Find matching records, initialize to zero values for account
    for(Opportunity record: Trigger.isdelete? Trigger.old: Trigger.new) {
        if((record.Final_Stage__c=='Won' || record.Final_Stage__c=='Lost' || record.Final_Stage__c=='No Deal')&&(Trigger.isDelete || Trigger.isUndelete || Trigger.isInsert || record.Final_Stage__c != Trigger.oldMap.get(record.Id).Final_Stage__c)) {
            accounts.put(record.AccountId, new Account(Id=record.AccountId, Number_Won_Opty__c=0, Number_Lost_Opty__c=0, Number_No_Deal_Opty__c=0));
        }
    }
    // This saves a longer if-then-else-if-then-else-if-then-else construct
    Map<String, SObjectField> fields = new Map<String, SObjectField> {
        'Won' => Account.Number_Won_Opty__c,
        'Lost' => Account.Number_Lost_Opty__c,
        'No Deal' => Account.Number_No_Deal_Opty__c };
    // Use aggregate result to sum the values
    for(AggregateResult result:[SELECT Final_Stage__c Stage, Count(Id) Sum, AccountId Account FROM Opportunity WHERE AccountId IN :accounts.keySet() AND Final_Stage__c IN ('Won','Lost','No Deal') GROUP BY Final_Stage__c, AccountId]) {
        // Dynamic programming for fields
        accounts.get((Id)result.get('Account')).put(fields.get((String)result.get('Stage')), result.get('Sum'));
    }
    update accounts.values();
}

Edit

First, I found an error, which is why this didn't make much sense. We want the mapped field, so we actually needed:

accounts.get((Id)result.get('Account')).put(fields.get((String)result.get('Stage')), result.get('Sum'));

I've corrected this above.

In pseudo-code, this is roughly:

  1. Get the Object from the AggregateResult aliased by the String 'Account'.
  2. Convert the data type of the Object from (1) to an Id.
  3. Get the Account from the Map that matches the Id from (2).
  4. Get the Object from the AggregateResult aliased by the String 'Stage'.
  5. Convert the data type of the Object from (4) to a String.
  6. Get the SObjectField from the Map that matches the String from (5).
  7. Get the Object from the AggregateResult aliased by the String 'Sum'.
  8. Place the value from (7) into the field referenced by (6) for the Account from (3).

Significant notes:

AggregateResult.get always returns an Object, which is almost never what we need. Therefore, we use casts to get to the correct data type (e.g. (Id)result.get('Account')).

An Account (or really, any SObject type), can dynamically set a field value with SObject.put(field, value). The field map translates the string 'Won', 'Lost', or 'No Deal' into the correct field we would like to use.

The second parameter for SObject.put(field, value) can be an Object, so we don't cast it, but it must be the correct data type; in this case, we aliased a number, and your field should be expecting a number value.

You could also express this as multiple steps, if you wanted to:

Id accountId = (Id)result.get('Account');
String finalStage = (String)result.get('Stage');
SObjectField mappedField = field.get(finalStage);
Decimal count = (Decimal)result.get('Sum');
accounts.get(accountId).put(mappedField, count);
| improve this answer | |
  • Hi Brian, could you please simplify the statement accounts.get((Id)result.get('Account')).put((String)result.get('Stage') I understand that it is conforming to map variable 'fields' you defined with 'Won','Lost','No Deal' as values of keys but I am unable to dissect it as I am new to programming – Jarvis Dec 13 '14 at 10:02
  • 1
    @Bennie I found a typo that explains some of the confusion you may have had, and also expanded the answer to include a proper explanation. You'll find that that very line of code in some form is probably found in most major code bases. I suspect it's partly a carry-over from the years of habit we developers were forced to build with the "script limit" problem we had. – sfdcfox Dec 13 '14 at 10:33
  • Hi Brian, thank you so much for your detailed explanation. It was really overwhelming and educating. We may also have to correct a part of IF condition to record.Final_Stage__c != Trigger.oldMap.get(record.id)Final_Stage__c – Jarvis Dec 13 '14 at 13:29
  • Hey Brian, I am getting DmlException: Update failed. First exception on row 0; first error: MISSING_ARGUMENT, Id not specified in an update call: [] for the expression you elaborated. I believe the line should read something like accounts.put(accountId, new account(mappedField=>count)); – Jarvis Dec 14 '14 at 13:28
  • 1
    @Bennie I updated my code and tested in my sandbox. Try it out now. – sfdcfox Dec 15 '14 at 16:45
-1

Please try below code. I have not tested but u can bulkify ur trigger by taking account ids in a set. Hope this will help u

trigger CalcuSummary on Account(after Update, after Insert) {
    integer won, lost, nodeal;
    Set < id > aid = new Set < Id > ();
    account a;
    for (opportunity o: Trigger.new) {
        aid.add(o.accountid);
    }

    won = [select count() from opportunity where accountid in : aid and final_Stage__c = 'Won'];
    lost = [select count() from opportunity where accountid in : aid and final_Stage__c = 'Lost'];
    nodeal = [select count() from opportunity where accountid = : aid and final_Stage__c = 'No Deal'];

    for (opportunity o: Trigger.new) {
        if (Trigger.newMap.get(o.Id).final_Stage__c != Trigger.oldMap.get(o.Id).final_Stage__c) {
            a = new account(id = aid, Number_Won_Opty__c = won, Number_Lost_Opty__c = lost, Number_No_Deal_Opty__c = nodeal);
        }
        update a;
    }
}
| improve this answer | |
  • Hi @Prateek, variable 'aid' will contain set of account ids and querying the opportunity table with 'aid' will result in bunch of opportunity records for different accounts. – Jarvis Dec 13 '14 at 8:24
  • a = new account(id = aid, Number_Won_Opty__c = won, Number_Lost_Opty__c = lost, Number_No_Deal_Opty__c = nodeal); is not a valid statement as aid is set of account ids. – Jarvis Dec 13 '14 at 8:31
  • Sorry my bad @Bennie ... "a = new account(id = o.accountid, Number_Won_Opty__c = won, Number_Lost_Opty__c = lost, Number_No_Deal_Opty__c = nodeal);" – tandonprateek Dec 13 '14 at 10:50
  • 3
    Please don't use this code. It has DML in a loop. Not best practice. – DavidSchach Dec 13 '14 at 15:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.