You need to sign in to do that
Don't have an account?
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
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
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?
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
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.
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
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.....
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
Good to know that it is working :)