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
DeptonDepton 

trigger to get the sum of a child (look up) object field

Hi,


I have 2 objects:

 

Milestones, and Actions, Actions is the child object (look up relationship)

 

In the Actions object I have a field called "total hours"

 

I would like to get the sum of the "total hours" in a Milestone object field called “Sum of Hours”

 

As there is no master detail relationship so I cannot create a roll up summary field.

 

I guess it can be done with a trigger. Does anyone have a sample trigger for this?

 

Thank you!

Best Answer chosen by Admin (Salesforce Developers) 
DeptonDepton

Solved it:

 

trigger OnMilestonesTrigger On Milestone1_Milestone__c(after insert, after update){

    Set<id> setProjectids = new Set<id>();
    List<Decimal> setMileStone_values=new List<Decimal>();
    List<Milestone1_Milestone__c> mmc=new List<Milestone1_Milestone__c>();
    
    List<Milestone1_Project__c> mpc=new List<Milestone1_Project__c>();

    for(Milestone1_Milestone__c mst: Trigger.new){
    
        if(mst.Project__c!=null && mst.Total_Actual_Hours__c!=null){
            setProjectids.add(mst.Project__c);
            setMileStone_values.add(mst.Total_Actual_Hours__c);
           // mmc.add(setProjectids,setMileStone_values); 
    
        }                    
    }

    integer i=0;
    for(Milestone1_Project__c pro:[select name, Sum_of_Actual_Hours__c  from Milestone1_Project__c where id In : setProjectids]){
        
        pro.Sum_of_Actual_Hours__c = setMileStone_values[i]; 
        i++;
        mpc.add(pro);
    
    }
    
    update mpc;

 :))

 

Thank you guys for your great help!!!

All Answers

kritinkritin

You should to crate Trigger on Child Object

 

Below code definately help , just check API fild name and Object Name & Data Type.....

Please mark this post as solution once it is accepted..

 

trigger OnActionsTrigger On Actions__c(after insert, after update){
set<Id> setMilestonesid=new set<Id>();
Map<string,Decimal> MilStoneWithTotalActionHours=new Map<string,Decimal>();
 for(Actions__c acs:Trigger.New){
  if(acs.MileStones_c!=null && acs.Hours!=null){
   setMilestonesid.add(setMilestonesid);
  }
 }
 
 if(setMilestonesid.size()>0){
  List<Milestones__c> ListMileStones=[Select Id, TotalHours__c from Milestones where MileStones__c =:setMilestonesid];
  List<Actions__c> ListMileStoneActions=[Select Id, Hours__c from Actions__c where MileStones_c =:setMilestonesid where Hours__c!=null];
  for(Id MileId:setMilestonesid){
   Decimal totHours=0;
   for(Actions__c acs: ListMileStoneActions){
    if(MileId=acs.MileStones_c){
     totHours=totHours + acs.Hours__c ;
    }
   }
   MilStoneWithTotalActionHours.put(MileId,totHours);
   
  }
 }
 
 List<Milestones__c> UpdateMilestones=new List<Milestones__c>();
 
 for(Milestones__c MC:ListMileStones){
  MC.TotalHours__c=MilStoneWithTotalActionHours.get(MC.id);
  UpdateMilestones.add(MC);
 }
 
 if(UpdateMilestones.size()>0){
  update UpdateMilestones;
 }
 
}
bob_buzzardbob_buzzard

You'll also need a trigger to handle when records are deleted (so that you can reduce the sum) and when they are undeleted (which should apply its value once again to the sum).  Kritin's example should be straightforward to adapt.
DeptonDepton
trigger OnMilestonesTrigger On Milestone1_Milestone__c(after insert, after update){
set<Id> setProjectid=new set<Id>();
Map<string,Decimal> ProjectWithTotalMilestoneHours=new Map<string,Decimal>();
 for(Milestone1_Milestone__c mst:Trigger.New){
  if(mst.Milestone1_Project__c!=null && mst.Hours!=null){
   setProjectid.add(setProjectid);
  }
 }
 
 if(setProjectid.size()>0){
  List<Milestone1_Project__c> ListProjects=[Select Id, Sum_of_Actual_Hours__c from Milestone1_Project__c where Milestone1_Project__c=:setProjectid];
  List<Milestone1_Milestone__c> ListMilestones=[Select Id, Total_Actual_Hours__c from Milestone1_Milestone__c where Milestone1_Project__c=:setProjectid where Total_Actual_Hours__c!=null];
  for(Id MileId:setProjectid){
   Decimal totHours=0;
   for(Milestone1_Milestone__c mst: ListMilestones){
    if(MileId=mst.Milestone1_Project__c){
     totHours=totHours + mst.Total_Actual_Hours__c;
    }
   }
   ProjectWithTotalMilestoneHours.put(MileId,totHours);
   
  }
 }
 
 List<Milestone1_Project__c> UpdateProject=new List<Milestone1_Project__c>();
 
 for(Milestone1_Project__cMC:ListProjects){
  MC.Sum_of_Actual_Hours__c=ProjectWithTotalMilestoneHours.get(MC.id);
  UpdateProject.add(MC);
 }
 
 if(UpdateProject.size()>0){
  update UpdateMilestones;
 }
 
}

 

 

This is how it looks now with the API names:

 

 

Milestone1_Project__c: is the Project object

Milestone1_Milestone__c is : is the milestones object (child)

 

Sum_of_Actual_Hours__c is the custom field in Projects

Total_Actual_Hours__c  is the custom field in Milestones

 

I got this error:


Error: Compile Error: unexpected token: 'where' at line 12 column 152

 

 

In this line:


  List<Milestone1_Milestone__c> ListMilestones=[Select Id, Total_Actual_Hours__c from Milestone1_Milestone__c where Milestone1_Project__c=:setProjectid where Total_Actual_Hours__c!=null];

 

 

???

 

Thank you!!!
bob_buzzardbob_buzzard

I'd imagine the second 'where' should be replaced with 'and'
DeptonDepton

Thank you Bob,

 

Now I got this error:

 

Error: Compile Error: Variable does not exist: ListProjects at line 27 column 31

 

It is because is inside the loop?

 

Thank you guys!!
bob_buzzardbob_buzzard

Its because ListProjects is declared inside an if statement and used outside.

 

You should probably initialise this outside the loop to an empty list:

 

List<Milestone1_Project__c> ListProjects=new List<Milestone1_Project__c>();
if(setProjectid.size()>0){
  ListProjects=[Select Id, Sum_of_Actual_Hours__c from Milestone1_Project__c where Milestone1_Project__c=:setProjectid];

 
DeptonDepton

Thanks Bob!! (once again) not only for the help but your clarifications

 

I  am Still getting one more error: (I tried to look at the boards help, web, and Apex developer guide without success...:(

 

Error: Compile Error: Incompatible element type SET<Id> for collection of Id at line 6 column 4

 

   setProjectid.add(setProjectid);

 

 

Thank you!!

 
bob_buzzardbob_buzzard

That's a strange line - its adding itself to the set of ids, but as its a set that doesn't work.  Looks like it should be the following to me:

 

setProjectId.add(mst.Milestone1_Project__c);

 as this is managing the project ids that will be retrieved.
DeptonDepton
trigger OnMilestonesTrigger On Milestone1_Milestone__c(after insert, after update){
 set<Id> setProjectid=new set<Id>();
 Map<string,Decimal> ProjectWithTotalMilestoneHours=new Map<string,Decimal>();
 for(Milestone1_Milestone__c mst:Trigger.New){
  if(mst.Milestone1_Project__c!=null && mst.Hours!=null){
   setProjectId.add(mst.Milestone1_Project__c);
  }
 }
 
 List<Milestone1_Project__c> ListProjects=new List<Milestone1_Project__c>();
 if(setProjectid.size()>0){
  ListProjects=[Select Id, Sum_of_Actual_Hours__c from Milestone1_Project__c where Milestone1_Project__c=:setProjectid];
  List<Milestone1_Milestone__c> ListMilestones=[Select Id, Total_Actual_Hours__c from Milestone1_Milestone__c where Milestone1_Project__c=:setProjectid and Total_Actual_Hours__c!=null];
  for(Id MileId:setProjectid){
   Decimal totHours=0;
   for(Milestone1_Milestone__c mst: ListMilestones){
    if(MileId=mst.Milestone1_Project__c){
     totHours=totHours + mst.Total_Actual_Hours__c;
    }
   }
   ProjectWithTotalMilestoneHours.put(MileId,totHours);
   
  }
 }
 
 List<Milestone1_Project__c> UpdateProject=new List<Milestone1_Project__c>();
 
 for(Milestone1_Project__c MC:ListProjects){
  MC.Sum_of_Actual_Hours__c=ProjectWithTotalMilestoneHours.get(MC.id);
  UpdateProject.add(MC);
 }
 
 if(UpdateProject.size()>0){
  update UpdateProject;
 }
 
}

 

this is what i got and getting this:



Error: Compile Error: Invalid field Milestone1_Project__c for SObject Milestone1_Milestone__c at line 6 column 21

 

bit confuse with :

 

 for(Milestone1_Milestone__c mst:Trigger.New){

  if(mst.Milestone1_Project__c!=null && mst.Hours!=null){   

setProjectId.add(mst.Milestone1_Project__c); 

} 

}

 

:)
bob_buzzardbob_buzzard

Milestone1_Project__c is used all over the place - this is the project relationship on your milestone.  Simply replace that with the real field I'd guess.
DeptonDepton

Solved it:

 

trigger OnMilestonesTrigger On Milestone1_Milestone__c(after insert, after update){

    Set<id> setProjectids = new Set<id>();
    List<Decimal> setMileStone_values=new List<Decimal>();
    List<Milestone1_Milestone__c> mmc=new List<Milestone1_Milestone__c>();
    
    List<Milestone1_Project__c> mpc=new List<Milestone1_Project__c>();

    for(Milestone1_Milestone__c mst: Trigger.new){
    
        if(mst.Project__c!=null && mst.Total_Actual_Hours__c!=null){
            setProjectids.add(mst.Project__c);
            setMileStone_values.add(mst.Total_Actual_Hours__c);
           // mmc.add(setProjectids,setMileStone_values); 
    
        }                    
    }

    integer i=0;
    for(Milestone1_Project__c pro:[select name, Sum_of_Actual_Hours__c  from Milestone1_Project__c where id In : setProjectids]){
        
        pro.Sum_of_Actual_Hours__c = setMileStone_values[i]; 
        i++;
        mpc.add(pro);
    
    }
    
    update mpc;

 :))

 

Thank you guys for your great help!!!

This was selected as the best answer