function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Sourav PSourav P 

Autopopulate summation of fields from a lookup object on a Main object field

Hi All, any one plz help me out on this,

I have a custom object called " Quotation", with a field called " Total Premium". The object has a look up relationship with another custom object " Cover". In the cover object i have field called " Gross Premium". I need that all the Cover object records addded to the Quotation object, The gross premium from those will sum up and autopopulate in teh Total premium field. Deleting or adding the records, should affect the Total premium field value.

User-added image

I have written the below trigger, but its showing the error as,
Error: Compile Error: unexpected token: trigger trgSummarizeCover at line 1 column 0
Can anyone plz figure it out whats wrong in my code and rectify it. Thanks
  
trigger trgSummarizeCover on Cover__c (after delete, after insert, after update) {

  //Limit the size of list by using Sets which do not contain duplicate elements
  set<Id> QuotationIds = new set<Id>();

  //When adding new CustomObject or updating existing CustomObject
  if(trigger.isInsert || trigger.isUpdate){
    for(Cover__c p : trigger.new){
      QuotationIds.add(p.Quotation__c);
    }
  }

  //When deleting CustomObject
  if(trigger.isDelete){
    for(Cover__c p : trigger.old){
      QuotationsIds.add(p.Quotation__c);
    }
  }

  //Map will contain one Quotation Id to one sum value
  map<Id,Double> QuotationMap = new map <Id,Double>();

  //Produce a sum of Cover__c and add them to the map
  //use group by to have a single Quotation Id with a single sum value
  for(AggregateResult q : [select Id,sum(Gross_premium_policy__c)
    from Cover__c where Quotation__c IN :QuotationIds group by Quotation__c]){
      QuotationMap.put((Id) q.get('Quotation__c'),(Double)q.get('expr0'));
  }

  List<Quotation__c> QuotationsToUpdate = new List<Quotation__c>();

  //Run the for loop on Quotation using the non-duplicate set of Quotation Ids
  //Get the sum value from the map and create a list of Quotations to update
  for(Quotation__c o : [Select Id, Total_Premium__c from Quotation__c where Id IN :QuotationIds]){
    Double Cover__c sum = QuotationMap.get(o.Id);
    o.Total_Premium__c = Cover__c sum;
    QuotationsToUpdate.add(o);
  }

  update QuotationsToUpdate;
}


 
Best Answer chosen by Sourav P
EldonEldon
You have to give the lookup field name there as we are taking the parent ids in that list.Also why it is not working is because you didnt give update option in the isInsert portion. Replace with the below code

  
trigger trgUpdateCover on Cover__c (after insert,after update, after delete,after undelete) {
    List<id> QuotationsIds = new List<id>();
    if(Trigger.isInsert || Trigger.isUndelete || Trigger.isupdate){
        For(Cover__c con1 : Trigger.new){
            QuotationsIds.add(con1.Quotation_Number__c);
        }
    }
    if(Trigger.isDelete){
        For(Cover__c con1 : Trigger.old){
            QuotationsIds.add(con1.Quotation_Number__c);
        }
    }
    List<Quotation__c> QuotationsToUpdate = new List<Quotation__c>();
    decimal sum = 0;
    if(Trigger.isInsert || Trigger.isUndelete || Trigger.isupdate){
        For (Quotation__c q: [SELECT id,Total_Premium__c, (SELECT id, Gross_premium_policy__c FROM Cover__r) FROM Quotation__c WHERE id =:QuotationsIds]){
            sum = 0;
            for(Cover__c  p : q.Cover__r)
                sum = sum + p.Cover__c ;
            q.Total_Premium__c  = sum;
            QuotationsToUpdate .add(q);
        }
        try{
            update QuotationsToUpdate ;
        }Catch(Exception e){
            System.debug('Exception :'+e.getMessage());
        }
    }
    if(Trigger.isDelete){
        For(Quotation__c q : [SELECT Total_Premium__c ,(SELECT id,Gross_premium_policy__c FROM Cover__r) FROM Quotation__c WHERE id =: QuotationsIds]){
            sum = 0;
            for(Cover__c  p : q.Cover__r)
                sum = sum + p.Cover__c ;
            q.Total_Premium__c  = sum;
            QuotationsToUpdate .add(q);
        }
        
        
        try{
            update QuotationsToUpdate ;
        }Catch(Exception e){
            System.debug('Exception :'+e.getMessage());
        }
    }
}

 

All Answers

bob_buzzardbob_buzzard
Rather than write a solution from scratch, I'd be inclined to use one of the declarative roll up summary soltutions that already exist.  For an example, check out this blog post from my fellow MVP Andrew Fawcett:

https://developer.salesforce.com/page/Declarative_Rollup_Summary_Tool_for_Force.com_Lookup_Relationships
EldonEldon
Hi Sourav,

I have done the insert and update portion try it and let me know if you can complete the delete portion also,(please note that i have used the child relationship name as 'pobs__r' (line 17 and 19)change it to your name Also i assume your lookup field name is 'Quotation__c')
  
trigger DevComPob3 on Cover__c  (after insert,after update, after delete,after undelete) {
    
    List<id> QuotationsIds = new List<id>();
    if(Trigger.isInsert || Trigger.isUndelete || Trigger.isupdate){
        For(Cover__c  con1 : Trigger.new){
            QuotationsIds.add(con1.Quotation__c);
        }
    }
    if(Trigger.isDelete){
        For(Cover__c  con1 : Trigger.old){
            QuotationsIds.add(con1.Quotation__c);
        }
    }
    List<Quotation__c> QuotationsToUpdate  = new List<Quotation__c>();
    decimal sum = 0;
    if(Trigger.isInsert || Trigger.isUndelete || Trigger.isupdate){
        For(Quotation__c q : [SELECT Total_Premium__c ,(SELECT id,Cover__c  FROM pobs__r) FROM Quotation__c WHERE id =: QuotationsIds]){
            sum = 0;
            for(Cover__c  p : q.pobs__r)
                sum = sum + p.Cover__c ;
            q.Total_Premium__c  = sum;
            QuotationsToUpdate .add(q);
        }
        try{
            update QuotationsToUpdate ;
        }Catch(Exception e){
            System.debug('Exception :'+e.getMessage());
        }
    }
}


Please close the thread as solved if it helped you.

Regards


 
Sourav PSourav P
Hi Elden , thanks. I tried to write the code but its showing below errors, Can you plz check that. I tried various ways but keep on showing same error. The Isdelete, would be teh same i think as written above, so i put the same.
  
trigger trgUpdateCover on Cover__c (after insert,after update, after delete,after undelete) {
List<id> QuotationsIds = new List<id>();
if(Trigger.isInsert || Trigger.isUndelete || Trigger.isupdate){
For(Cover__c con1 : Trigger.new){
QuotationsIds.add(con1.Quotation__c);
}
}
if(Trigger.isDelete){
For(Cover__c con1 : Trigger.old){
QuotationsIds.add(con1.Quotation__c);
}
}
List<Quotation__c> QuotationsToUpdate = new List<Quotation__c>();
decimal sum = 0;
if(Trigger.isInsert || Trigger.isUndelete || Trigger.isupdate){
For (Quotation__c q: [SELECT id,Total_Premium__c (SELECT id, Gross_premium_policy__c FROM Cover__r) FROM Quotation__c WHERE id =:QuotationsIds]){
sum = 0;
for(Cover__c  p : q.Cover__r)
sum = sum + p.Cover__c ;
q.Total_Premium__c  = sum;
QuotationsToUpdate .add(q);
}
if(Trigger.isDelete){
For(Quotation__c q : [SELECT Total_Premium__c ,(SELECT id,Gross_premium_policy__c FROM Cover__r) FROM Quotation__c WHERE id =: QuotationsIds]){
sum = 0;
for(Cover__c  p : q.Cover__r)
sum = sum + p.Cover__c ;
q.Total_Premium__c  = sum;
QuotationsToUpdate .add(q);
}


      try{
            update QuotationsToUpdate ;
        }Catch(Exception e){
            System.debug('Exception :'+e.getMessage());
        }
    }
}

 
Sourav PSourav P
The error showing is " Error: Compile Error: unexpected token: 'SELECT' at line 16 column 50"
EldonEldon
Which one is parent and which one is child? i assumed quotation__c as parent.
Also change the Cover__c in line 19 and  27 to Gross_premium_policy__c 
Sourav PSourav P
Hi Eldon, Yes.
Parent is : Quotation__c ( field here where sum should reflect is " Total premium"
Child is : Cover__c ( field to be summed up " Policy Gross premium"

Thanks, Let me try your suggestion.
Sourav PSourav P
I did the same, but the same error "
Error: Compile Error: unexpected token: 'SELECT' at line 16 column 50" is till keep on showing.
EldonEldon
Check if your child relationship name Cover__r  is correct or not. Also one braces is missing at last i think
EldonEldon
Hey you are missing a comma after Total_Premium__c in the query
replace line 16 with this
  
For (Quotation__c q: [SELECT id,Total_Premium__c, (SELECT id, Gross_premium_policy__c FROM Cover__r) FROM Quotation__c WHERE id =:QuotationsIds]){

Regards
Sourav PSourav P
Hi Elden, I did the same 
For (Quotation__c q: [SELECT id,Total_Premium__c,(SELECT id, Gross_premium_policy__c FROM Cover__r) FROM Quotation__c WHERE id =:QuotationsIds]){
and now its showing me error as, seems somwhere one parenthesis issue
Error: Compile Error: unexpected token: ) at line 16 column 98

Sorry, i purposefully removed that comma, to compensate an error.
Ya, Childe relationship name is ok,

User-added image
 
EldonEldon
In the code you gave one curly brace is missing. Try below code
  
trigger trgUpdateCover on Cover__c (after insert,after update, after delete,after undelete) {
    List<id> QuotationsIds = new List<id>();
    if(Trigger.isInsert || Trigger.isUndelete || Trigger.isupdate){
        For(Cover__c con1 : Trigger.new){
            QuotationsIds.add(con1.Quotation__c);
        }
    }
    if(Trigger.isDelete){
        For(Cover__c con1 : Trigger.old){
            QuotationsIds.add(con1.Quotation__c);
        }
    }
    List<Quotation__c> QuotationsToUpdate = new List<Quotation__c>();
    decimal sum = 0;
    if(Trigger.isInsert || Trigger.isUndelete || Trigger.isupdate){
        For (Quotation__c q: [SELECT id,Total_Premium__c, (SELECT id, Gross_premium_policy__c FROM Cover__r) FROM Quotation__c WHERE id =:QuotationsIds]){
            sum = 0;
            for(Cover__c  p : q.Cover__r)
                sum = sum + p.Cover__c ;
            q.Total_Premium__c  = sum;
            QuotationsToUpdate .add(q);
        }
    }
        if(Trigger.isDelete){
            For(Quotation__c q : [SELECT Total_Premium__c ,(SELECT id,Gross_premium_policy__c FROM Cover__r) FROM Quotation__c WHERE id =: QuotationsIds]){
                sum = 0;
                for(Cover__c  p : q.Cover__r)
                    sum = sum + p.Cover__c ;
                q.Total_Premium__c  = sum;
                QuotationsToUpdate .add(q);
            }
            
            
            try{
                update QuotationsToUpdate ;
            }Catch(Exception e){
                System.debug('Exception :'+e.getMessage());
            }
        }
    }

 
Sourav PSourav P
Hi Elden, I taken the above code, on line 19 & 28 i changed it to " Gross_premium_policy__c" ( is that ok ? ), But now its giving me below error,

Error: Compile Error: Invalid field Quotation__c for SObject Cover__c at line 5 column 27

Quotation__c is the object, it should come here ?
  
trigger trgUpdateCover on Cover__c (after insert,after update, after delete,after undelete) {
    List<id> QuotationsIds = new List<id>();
    if(Trigger.isInsert || Trigger.isUndelete || Trigger.isupdate){
        For(Cover__c con1 : Trigger.new){
        QuotationsIds.add(con1.Quotation__c);
        }
    }
    if(Trigger.isDelete){
        For(Cover__c con1 : Trigger.old){
            QuotationsIds.add(con1.Quotation__c);
        }
    }
    List<Quotation__c> QuotationsToUpdate = new List<Quotation__c>();
    decimal sum = 0;
    if(Trigger.isInsert || Trigger.isUndelete || Trigger.isupdate){
        For (Quotation__c q: [SELECT id,Total_Premium__c, (SELECT id, Gross_premium_policy__c FROM Cover__r) FROM Quotation__c WHERE id =:QuotationsIds]){
            sum = 0;
            for(Cover__c  p : q.Cover__r)
                sum = sum + p.Gross_premium_policy__c ;
            q.Total_Premium__c  = sum;
            QuotationsToUpdate .add(q);
        }
    }
        if(Trigger.isDelete){
            For(Quotation__c q : [SELECT Total_Premium__c ,(SELECT id,Gross_premium_policy__c FROM Cover__r) FROM Quotation__c WHERE id =: QuotationsIds]){
                sum = 0;
                for(Cover__c  p : q.Cover__r)
                    sum = sum + p.Gross_premium_policy__c ;
                q.Total_Premium__c  = sum;
                QuotationsToUpdate .add(q);
            }
            
            
            try{
                update QuotationsToUpdate ;
            }Catch(Exception e){
                System.debug('Exception :'+e.getMessage());
            }
        }
    }

 
EldonEldon
In line 5 and line 10 Instead of quotation__c give the name of lookup field to quotation__c object from cover__c
Sourav PSourav P
Hi Elden,
Do you mean ,
"Gross_premium_policy__c", this fields are going to sum up and fill the " Total premium" in Quotation. But if i am giving this field , its showing error as " Error: Compile Error: Incompatible element type Decimal for collection of Id at line 5 column 9"

Or, Do you mean,  Quotation Number     Quotation_Number__c         Lookup(Quotation)
which is a look up field in the Cover object. If i put this one, then its showing me no more errors, But the trigger is not working. Its not giving a cover summations in the " Total premium" field.

User-added image
EldonEldon
You have to give the lookup field name there as we are taking the parent ids in that list.Also why it is not working is because you didnt give update option in the isInsert portion. Replace with the below code

  
trigger trgUpdateCover on Cover__c (after insert,after update, after delete,after undelete) {
    List<id> QuotationsIds = new List<id>();
    if(Trigger.isInsert || Trigger.isUndelete || Trigger.isupdate){
        For(Cover__c con1 : Trigger.new){
            QuotationsIds.add(con1.Quotation_Number__c);
        }
    }
    if(Trigger.isDelete){
        For(Cover__c con1 : Trigger.old){
            QuotationsIds.add(con1.Quotation_Number__c);
        }
    }
    List<Quotation__c> QuotationsToUpdate = new List<Quotation__c>();
    decimal sum = 0;
    if(Trigger.isInsert || Trigger.isUndelete || Trigger.isupdate){
        For (Quotation__c q: [SELECT id,Total_Premium__c, (SELECT id, Gross_premium_policy__c FROM Cover__r) FROM Quotation__c WHERE id =:QuotationsIds]){
            sum = 0;
            for(Cover__c  p : q.Cover__r)
                sum = sum + p.Cover__c ;
            q.Total_Premium__c  = sum;
            QuotationsToUpdate .add(q);
        }
        try{
            update QuotationsToUpdate ;
        }Catch(Exception e){
            System.debug('Exception :'+e.getMessage());
        }
    }
    if(Trigger.isDelete){
        For(Quotation__c q : [SELECT Total_Premium__c ,(SELECT id,Gross_premium_policy__c FROM Cover__r) FROM Quotation__c WHERE id =: QuotationsIds]){
            sum = 0;
            for(Cover__c  p : q.Cover__r)
                sum = sum + p.Cover__c ;
            q.Total_Premium__c  = sum;
            QuotationsToUpdate .add(q);
        }
        
        
        try{
            update QuotationsToUpdate ;
        }Catch(Exception e){
            System.debug('Exception :'+e.getMessage());
        }
    }
}

 
This was selected as the best answer
Sourav PSourav P
Hi Elden,
Sorry, but where i was not giving the update opton in IsInsert part ? , I can see that the above code is same to my previous one. May i know plz where you changed the update option ?. But i tried the above code, No error but till its not giving the summation. I replace the above 19 & 33 with 
sum = sum + p.Gross_premium_policy__c ;

as per your earlier suggestion, So my final code is as per below now,
  
trigger trgUpdateCover on Cover__c (after insert,after update, after delete,after undelete) {
    List<id> QuotationsIds = new List<id>();
    if(Trigger.isInsert || Trigger.isUndelete || Trigger.isupdate){
        For(Cover__c con1 : Trigger.new){
            QuotationsIds.add(con1.Quotation_Number__c);
        }
    }
    if(Trigger.isDelete){
        For(Cover__c con1 : Trigger.old){
            QuotationsIds.add(con1.Quotation_Number__c);
        }
    }
    List<Quotation__c> QuotationsToUpdate = new List<Quotation__c>();
    decimal sum = 0;
    if(Trigger.isInsert || Trigger.isUndelete || Trigger.isupdate){
        For (Quotation__c q: [SELECT id,Total_Premium__c, (SELECT id, Gross_premium_policy__c FROM Cover__r) FROM Quotation__c WHERE id =:QuotationsIds]){
            sum = 0;
            for(Cover__c  p : q.Cover__r)
                sum = sum + p.Gross_premium_policy__c;
            q.Total_Premium__c  = sum;
            QuotationsToUpdate .add(q);
        }
        try{
            update QuotationsToUpdate ;
        }Catch(Exception e){
            System.debug('Exception :'+e.getMessage());
        }
    }
    if(Trigger.isDelete){
        For(Quotation__c q : [SELECT Total_Premium__c ,(SELECT id,Gross_premium_policy__c FROM Cover__r) FROM Quotation__c WHERE id =: QuotationsIds]){
            sum = 0;
            for(Cover__c  p : q.Cover__r)
                sum = sum + p.Gross_premium_policy__c ;
            q.Total_Premium__c  = sum;
            QuotationsToUpdate .add(q);
        }
        
        
        try{
            update QuotationsToUpdate ;
        }Catch(Exception e){
            System.debug('Exception :'+e.getMessage());
        }
    }
}

 
Sourav PSourav P
Ohh , seems its worked :)
Sourav PSourav P
Hi Eldon, But somtimes working, and somtimes not. Let me check a bit throughly whats the issue is and let you know . Seems we are very close. thanks a lot for your continuing support.
EldonEldon
When is it not working? Updating,inserting or deleting?
Sourav PSourav P
Hi Eldon,
Excellent, It starts working fine now, thank you so much for your support.
Sourav PSourav P
Hi @bob_buzzard, Excellent article. But i tried the one. and it doesnt worked for me. If its working in practical, wold have been a great help. I also went to the apps created by Andrew, tried to install the app, on Declarative look up roll up, but it told version has expired , couldnot download.If i would download that or if Andrew would update that App, It would have been great.
EldonEldon
Also go through this app exchange app called 'roll up helper' 
https://appexchange.salesforce.com/listingDetail?listingId=a0N30000009i3UpEAI