Sign Up ›

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

Criteria based rollup in trigger

I have an object, Object1__c, that has a lookup relationship to the account object.  Also on Object1__c is a picklist field that is used to indicate the outcome of a call (declined, no answer, etc.).

On the account object I have then placed a number of custom fields that will be used to house the count of each outcome value for Object1 records related to that account.

In my trigger on Object1, I am able to populate the appropriate custom field on the account object using this code as an example:
  
List<Account> aToUpdate = new List<Account>();

for(Account a : [SELECT Id, Declined__c, (SELECT Id, Outcome__c FROM Object1s__r WHERE Disposition__c = 'Declined') FROM Account WHERE Id IN :acctSet]){

    a.Declined__c = a.Object1s__r.size();                 
    aToUpdate.add(a);      
          
}

Update aToUpdate;
...but doing it this way doesn't feel right as I'd then have a separate SOQL query for each outcome value.  

Is there a way to do this with one query?  I thought something along the lines of AggregateResult might be appropriate, but am having trouble then ensuring that I pull the correct values based on the individual account Ids found in my set of account Ids (acctSet).

Any help would be appreciated.
 
John PipkinJohn Pipkin
pjs, 

I could be misreading this, so I'm sorry if I am.
This code only has 1 query. The query won't run through each iteration, just once and then it will loop through the list that it returns. 
PaulJSPaulJS
John - thanks for your reply.  Here all the code I have thus far.  Admittedly, this is incomplete as I was trying to figure out how to overcome the issue I raised in my initial post.
  • I am looping through all the records in Trigger.new and, if there is a change to the outcome__c field, I am adding its account to the set.
  • If the set includes an account, I am then, in this example, querying the account object for the account and all related Object1 records whose disposition is "declined."
  • I am then adding the account Id and the size of the related list of Object1 records to the aToUpdate list, which I will then use to update the appropriate account records.
trigger CallSummaryResults on Object1__c (after update) {
    
    //Create a set of accounts
    Set<Account> acctSet = new Set<Account>();
    for(Object1__c p : Trigger.new){
        
        //If the Outcome__c field was changed, add the account to acctSet
        if(p.Disposition__c != trigger.oldMap.get(p.Id).Outcome__c){
        	acctSet.add(p.account__r);
    	}
    }
    
    if(acctSet != NULL){

		List<Account> aToUpdate = new List<Account>();
        
		for(Account a : [SELECT Id, Declined__c, (SELECT Id, Outcome__c FROM Object1s__r WHERE Disposition__c = 'Declined') FROM Account WHERE Id IN :acctSet]){
			
            a.Declined__c = a.Object1s__r.size();
            aToUpdate.add(a);
		}
        
        Update aToUpdate;
        
    }
}
John PipkinJohn Pipkin
I made a couple of small tweeks. Your account query was comparing an ID to an SObject
  
trigger CallSummaryResults on Object1__c (after update) {
    
    //Create a set of accounts
    Set<ID> acctSet = new Set<ID>();
    for(Object1__c p : Trigger.new){
        
        //If the Outcome__c field was changed, add the account to acctSet
        if(p.Outcome__c != trigger.oldMap.get(p.Id).Outcome__c){
        	acctSet.add(p.account__c);
    	}
    }
    
    if(acctSet != NULL){

		List<Account> aToUpdate = new List<Account>();
        
		for(Account a : [SELECT Id, Declined__c, (SELECT Id, Outcome__c FROM Object1s__r WHERE Disposition__c = 'Declined') FROM Account WHERE Id IN :acctSet]){
			
            a.Declined__c = a.Object1s__r.size();
            aToUpdate.add(a);
		}
        
        Update aToUpdate;
        
    }
}

 
PaulJSPaulJS
Thanks John.  "Declined" is just one example, but there are also other values in the outcome__c field on Object1 for which I'd add a placeholder field on the account object.  If I wanted to do a similar update to the account object for each of those outcome values/placeholder fields, do I need to repeat this query 4 or 5 (or however many) times, or is there some way to run just one query?
John PipkinJohn Pipkin
You could do something like this:
  
trigger CallSummaryResults on Object1__c (after update) {
    
    //Create a set of accounts
    Set<ID> acctSet = new Set<ID>();
    for(Object1__c p : Trigger.new){
        
        //If the Outcome__c field was changed, add the account to acctSet
        if(p.Outcome__c != trigger.oldMap.get(p.Id).Outcome__c){
        	acctSet.add(p.account__c);
    	}
    }
    
    if(acctSet != NULL){

		List<Account> aToUpdate = new List<Account>();
		Map<ID,Account> AcctMap = new Map<ID,Account>();
		AcctMap = new Map<ID,Account>([Select Id, Declined__c, otherfields from Account where Id in :acctSet]);
		Object1__c olist = [Select Id, Outcome__c, Account__c from Object1__c where Disposition__c in ('Declined','other values') and Account__c in :accSet Group by Account__c];

		For(Account a :AcctMap.values()){
			Integer i1 = 0;
			Integer i2 = 0;
			for(Object1__c o :olist){
				if(o.Account__c == a.Id){
					if(Disposition__c == 'Declined')
						i1 += 1;
					else if(Disposition__c == 'other values')
						i2 += 1;
				}
			}
			a.Declined__c = i1;
			a.otherfields = i2;
			aToUpdate.add(a);

		}
		if(!aToUpdate.isEmpty())
        	update aToUpdate;
    }
}

 
John PipkinJohn Pipkin
oops. have some syntax errors: 
trigger CallSummaryResults on Object1__c (after update) {
    
    //Create a set of accounts
    Set<ID> acctSet = new Set<ID>();
    for(Object1__c p : Trigger.new){
        
        //If the Outcome__c field was changed, add the account to acctSet
        if(p.Outcome__c != trigger.oldMap.get(p.Id).Outcome__c){
        	acctSet.add(p.account__c);
    	}
    }
    
    if(acctSet != NULL){

		List<Account> aToUpdate = new List<Account>();
		Map<ID,Account> AcctMap = new Map<ID,Account>();
		AcctMap = new Map<ID,Account>([Select Id, Declined__c, otherfields from Account where Id in :acctSet]);
		Object1__c olist = [Select Id, Outcome__c, Account__c from Object1__c where Disposition__c in ('Declined','other values') and Account__c in :accSet Group by Account__c];

		For(Account a :AcctMap.values()){
			Integer i1 = 0;
			Integer i2 = 0;
			for(Object1__c o :olist){
				if(o.Account__c == a.Id){
					if(o.Disposition__c == 'Declined')
						i1 += 1;
					else if(o.Disposition__c == 'other values')
						i2 += 1;
				}
			}
			a.Declined__c = i1;
			a.otherfields = i2;
			aToUpdate.add(a);

		}
		if(!aToUpdate.isEmpty())
        	update aToUpdate;
    }
}