+ Start a Discussion
amruta_dhumalamruta_dhumal 

how to write code for SUM() of all opportunities in trigger

Hi

 

I want to write code for SUM() of all opportunity and SUM() of all WON opty.

 

here is my code but getting error:

System.DmlException: Update failed. First exception on row 0; first error: MISSING_ARGUMENT, Id not specified in an update call: []

 

trigger OptyTrigger on Opportunity(after delete, after insert, after undelete,after update) {
Opportunity[] cons;
if (Trigger.isDelete)
cons = Trigger.old;
else
cons = Trigger.new;

// get list of opty
Set<ID> acctIds = new Set<ID>();
for (Opportunity con : cons) {
acctIds.add(con.Framework_Agreement__c);
}

Map<ID, Opportunity> contactsForAccounts = new Map<ID, Opportunity>([select Id
,Framework_Agreement__c
from Opportunity
where Framework_Agreement__c in :acctIds]);

Map<ID, Opportunity> acctsToUpdate = new Map<ID, Opportunity>([select Id
,Number_of_Opportunities__c
from Opportunity
where Id in :acctIds]);

for (Opportunity acct : acctsToUpdate.values()) {
Set<ID> conIds = new Set<ID>();
for (Opportunity con : contactsForAccounts.values()) {
if (con.Framework_Agreement__c== acct.Id)
conIds.add(con.Id);
}
if (acct.Number_of_Opportunities__c != conIds.size())
acct.Number_of_Opportunities__c = conIds.size();
}

update acctsToUpdate.values();


LIST<AggregateResult> ChildCMRRList = [select Framework_Agreement__c fm,sum(Amount)childsum
from Opportunity where Framework_Agreement__c in :acctIds group by Framework_Agreement__c];
System.debug('ChildCMRR: ' + ChildCMRRList );

list<Opportunity> AccToUpdate = new list<Opportunity >();
for(AggregateResult ChildCMRR : ChildCMRRList){
ID Opportunity = (ID)ChildCMRR.get('fm');
Opportunity acc = new Opportunity (ID = Opportunity);

Decimal D = (Decimal)ChildCMRR.get('childsum');
acc.Total_Opportunity_Sum__c = D;

AccToUpdate.add(acc);
}
if(!AccToUpdate.isEmpty())
update AccToUpdate;

}

 

 

pls help how to resolve this.

 

Amruta

 

Best Answer chosen by Admin (Salesforce Developers) 
amruta_dhumalamruta_dhumal

No it's not working.....I changed my code once again,issue was I used '0'(zero)index variable ,i.e.groupedResults[0].

 

here is my updated code and it's working fine.....

 

trigger OptyTrigger on Opportunity(after delete, after insert,after update) {
   Opportunity[] cons;
    if (Trigger.isDelete) 
        cons = Trigger.old;
    else
        cons = Trigger.new;

    // get list of opty
    Set<ID> optyIds = new Set<ID>();
    for (Opportunity con : cons) {
            optyIds.add(con.Framework_Agreement__c);
    }
    
        Map<ID, Opportunity> childopty= new Map<ID, Opportunity>([select Id,Framework_Agreement__c from Opportunity where Framework_Agreement__c in :optyIds]);

        Map<ID, Opportunity> parentToUpdate= new Map<ID, Opportunity>([select Id,Number_of_Opportunities__c,Total_Sales__c,Total_Opportunity_Sum__c from Opportunity where Id in :optyIds]);

     //Use aggregate soql to get total sum of all opportunity
          AggregateResult[] groupedResults=[select Framework_Agreement__c ,sum(Amount)fm from Opportunity where Framework_Agreement__c in :optyIds group by Framework_Agreement__c];                                         

     //Use aggregate soql to get total sum of all Won opportunity

         AggregateResult[] groupedResults1=[select Framework_Agreement__c ,sum(Amount)ar from Opportunity where Framework_Agreement__c in :optyIds and StageName='Closed Won' group by Framework_Agreement__c];    
   
         for (Opportunity o: parentToUpdate.values()) {
             Set<ID> opId= new Set<ID>();
             for (Opportunity con : childopty.values()) {
               if (con.Framework_Agreement__c== o.Id)
                  opId.add(con.Id);
                }
  
            if (o.Number_of_Opportunities__c != opId.size())
                o.Number_of_Opportunities__c = opId.size();
        
         for(integer i=0;i<groupedResults.size();i++){   
             o.Total_Opportunity_Sum__c =(Decimal)groupedResults[i].get('fm');}
          
          for(integer j=0;j<groupedResults1.size();j++){
          o.Total_Sales__c = (Decimal)groupedResults1[j].get('ar');} 
      }
 
 //update parent opportunity 
 update parentToUpdate.values();

 Thanks for all your help and quick reply.......

 

Amruta

All Answers

thatheraherethatherahere

I think the possible cause of this exception is that in your code:


LIST<AggregateResult> ChildCMRRList = [select Framework_Agreement__c fm,sum(Amount)childsum
from Opportunity where Framework_Agreement__c in :acctIds group by Framework_Agreement__c];
System.debug('ChildCMRR: ' + ChildCMRRList );

list<Opportunity> AccToUpdate = new list<Opportunity >();
for(AggregateResult ChildCMRR : ChildCMRRList){
ID Opportunity = (ID)ChildCMRR.get('fm');
Opportunity acc = new Opportunity (ID = Opportunity);

Decimal D = (Decimal)ChildCMRR.get('childsum');
acc.Total_Opportunity_Sum__c = D;

AccToUpdate.add(acc);
}

 

here in the highlighted code, to your Opportunity you are assignning ID  = Opportunity where the Opportunity variable contains Framework_Agreement__c field's value in it. Now tell me Is the value of Framework_Agreement__c Is equal to your opportunity Ids(generated by system) or its something else?

amruta_dhumalamruta_dhumal

I changed my code logic now and able to find sum() of all child opties.

 

here is my code:

trigger OptyTrigger on Opportunity(after delete, after insert,after update) {
   Opportunity[] cons;
    if (Trigger.isDelete)
        cons = Trigger.old;
    else
        cons = Trigger.new;

    // get list of opty
    Set<ID> optyIds = new Set<ID>();
    for (Opportunity con : cons) {
            optyIds.add(con.Framework_Agreement__c);
    }
   
Map<ID, Opportunity> childopty= new Map<ID, Opportunity>([select Id,Framework_Agreement__c from Opportunity where Framework_Agreement__c in :optyIds]);

Map<ID, Opportunity> parentToUpdate= new Map<ID, Opportunity>([select Id,Number_of_Opportunities__c,Total_Sales__c,Total_Opportunity_Sum__c from Opportunity where Id in :optyIds]);
AggregateResult[] groupedResults=[select Framework_Agreement__c ,sum(Amount)fm from Opportunity where Framework_Agreement__c in :optyIds group by Framework_Agreement__c];                                        
AggregateResult[] groupedResults1=[select Framework_Agreement__c ,sum(Amount)ar from Opportunity where Framework_Agreement__c in :optyIds and StageName='Closed Won' group by Framework_Agreement__c];   
   for (Opportunity o: parentToUpdate.values()) {
     Set<ID> opId= new Set<ID>();
       for (Opportunity con : childopty.values()) {
         if (con.Framework_Agreement__c== o.Id)
             opId.add(con.Id);
        }
        if (o.Number_of_Opportunities__c != opId.size())
            o.Number_of_Opportunities__c = opId.size();
            o.Total_Opportunity_Sum__c =(Decimal)groupedResults[0].get('fm');
             o.Total_Sales__c = (Decimal)groupedResults[0].get('ar');
 
 update parentToUpdate.values();

 }
 }     

 

But now I am getting another error,when I try to delete any child opty ,exception is:

Validation Errors While Saving Record(s) There were custom validation error(s) encountered while saving the affected record(s). The first validation error encountered was "Apex trigger OptyTrigger caused an unexpected exception, contact your administrator: OptyTrigger: execution of AfterDelete caused by: System.SObjectException: Invalid field ar for AggregateResult: Trigger.OptyTrigger: line 28, column 1". 

 

line 28 :  o.Total_Sales__c = (Decimal)groupedResults[0].get('ar');

 

 

How to resolve this.

 

Amruta

Vinit_KumarVinit_Kumar

Change your code from

 

o.Total_Sales__c = (Decimal)groupedResults[0].get('ar');

 

to

 

o.Total_Sales__c = (Decimal)groupedResults1[0].get('ar');

 

 

As you have declared the other variable as groupedResults1.

amruta_dhumalamruta_dhumal

No it's not working.....I changed my code once again,issue was I used '0'(zero)index variable ,i.e.groupedResults[0].

 

here is my updated code and it's working fine.....

 

trigger OptyTrigger on Opportunity(after delete, after insert,after update) {
   Opportunity[] cons;
    if (Trigger.isDelete) 
        cons = Trigger.old;
    else
        cons = Trigger.new;

    // get list of opty
    Set<ID> optyIds = new Set<ID>();
    for (Opportunity con : cons) {
            optyIds.add(con.Framework_Agreement__c);
    }
    
        Map<ID, Opportunity> childopty= new Map<ID, Opportunity>([select Id,Framework_Agreement__c from Opportunity where Framework_Agreement__c in :optyIds]);

        Map<ID, Opportunity> parentToUpdate= new Map<ID, Opportunity>([select Id,Number_of_Opportunities__c,Total_Sales__c,Total_Opportunity_Sum__c from Opportunity where Id in :optyIds]);

     //Use aggregate soql to get total sum of all opportunity
          AggregateResult[] groupedResults=[select Framework_Agreement__c ,sum(Amount)fm from Opportunity where Framework_Agreement__c in :optyIds group by Framework_Agreement__c];                                         

     //Use aggregate soql to get total sum of all Won opportunity

         AggregateResult[] groupedResults1=[select Framework_Agreement__c ,sum(Amount)ar from Opportunity where Framework_Agreement__c in :optyIds and StageName='Closed Won' group by Framework_Agreement__c];    
   
         for (Opportunity o: parentToUpdate.values()) {
             Set<ID> opId= new Set<ID>();
             for (Opportunity con : childopty.values()) {
               if (con.Framework_Agreement__c== o.Id)
                  opId.add(con.Id);
                }
  
            if (o.Number_of_Opportunities__c != opId.size())
                o.Number_of_Opportunities__c = opId.size();
        
         for(integer i=0;i<groupedResults.size();i++){   
             o.Total_Opportunity_Sum__c =(Decimal)groupedResults[i].get('fm');}
          
          for(integer j=0;j<groupedResults1.size();j++){
          o.Total_Sales__c = (Decimal)groupedResults1[j].get('ar');} 
      }
 
 //update parent opportunity 
 update parentToUpdate.values();

 Thanks for all your help and quick reply.......

 

Amruta

This was selected as the best answer
Vinit_KumarVinit_Kumar

Is this code working or not working.Your statement is confusing :-

 

No it's not working.....I changed my code once again,issue was I used '0'(zero)index variable ,i.e.groupedResults[0].

 

here is my updated code and it's working fine.....

 

 

amruta_dhumalamruta_dhumal

Actually.....Previously I used below syntax in code:

 

 o.Total_Opportunity_Sum__c =(Decimal)groupedResults[0].get('fm');
 o.Total_Sales__c = (Decimal)groupedResults1[0].get('ar');

 

then I iterate it through for loop to avoid list index bound exception and it's working ...

 

 for(integer i=0;i<groupedResults.size();i++){   
             o.Total_Opportunity_Sum__c =(Decimal)groupedResults[i].get('fm');}
          
          for(integer j=0;j<groupedResults1.size();j++){
          o.Total_Sales__c = (Decimal)groupedResults1[j].get('ar');} 

 

 

Amruta

Vinit_KumarVinit_Kumar

Good to know that it is working :)