You need to sign in to do that
Don't have an account?
John 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
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
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;
}
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];
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;
}
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).
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;
}
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?
They are on the account object
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;
}
Thank you Sam for all the help. This worked for what we need to do.