COUNT/ROLLUP No of Records with Multi Level Relatioinships using Trigger.

This is one of a very challenging requirment faced recently ,  I need to count the number of records and maintain the count in some other which has indirect relationship with this child object, the relationship hierarchy is just like the below one.

Example:

child  ----Lookup-----> Parent

Child Obj --> Parent Obj --> GrandParent Obj <-- Object X

• child - online Application
• Parent - Account
• GrandParent - Parent Account
• Object X - UR Account Goal
• Field  in Online Application ---> Institution Account(Parent Account Id)(Formula field),it just populates the Parent id (Grandparent) in Online Application.

There are two types of child objects records, say one type is custom and another is standard, its just varied by using a picklist in the child object, each child object having only one parent and that parent having only one grandparent, and that grandparent is having only one ObjectX.

The twist here is, from child to grandparent, the relationship is just straight and inner level, but the destinatin object ,which needs to maintain the count of the child is related to the grandparent invertedly, as you can see above ,

What i needs to do is whenever a new child record introduces, i needs to increase the count in the Object X related to the childs-grandparent,In the Object X there is two fields named as standard count and custom count, which seperately maintains the count of  two different types of applications.

Another interest twist is i needs to write a trigger which also supports bulk/batch inserts/updates, so the challenge is if a batch inserts 200 records means, there may be a possibility of having multiple records for that child object, so the child record count is first needs to be done within that 200 records and after that i needs to append the count level in Object X.

Below is just my sample code to achieve this functionality,this can be achieved using the map concepts , I have tested the below code, it works perfectly as per the need and also supports bulk/batch inserts.

I have to tell this is not the best code, but it do the work,still trying to make this a best one and soon i will update the best ,suggestions are welcome.

Hope you understand the above requirement......


Trigger Code:

1. Trigger CountTrigger on Online_Application__c (after insert, after update) {
3. Set<String> InstAccSet = new Set<String>();
4. Online_Application__c oldapp,newapp;
5. Map<Id,UniversityRelationsAccountGoals__c> URGoalMap = new Map<Id,UniversityRelationsAccountGoals__c>();
8. For(Online_Application__c oapp : Trigger.New){
9. if(( Trigger.IsInsert && oapp.Offering__c!=null) || (Trigger.IsUpdate &&  oapp.Offering__c!=null &&Trigger.oldMap.get(oapp.Id).Type__c!=Trigger.newMap.get(oapp.Id).Type__c) ){
10. InstAccSet.add(oapp.Institution_Account__c);
11. }
12. }
14. If(InstAccSet.size()>0){
15. List<UniversityRelationsAccountGoals__c> URGoalList = [Select Id,Account__c,AcademicTerm__c,Standard_Program_Count_Source__c,Custom_Program_Count_Source__c from UniversityRelationsAccountGoals__c where Account__c  IN: InstAccSet];
18. For(Online_Application__c OnlineApp : Trigger.New){
19. For(UniversityRelationsAccountGoals__c URGoalMapLoop: URGoalList){
21.  if(OnlineApp.Institution_Account__c == URGoalMapLoop.Account__c ){
22.          if(OnlineApp.Academic_Term__c == URGoalMapLoop.AcademicTerm__c ){
23.          
24.          if(URGoalMap.containsKey(URGoalMapLoop.Id)){
25.          
26.           if(OnlineApp.Type__c=='Custom'){  
27.                         URGoalMapLoop.Custom_Program_Count_Source__c=URGoalMap.get(URGoalMapLoop.Id).Custom_Program_Count_Source__c+1;}
28.                if(OnlineApp.Type__c=='Standard'){
29.                         URGoalMapLoop.Standard_Program_Count_Source__c=URGoalMap.get(URGoalMapLoop.Id).Standard_Program_Count_Source__c+1;}
30.                             URGoalMap.put(URGoalMapLoop.Id,URGoalMapLoop);
31.          }
32.          
33.          else {
34.                    if(OnlineApp.Type__c=='Custom'){  
35.                         URGoalMapLoop.Custom_Program_Count_Source__c=URGoalMapLoop.Custom_Program_Count_Source__c+1;}
36.                if(OnlineApp.Type__c=='Standard'){
37.                         URGoalMapLoop.Standard_Program_Count_Source__c=URGoalMapLoop.Standard_Program_Count_Source__c+1;}
38.          URGoalMap.put(URGoalMapLoop.Id,URGoalMapLoop);
39.               }
40. }
41. }
42. }
43. }}
44. update URGoalMap.values();

...