+ Start a Discussion
John L.ax1429John L.ax1429 

Pull Total Support Hours to the account recort.

I need help with trying to sum two fields that are on the case record that are called support hours and customer hours to be visiable on the account record?  I do not know how to develope the code required to do this and I am asking if someone can help me or develope this code.  Please let me know if anyone can help me.  Thank you for your time in this matter.

 

Regards,

 

John

Best Answer chosen by Admin (Salesforce Developers) 
Saikishore Reddy AengareddySaikishore Reddy Aengareddy

Try this code...

 

trigger totalSupportHours on Case (after insert, after update) {  

       set<Id> AccIds = new set<Id>();   

       List<Account> accs = new List<Account>();      

 

      for(Case c : Trigger.new){      

            if(c.AccountId<>null)            

                AccIds.add(c.AccountId);   

       }        

 

       List <AggregateResult> aggrs =[SELECT Account.Id aId, sum

(Time_With_Support__c) sh,sum(Time_With_Customer__c) ch from Case  

                                  WHERE createddate=this_month AND AccountId IN:AccIds   

                                 GROUP BY Account.Id];

 

    for (AggregateResult ja : aggrs)    

    {        

          System.debug('Acc Id:'+ja.get('aId') + ' Support Hrs: ' + ja.get('sh')+ ' Customer Hrs: ' + ja.get('ch'));

 

          Account acc = new Account(Id=(Id)ja.get('aId'),Total_Hours__c=Integer.valueOf(ja.get('sh'))+Integer.valueOf(ja.get('ch')), Total_Support_Hours__c=Integer.valueOf(ja.get('sh')),Total_Customer_Hours__c=Integer.valueOf(ja.get('ch')));

 

          accs.add(acc);

    }

    if(accs.size()>0)

        update accs;

}

All Answers

Saikishore Reddy AengareddySaikishore Reddy Aengareddy

John... Try using this code... I've tested this and it is working as expected...

Assuming you have two Number Type fields on Case called Customer_Hours__C and Support_Hours__C 

On Account a Number Type field called Total_Hours__C.

 

Eg: If a Account has 5 different casses then this trigger will sum up all the "customer hours" and "support hours"... and finally updates these to "Total Hours" field on Account.

 

trigger totalSupportHours on Case (after insert, after update) {
    
    set<Id> AccIds = new set<Id>();
    List<Account> accs = new List<Account>();
      
    for(Case c : Trigger.new){
        if(c.AccountId<>null)
            AccIds.add(c.AccountId);
    }
    
    List <AggregateResult> aggrs =[SELECT Account.Id aId, sum(Support_Hours__C) sh,sum(Customer_Hours__C) ch from Case
                                   WHERE AccountId IN:AccIds
                                   GROUP BY Account.Id];

    for (AggregateResult ja : aggrs)
    {
        System.debug('Acc Id:'+ja.get('aId') + ' Support Hrs: ' + ja.get('sh')+ ' Customer Hrs: ' + ja.get('ch'));
        
        Account acc = new Account(Id=(Id)ja.get('aId'),Total_Hours__c=Integer.valueOf(ja.get('sh'))+Integer.valueOf(ja.get('ch')));
        
        accs.add(acc);
    }
    if(accs.size()>0)
        update accs;
}

John L.ax1429John L.ax1429
Thank you for the help I will try this I forgot to add this to my post let me know if this can be done, can I just total the current month and current year total support and customer hours. If it can't or requires more then what you are able to help its ok
Saikishore Reddy AengareddySaikishore Reddy Aengareddy

It can be done... Add these changes to above code

 

 

List <AggregateResult> aggrs =[SELECT Account.Id aId, sum(Support_Hours__C) sh,sum(Customer_Hours__C) ch from Case
                                   WHERE createddate=this_month AND AccountId IN:AccIds 
                                   GROUP BY Account.Id];

    

John L.ax1429John L.ax1429

This is what I have for the trigger on the case object from what you have given me.  How does this update the account page with the total number of hours?  Let me know if I need to add any code to the account object and Thank you for the help on this.

 

 

trigger totalSupportHours on Case (after insert, after update) {  

       set<Id> AccIds = new set<Id>();   

       List<Account> accs = new List<Account>();      

 

      for(Case c : Trigger.new){      

            if(c.AccountId<>null)            

                AccIds.add(c.AccountId);   

       }        

 

       List <AggregateResult> aggrs =[SELECT Account.Id aId, sum

(Time_With_Support__c) sh,sum(Time_With_Customer__c) ch from Case  

                                  WHERE createddate=this_month AND AccountId IN:AccIds   

                                 GROUP BY Account.Id];

 

    for (AggregateResult ja : aggrs)    

    {        

          System.debug('Acc Id:'+ja.get('aId') + ' Support Hrs: ' + ja.get('sh')+ ' Customer Hrs: ' + ja.get('ch'));

 

          Account acc = new Account(Id=(Id)ja.get('aId'),Total_Hours__c=Integer.valueOf(ja.get('sh'))+Integer.valueOf(ja.get('ch')));

 

          accs.add(acc);

    }

    if(accs.size()>0)

        update accs;

}

Saikishore Reddy AengareddySaikishore Reddy Aengareddy

John, 

 

You don't have to add any code. Once a case is created this trigger will be fired and it will update the associated account with appropriate hours.

 

Highlighted code will take care of updating the account associated to a case. 

 

Have you tried creating a case for an account? If yeswhere you not seeing the total support hours calculated on account?

 

    for (AggregateResult ja : aggrs)    

    {        

          System.debug('Acc Id:'+ja.get('aId') + ' Support Hrs: ' + ja.get('sh')+ ' Customer Hrs: ' + ja.get('ch'));

 

      Account acc = new Account(Id=(Id)ja.get('aId'),Total_Hours__c=Integer.valueOf(ja.get('sh'))+Integer.valueOf(ja.get('ch')));

 

          accs.add(acc);

    }

    if(accs.size()>0)

        update accs;    //->>>>here we are updating accounts(s).

 
John L.ax1429John L.ax1429

the acc1 and acc2 I added or at least tried to add as I need to show them separated also on the account record. Then I can do my report on those fields if this makes any sence.

 

 

 for (AggregateResult ja : aggrs)
    {
        System.debug('Acc Id:'+ja.get('aId') + ' Support Hrs: ' + ja.get('sh')+ ' Customer Hrs: ' + ja.get('ch'));
        
        Account acc = new Account(Id=(Id)ja.get('aId'),Total_Hours__c=Integer.valueOf(ja.get('sh'))+Integer.valueOf(ja.get('ch')));
        Account acc1 = new Account(Id=(Id)ja.get('aId'),Total_Support_Hours__c=Integer.valueOf(ja.get('sh')));
        Account acc2 = new Account(Id=(Id)ja.get('aId'),Total_Customer_Hours__c=Integer.valueOf(ja.get('ch')));

        accs.add(acc);
        accs.add(acc1);
        accs.add(acc2);
    }
    if(accs.size()>0)
        update accs;
    }

Saikishore Reddy AengareddySaikishore Reddy Aengareddy

Can you explain detail about your statement...."the acc1 and acc2 I added or at least tried to add as I need to show them separated also on the account record."

 

Are Total_Support_Hours__c and Total_Customer_Hours__c fields in Account object or case object?

John L.ax1429John L.ax1429

They are on the account object

Saikishore Reddy AengareddySaikishore Reddy Aengareddy

Try this code...

 

trigger totalSupportHours on Case (after insert, after update) {  

       set<Id> AccIds = new set<Id>();   

       List<Account> accs = new List<Account>();      

 

      for(Case c : Trigger.new){      

            if(c.AccountId<>null)            

                AccIds.add(c.AccountId);   

       }        

 

       List <AggregateResult> aggrs =[SELECT Account.Id aId, sum

(Time_With_Support__c) sh,sum(Time_With_Customer__c) ch from Case  

                                  WHERE createddate=this_month AND AccountId IN:AccIds   

                                 GROUP BY Account.Id];

 

    for (AggregateResult ja : aggrs)    

    {        

          System.debug('Acc Id:'+ja.get('aId') + ' Support Hrs: ' + ja.get('sh')+ ' Customer Hrs: ' + ja.get('ch'));

 

          Account acc = new Account(Id=(Id)ja.get('aId'),Total_Hours__c=Integer.valueOf(ja.get('sh'))+Integer.valueOf(ja.get('ch')), Total_Support_Hours__c=Integer.valueOf(ja.get('sh')),Total_Customer_Hours__c=Integer.valueOf(ja.get('ch')));

 

          accs.add(acc);

    }

    if(accs.size()>0)

        update accs;

}

This was selected as the best answer
John L.ax1429John L.ax1429

Thank you Sam for all the help.  This worked for what we need to do.