Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
huskerwendyhuskerwendy 

AggregateResult with Multiple Values

I'm an admin trying to be a devleloper and am struggling. I'm trying to bulkify my trigger but am not sure how to assign multiple values from an aggregate query to a list. I've tried using a set, map and list but every example I see just has a Key and one value. I have two keys and multiple values.

My end goal is to update the case with the totalRevenue, TotalHours, and OverageHours from the billing. I have a related list of billing for each case. Each billing has a product that can have a different incident charge, per incident hours and possibly overage charge and each case can have multiple billings for the same product BUT only one case is counted as an incident. So if there are 4 billings with two different products, the incident charge would only be there once for each product, not four times for each billing. I wrote an aggregate query to get the sum of hours worked for each case and product. What I need to happen is for my trigger to loop through the aggregate results and for each case the for each product:

for case
loop through each product
if the totalHoursWorked > Per_Incident_hours__c then
total_hours =  (totalHoursWorked - incidentHours * overageCharge) + incidentCharge 
else total_hours = totalHoursWorked * incidentCharge
get next product
then update case TotalRevenue, TotalHours, TotalOverageHours
get next case

Here's what I have. However I don't know what to do with the AggregateResult set.  
trigger CaseClosed on Case (after insert, after update) {
    // this code will get the list of closed cases for Cloud IT records. It will loop through the billing for each case 
    // and calculate the total case revenue along with the Total Hours Worked and Case Overage Hours. 
    // list to hold updated case values
    List<Case> updateCase = new List<Case>();
    
   // get list of closed cases and add the case to the case list
   	set<id> caseIDs = new set<id>();
    for (Case c : trigger.new){
        	if (c.IsClosed && c.RecordTypeId == '012600000001COh') {
               	caseIDs.add(c.Id); 
                  system.debug('in CaseClosed trigger');
        	}
        } 
    
    // loop through the caseIDs and get billing for each product
    // create an aggregate Map of all billing for each case by product
    LIST<aggregateResult> results = ([SELECT Case__c, Product__c, MAX(Per_Incident_Hours__c) incidentHours, MAX(Incident_Charge__c) incidentCharge, MAX(Overage_Amount__c) overageCharge, SUM(Hours_Worked__c) totalHoursWorked FROM Billing__c  WHERE Case__c in : caseIds GROUP BY Case__c, Product__c]);  
    decimal total_hours = 0;
    decimal total_dollars = 0;
    decimal total_overage_hours = 0;
    
    // create a map to hold results from query
    Map<Id, ID> arCaseProduct = new Map<Id, Id>();
   list<Object> myList = new list<Object>();
    // loop through and get billing
    for (AggregateResult ar : results){
    	System.debug('in aggregate debug'+ar.get('Case__c')+'-'+ar.get('Product__c')+'-'+ar.get('incidentHours')+'-'+ar.get('incidentCharge')+'-'+ar.get('overageCharge')+'-'+ar.get('totalHoursWorked'));  
    	arCaseProduct.put((ID)ar.get('Case__c'), (id)ar.get('Product__c'));
        system.debug('arCaseProduct is '+ arCaseProduct);
    }
}



 
Best Answer chosen by huskerwendy
huskerwendyhuskerwendy
The above trigger didn't work because I was getting recursion "CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY: maximum trigger depth exceeded and if I fixed that, I was getting an error "System.ListException: Duplicate id in list:".  I changed the trigger to a Before Insert, Before Update and modified some of the code and that fixed my problem. 
trigger CaseClosed on Case (before insert, before update) {
    if (trigger.isUpdate){
        // this code will get the list of closed cases for Cloud IT records. It will loop through the billing for each case 
        // and calculate the total case revenue along with the Total Hours Worked and Case Overage Hours. 
        
       // get list of closed cases and add the case to the case list
        set<id> caseIDs = new set<id>();
        for (Case c : trigger.new){
            Case oldCase = System.Trigger.oldMap.get(c.Id);       
            system.debug('oldCase Status is ' + oldCase.Status);
            system.debug('status is ' + c.Status);
            system.debug('isClosed is ' + c.IsClosed);
            system.debug('recordtypeid is ' + c.RecordTypeId);
           
            if(oldCase.Status != c.Status && c.Status == 'Closed' && c.RecordTypeId=='012600000001COhAAM'){
                caseIDs.add(c.Id); 
            	system.debug('in CaseClosed trigger');
            }     
        }
        
        // loop through the caseIDs and get billing for each product
        // create an aggregate Map of all billing for each case by product
        LIST<aggregateResult> results = ([SELECT Case__c, Product__c, MAX(Per_Incident_Hours__c) incidentHours, MAX(Incident_Charge__c) incidentCharge, MAX(Overage_Amount__c) overageCharge, SUM(Hours_Worked__c) totalHoursWorked FROM Billing__c  WHERE Case__c in : caseIds GROUP BY Case__c, Product__c]);  
        Id currentCaseId;
        Id currentProductId;
        decimal total_hours = 0;
        decimal total_revenue = 0;
        decimal total_overage_hours = 0;
        decimal prod_per_incident_hours = 0;
        decimal prod_per_incident_charge = 0;
        decimal prod_total_hours_worked = 0;
        decimal prod_overage_charge = 0;
        
        // loop through and get billing
        for (AggregateResult ar : results){
            //System.debug('in aggregate debug'+ar.get('Case__c')+'-'+ar.get('Product__c')+'-'+ar.get('incidentHours')+'-'+ar.get('incidentCharge')+'-'+ar.get('overageCharge')+'-'+ar.get('totalHoursWorked'));  
            system.debug(' ar is ' + ar);
            Id thisCaseId = (Id)ar.get('Case__c');
            Id thisProductId = (Id)ar.get('Product__c');
            if(thisCaseId != currentCaseId){
                If(thisProductId != currentProductId){
                    prod_total_hours_worked = (decimal)ar.get('totalHoursWorked');
                    prod_per_incident_hours = (decimal)ar.get('incidentHours');
                    prod_per_incident_charge = (decimal)ar.get('incidentCharge');
                    prod_overage_charge = (decimal)ar.get('overageCharge');
                    total_hours = total_hours + prod_total_hours_worked;
                        if(prod_total_hours_worked < prod_per_incident_hours){  
                            system.debug('total_revenue is ' + total_revenue);
                            total_revenue = total_revenue + prod_total_hours_worked * prod_per_incident_charge;
                            total_overage_hours = total_overage_hours + 0;
                        }else {
                            total_revenue = (total_revenue + ((prod_total_hours_worked - prod_per_incident_hours) * prod_overage_charge)  + prod_per_incident_charge);
                            total_overage_hours = (total_overage_hours + (prod_total_hours_worked - prod_per_incident_hours));
                        } 
                    currentProductId = thisProductId;  
                     for (Case upCase : Trigger.new){
                      	upCase.Case_Revenue__c = total_revenue;
                       	upCase.Hours_Worked__c = total_hours;
                       	upCase.Case_Overage_Hours__c = total_overage_hours;
                     }
                  //  system.debug('in Product if - currentProductId is: '+ currentProductId + ' thisProductId is: ' + thisProductId  + ' currentCaseId is: ' + currentCaseId + ' thisCaseId is: ' + thisCaseId);
                  //  system.debug('variables values are = total_revenue ' + total_revenue + ' total_overage_hours ' + total_overage_hours + ' total_hours ' + total_hours);
                } 
            }          
        }
    }
}

All Answers

huskerwendyhuskerwendy
I *think* I'm making progress. I have the right values in the "If(thisProductID != currentProductID) section. However, it will never go into the next else state to update the case. I'm not sure what I'm doing wrong.  
trigger CaseClosed on Case (after insert, after update) {
    // this code will get the list of closed cases for Cloud IT records. It will loop through the billing for each case 
    // and calculate the total case revenue along with the Total Hours Worked and Case Overage Hours. 
    // list to hold updated case values
    List<Case> updateCases = new List<Case>();
    
   // get list of closed cases and add the case to the case list
   	set<id> caseIDs = new set<id>();
    for (Case c : trigger.new){
        	if (c.IsClosed && c.RecordTypeId == '012600000001COh') {
               	caseIDs.add(c.Id); 
                  system.debug('in CaseClosed trigger');
        	}
        } 
    
    // loop through the caseIDs and get billing for each product
    // create an aggregate Map of all billing for each case by product
    LIST<aggregateResult> results = ([SELECT Case__c, Product__c, MAX(Per_Incident_Hours__c) incidentHours, MAX(Incident_Charge__c) incidentCharge, MAX(Overage_Amount__c) overageCharge, SUM(Hours_Worked__c) totalHoursWorked FROM Billing__c  WHERE Case__c in : caseIds GROUP BY Case__c, Product__c]);  
    Id currentCaseId;
    Id currentProductId;
    decimal total_hours = 0;
    decimal total_revenue = 0;
    decimal total_overage_hours = 0;
    decimal prod_per_incident_hours = 0;
    decimal prod_per_incident_charge = 0;
    decimal prod_total_hours_worked = 0;
    decimal prod_overage_charge = 0;
    
    // loop through and get billing
    for (AggregateResult ar : results){
    	//System.debug('in aggregate debug'+ar.get('Case__c')+'-'+ar.get('Product__c')+'-'+ar.get('incidentHours')+'-'+ar.get('incidentCharge')+'-'+ar.get('overageCharge')+'-'+ar.get('totalHoursWorked'));  
    	system.debug(' ar is ' + ar);
        Id thisCaseId = (Id)ar.get('Case__c');
        Id thisProductId = (Id)ar.get('Product__c');
        if(thisCaseId != currentCaseId){
            If(thisProductId != currentProductId){
                prod_total_hours_worked = (decimal)ar.get('totalHoursWorked');
                prod_per_incident_hours = (decimal)ar.get('incidentHours');
                prod_per_incident_charge = (decimal)ar.get('incidentCharge');
                prod_overage_charge = (decimal)ar.get('overageCharge');
                total_hours = total_hours + prod_total_hours_worked;
                    if(prod_total_hours_worked < prod_per_incident_hours){               
                        total_revenue = total_revenue + prod_total_hours_worked * prod_per_incident_charge;
                        total_overage_hours = total_overage_hours + 0;
                    }else {
                        total_revenue = (((prod_total_hours_worked - prod_per_incident_hours) * prod_overage_charge)  + prod_per_incident_charge);
                        total_overage_hours = prod_total_hours_worked - prod_per_incident_hours;
                    } 
                currentProductId = thisProductId;                
                system.debug('in Product loop - currentProductId is: '+ currentProductId + ' thisProductId is: ' + thisProductId  + ' currentCaseId is: ' + currentCaseId + ' thisCaseId is: ' + thisCaseId);
				system.debug('variables values are = total_revenue ' + total_revenue + ' total_overage_hours ' + total_overage_hours + ' total_hours ' + total_hours);
            } else {
                currentCaseId = thisCaseId;
                system.debug('in case else case id is' + thisCaseId + 'total_revenue is' + total_revenue + 'total_hours is ' + total_hours + 'overage hours is ' + total_overage_hours); 
                // add totals to case map
                 Case c = new Case(
                     id = thisCaseId,
                     Case_Revenue__c = total_revenue,
                     Hours_Worked__c = total_hours,
                     Case_Overage_Hours__c = total_overage_hours);
                	UpdateCases.add(c);
            }
        } else {
            system.debug('in final else ');        
        }          
    }
    system.debug('ouside of for loop before update' + UpdateCases);
    update UpdateCases;
}

Am I on the right track?
huskerwendyhuskerwendy
The above trigger didn't work because I was getting recursion "CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY: maximum trigger depth exceeded and if I fixed that, I was getting an error "System.ListException: Duplicate id in list:".  I changed the trigger to a Before Insert, Before Update and modified some of the code and that fixed my problem. 
trigger CaseClosed on Case (before insert, before update) {
    if (trigger.isUpdate){
        // this code will get the list of closed cases for Cloud IT records. It will loop through the billing for each case 
        // and calculate the total case revenue along with the Total Hours Worked and Case Overage Hours. 
        
       // get list of closed cases and add the case to the case list
        set<id> caseIDs = new set<id>();
        for (Case c : trigger.new){
            Case oldCase = System.Trigger.oldMap.get(c.Id);       
            system.debug('oldCase Status is ' + oldCase.Status);
            system.debug('status is ' + c.Status);
            system.debug('isClosed is ' + c.IsClosed);
            system.debug('recordtypeid is ' + c.RecordTypeId);
           
            if(oldCase.Status != c.Status && c.Status == 'Closed' && c.RecordTypeId=='012600000001COhAAM'){
                caseIDs.add(c.Id); 
            	system.debug('in CaseClosed trigger');
            }     
        }
        
        // loop through the caseIDs and get billing for each product
        // create an aggregate Map of all billing for each case by product
        LIST<aggregateResult> results = ([SELECT Case__c, Product__c, MAX(Per_Incident_Hours__c) incidentHours, MAX(Incident_Charge__c) incidentCharge, MAX(Overage_Amount__c) overageCharge, SUM(Hours_Worked__c) totalHoursWorked FROM Billing__c  WHERE Case__c in : caseIds GROUP BY Case__c, Product__c]);  
        Id currentCaseId;
        Id currentProductId;
        decimal total_hours = 0;
        decimal total_revenue = 0;
        decimal total_overage_hours = 0;
        decimal prod_per_incident_hours = 0;
        decimal prod_per_incident_charge = 0;
        decimal prod_total_hours_worked = 0;
        decimal prod_overage_charge = 0;
        
        // loop through and get billing
        for (AggregateResult ar : results){
            //System.debug('in aggregate debug'+ar.get('Case__c')+'-'+ar.get('Product__c')+'-'+ar.get('incidentHours')+'-'+ar.get('incidentCharge')+'-'+ar.get('overageCharge')+'-'+ar.get('totalHoursWorked'));  
            system.debug(' ar is ' + ar);
            Id thisCaseId = (Id)ar.get('Case__c');
            Id thisProductId = (Id)ar.get('Product__c');
            if(thisCaseId != currentCaseId){
                If(thisProductId != currentProductId){
                    prod_total_hours_worked = (decimal)ar.get('totalHoursWorked');
                    prod_per_incident_hours = (decimal)ar.get('incidentHours');
                    prod_per_incident_charge = (decimal)ar.get('incidentCharge');
                    prod_overage_charge = (decimal)ar.get('overageCharge');
                    total_hours = total_hours + prod_total_hours_worked;
                        if(prod_total_hours_worked < prod_per_incident_hours){  
                            system.debug('total_revenue is ' + total_revenue);
                            total_revenue = total_revenue + prod_total_hours_worked * prod_per_incident_charge;
                            total_overage_hours = total_overage_hours + 0;
                        }else {
                            total_revenue = (total_revenue + ((prod_total_hours_worked - prod_per_incident_hours) * prod_overage_charge)  + prod_per_incident_charge);
                            total_overage_hours = (total_overage_hours + (prod_total_hours_worked - prod_per_incident_hours));
                        } 
                    currentProductId = thisProductId;  
                     for (Case upCase : Trigger.new){
                      	upCase.Case_Revenue__c = total_revenue;
                       	upCase.Hours_Worked__c = total_hours;
                       	upCase.Case_Overage_Hours__c = total_overage_hours;
                     }
                  //  system.debug('in Product if - currentProductId is: '+ currentProductId + ' thisProductId is: ' + thisProductId  + ' currentCaseId is: ' + currentCaseId + ' thisCaseId is: ' + thisCaseId);
                  //  system.debug('variables values are = total_revenue ' + total_revenue + ' total_overage_hours ' + total_overage_hours + ' total_hours ' + total_hours);
                } 
            }          
        }
    }
}
This was selected as the best answer