+ Start a Discussion
Stephanie_ArceStephanie_Arce 

Help bulkifying trigger

I'm wondering if someone could help me bulkify this trigger. As background, we have a custom object RBA_Invoice_Line_Item__c in master-detail to RBA_Participant__c and RBA_Invoice__c. We would like to get a double amount updated on the Participant based on the Invoice Date. If it's in the current month, the total amount from the Line Items would update "Current Month Total" on the Participant. The same would happen for Line Items where the invoice date = last month, to update a "Previous Month Total" field on Participant.

 

Here is the trigger:

trigger RBA_Invoice_Line_Item_RollupTrigger on RBA_Invoice_Line_Item__c (after update, after insert, after delete) {

     Map<Id, Double> previousMonthTotals = new Map<Id, Double>();
     Map<Id, Double> currentMonthTotals = new Map<Id, Double>();
     List<RBA_Participant__c> participantsToUpdate = new List<RBA_Participant__c>();
     List<RBA_Invoice_Line_Item__c> itemsForParticipant;
     
    
     List<RBA_Invoice_Line_Item__c> items;

     if (trigger.isDelete) {
         items = trigger.old;
     }
     else {
         items = trigger.new;
     }
     
        // Go through all of the line items that the trigger
        // is acting on
        
        for (RBA_Invoice_Line_Item__c triggerItem: items) {
            
            // Get the participant's ID for the line item and then
            // ensure their ID exists in the previous / current month totals
            // maps and their totals default to $0.00
            
            Id participant = triggerItem.RBA_Participant__c;
            
            if (!previousMonthTotals.containsKey(participant)) {
                previousMonthTotals.put(participant, 0.0);
            }
            if (!currentMonthTotals.containsKey(participant)) {
                currentMonthTotals.put(participant, 0.0);
            }
            
            // Sum the total cost of all previous month's line items
            // for the current participant (see participant above)
            //
            // 1. get all of the line items for the previous month
            // 2. for each line item update the mapping for the participant 
            //    to be (previous total + total cost)
                        
            itemsForParticipant =
                [SELECT Id, Total_Cost__c FROM RBA_Invoice_Line_Item__c
                    WHERE Invoice_Date__c = LAST_MONTH AND RBA_Participant__c = :participant];
            
            for (RBA_Invoice_Line_Item__c item : itemsForParticipant) {
                // previous month total = current total cost + total cost of line item
                previousMonthTotals.put(participant, 
                    previousMonthTotals.get(participant) + item.Total_Cost__c);
            }
            
            // Sum the total cost of all current month's line items
            // for the current participant (see participant above)
            //
            // 1. get all of the line items for the current month
            // 2. for each line item update the mapping for the participant 
            //    to be (previous total + total cost)
                          
            itemsForParticipant =
                [SELECT Id, Total_Cost__c FROM RBA_Invoice_Line_Item__c
                    WHERE Invoice_Date__c = THIS_MONTH AND RBA_Participant__c = :participant];
            
            for (RBA_Invoice_Line_Item__c item : itemsForParticipant) {
                // current month total = current total cost + total cost of line item
                currentMonthTotals.put(participant, 
                    currentMonthTotals.get(participant) + item.Total_Cost__c);
            }
                          
        }
        
        // Collect all of the unique participant IDs from both
        // mappings into a list
        
        List<Id> participants = new List<Id>();
        
        // First add all previous month unique participants
        
        for (Id previous : previousMonthTotals.keyset()) {
            participants.add(previous);
        }
        
        // ... then add any participants in the current month totals
        // that aren't in the previous month totals
        
        for (Id current : currentMonthTotals.keyset()) {
            if (!previousMonthTotals.containsKey(current)) {
                participants .add(current);
            }
        }
        
        // For each collected participant ID from the previous step, retrieve the participant
        // record from Salesforce then update their totals if and only if we collected
        // totals from them
        
        for (Id id : participants) {
        
            RBA_Participant__c participant = [SELECT Id, Previous_Month_Total__c, Current_Month_Total__c
                 FROM RBA_Participant__c WHERE Id = :id];
                 
            if (previousMonthTotals.containsKey(id)) {
                participant.Previous_Month_Total__c = previousMonthTotals.get(id);
            }
            
            if (currentMonthTotals.containsKey(id)) {
                participant.Current_Month_Total__c = currentMonthTotals.get(id);
            }
            
            // Collect participant record in a list we will then batch update
            // once we are done looping
            
            participantsToUpdate.add(participant);
        }
        
        // Batch update all updated participants
        
        update participantsToUpdate;

}

 

I see this in the debug log:

09:22:32.982|CUMULATIVE_PROFILING|SOQL operations|
Trigger.RBA_Invoice_Line_Item_RollupTrigger: line 43, column 1: [SELECT Id, Total_Cost__c FROM RBA_Invoice_Line_Item__c
                    WHERE Invoice_Date__c = LAST_MONTH AND RBA_Participant__c = :participant]: executed 51 times in 279 ms
Trigger.RBA_Invoice_Line_Item_RollupTrigger: line 60, column 1: [SELECT Id, Total_Cost__c FROM RBA_Invoice_Line_Item__c
                    WHERE Invoice_Date__c = THIS_MONTH AND RBA_Participant__c = :participant]: executed 50 times in 182 ms

 

but I'm not sure how/where to move those queries?

Best Answer chosen by Admin (Salesforce Developers) 
Dhaval PanchalDhaval Panchal

Try This,

trigger RBA_Invoice_Line_Item_RollupTrigger on RBA_Invoice_Line_Item__c (after update, after insert, after delete) {
     Map<Id, Double> previousMonthTotals = new Map<Id, Double>();
     Map<Id, Double> currentMonthTotals = new Map<Id, Double>();
     List<RBA_Participant__c> participantsToUpdate = new List<RBA_Participant__c>();
     List<RBA_Invoice_Line_Item__c> itemsForParticipant;
     List<RBA_Invoice_Line_Item__c> items;
     if (trigger.isDelete) {
         items = trigger.old;
     }
     else {
         items = trigger.new;
    }
	Set<ID> setParticipantId = new Set<ID>();
	for (RBA_Invoice_Line_Item__c triggerItem: items) {       
		if (!previousMonthTotals.containsKey(triggerItem.RBA_Participant__c)) {
			previousMonthTotals.put(triggerItem.RBA_Participant__c, 0.0);
		}
		if (!currentMonthTotals.containsKey(triggerItem.RBA_Participant__c)) {
			currentMonthTotals.put(triggerItem.RBA_Participant__c, 0.0);
		}
		if(triggerItem.RBA_Participant__c != NULL)
			setParticipantId.add(triggerItem.RBA_Participant__c);
    }
	List<RBA_Invoice_Line_Item__c> lstInvLineItem_ThisMonth = new List<RBA_Invoice_Line_Item__c>();
	List<RBA_Invoice_Line_Item__c> lstInvLineItem_LastMonth = new List<RBA_Invoice_Line_Item__c>();
	if(setParticipantId.size()>0){
		lstInvLineItem_LastMonth = [SELECT Id, Total_Cost__c, RBA_Participant__c FROM RBA_Invoice_Line_Item__c
				WHERE Invoice_Date__c = LAST_MONTH AND RBA_Participant__c IN :setParticipantId];
		lstInvLineItem_ThisMonth = [SELECT Id, Total_Cost__c, RBA_Participant__c FROM RBA_Invoice_Line_Item__c
				WHERE Invoice_Date__c = THIS_MONTH AND RBA_Participant__c IN :setParticipantId];
	}
	if(lstInvLineItem_LastMonth.size()>0){
		for(RBA_Invoice_Line_Item__c item:lstInvLineItem_LastMonth){    
			previousMonthTotals.put(item.RBA_Participant__c, 
				previousMonthTotals.get(item.RBA_Participant__c) + item.Total_Cost__c); 
		}
	}
	if(lstInvLineItem_ThisMonth.size()>0){
		for(RBA_Invoice_Line_Item__c item:lstInvLineItem_ThisMonth){    
			currentMonthTotals.put(item.RBA_Participant__c, 
				currentMonthTotals.get(item.RBA_Participant__c) + item.Total_Cost__c); 
		}
	}
	List<Id> participants = new List<Id>();
	for (Id previous : previousMonthTotals.keyset()) {
		participants.add(previous);
	}
	for (Id current : currentMonthTotals.keyset()) {
		if (!previousMonthTotals.containsKey(current)) {
			participants.add(current);
		}
	}
	List<RBA_Participant__c> lstRBAParticipant = new List<RBA_Participant__c>();
	if(participants.size()>0){
		lstRBAParticipant = [SELECT Id, Previous_Month_Total__c, Current_Month_Total__c
			 FROM RBA_Participant__c WHERE Id IN :participants];
	}
	if(lstRBAParticipant.size()>0){
		for(RBA_Participant__c participant:lstRBAParticipant){
			if (previousMonthTotals.containsKey(participant.id)) {
				participant.Previous_Month_Total__c = previousMonthTotals.get(participant.id);
			}
			
			if (currentMonthTotals.containsKey(participant.id)) {
				participant.Current_Month_Total__c = currentMonthTotals.get(participant.id);
			}
			participantsToUpdate.add(participant);
		}
	}
	update participantsToUpdate;
}

 

*** I have not compiled this code, so please correct if any syntax error.

All Answers

Dhaval PanchalDhaval Panchal

Try This,

trigger RBA_Invoice_Line_Item_RollupTrigger on RBA_Invoice_Line_Item__c (after update, after insert, after delete) {
     Map<Id, Double> previousMonthTotals = new Map<Id, Double>();
     Map<Id, Double> currentMonthTotals = new Map<Id, Double>();
     List<RBA_Participant__c> participantsToUpdate = new List<RBA_Participant__c>();
     List<RBA_Invoice_Line_Item__c> itemsForParticipant;
     List<RBA_Invoice_Line_Item__c> items;
     if (trigger.isDelete) {
         items = trigger.old;
     }
     else {
         items = trigger.new;
    }
	Set<ID> setParticipantId = new Set<ID>();
	for (RBA_Invoice_Line_Item__c triggerItem: items) {       
		if (!previousMonthTotals.containsKey(triggerItem.RBA_Participant__c)) {
			previousMonthTotals.put(triggerItem.RBA_Participant__c, 0.0);
		}
		if (!currentMonthTotals.containsKey(triggerItem.RBA_Participant__c)) {
			currentMonthTotals.put(triggerItem.RBA_Participant__c, 0.0);
		}
		if(triggerItem.RBA_Participant__c != NULL)
			setParticipantId.add(triggerItem.RBA_Participant__c);
    }
	List<RBA_Invoice_Line_Item__c> lstInvLineItem_ThisMonth = new List<RBA_Invoice_Line_Item__c>();
	List<RBA_Invoice_Line_Item__c> lstInvLineItem_LastMonth = new List<RBA_Invoice_Line_Item__c>();
	if(setParticipantId.size()>0){
		lstInvLineItem_LastMonth = [SELECT Id, Total_Cost__c, RBA_Participant__c FROM RBA_Invoice_Line_Item__c
				WHERE Invoice_Date__c = LAST_MONTH AND RBA_Participant__c IN :setParticipantId];
		lstInvLineItem_ThisMonth = [SELECT Id, Total_Cost__c, RBA_Participant__c FROM RBA_Invoice_Line_Item__c
				WHERE Invoice_Date__c = THIS_MONTH AND RBA_Participant__c IN :setParticipantId];
	}
	if(lstInvLineItem_LastMonth.size()>0){
		for(RBA_Invoice_Line_Item__c item:lstInvLineItem_LastMonth){    
			previousMonthTotals.put(item.RBA_Participant__c, 
				previousMonthTotals.get(item.RBA_Participant__c) + item.Total_Cost__c); 
		}
	}
	if(lstInvLineItem_ThisMonth.size()>0){
		for(RBA_Invoice_Line_Item__c item:lstInvLineItem_ThisMonth){    
			currentMonthTotals.put(item.RBA_Participant__c, 
				currentMonthTotals.get(item.RBA_Participant__c) + item.Total_Cost__c); 
		}
	}
	List<Id> participants = new List<Id>();
	for (Id previous : previousMonthTotals.keyset()) {
		participants.add(previous);
	}
	for (Id current : currentMonthTotals.keyset()) {
		if (!previousMonthTotals.containsKey(current)) {
			participants.add(current);
		}
	}
	List<RBA_Participant__c> lstRBAParticipant = new List<RBA_Participant__c>();
	if(participants.size()>0){
		lstRBAParticipant = [SELECT Id, Previous_Month_Total__c, Current_Month_Total__c
			 FROM RBA_Participant__c WHERE Id IN :participants];
	}
	if(lstRBAParticipant.size()>0){
		for(RBA_Participant__c participant:lstRBAParticipant){
			if (previousMonthTotals.containsKey(participant.id)) {
				participant.Previous_Month_Total__c = previousMonthTotals.get(participant.id);
			}
			
			if (currentMonthTotals.containsKey(participant.id)) {
				participant.Current_Month_Total__c = currentMonthTotals.get(participant.id);
			}
			participantsToUpdate.add(participant);
		}
	}
	update participantsToUpdate;
}

 

*** I have not compiled this code, so please correct if any syntax error.

This was selected as the best answer
Stephanie_ArceStephanie_Arce

THANK YOU so much! I have a lot to learn!