+ Start a Discussion
Chad MoutesChad Moutes 

Rollup Summary Trigger for a lookup relationship

I have a custom object called "Project" that looks up to the object "Account" and I would like to create a Trigger that simply tells me how many "Projects" are linked to that specific "Account". Im just looking for someone to point me in the right direction.

Any help would be greatly appreciated.
Best Answer chosen by Chad Moutes
KevinPKevinP
Chad,

My apologies. 

I'm going to assume you're familiar with the basics of trigger creation (where to go to create one, what the various contexts mean etc.)

Your trigger is going to fire whenever a record of a given type is updated/created/deleted/undeleted depending on how you setup the trigger. So, for the sake of explaination i'm going to assume you're writing this trigger on the project object. 

Here's some highly annontated code to help get you started.
 
trigger RollupProjectCountToAccountTrigger on Project__c (after insert) {

	//Salesforce is going to automatically give you a list of incoming project__c records in a variable called 
	//Trigger.new . Because we're running this trigger *after* insert you also have access to the Trigger.NewMap variable
	//which is a Map record type of <id,Project__c> -- because it uses ID's as the keys, the newMap variable is only avialable 
	//after insert and during update calls when the system has assigned the id.

	// lets kick this off by creating a list of id's that we can use to store account id's. 
	List<Id> AccountIds = new List<Id>();

	// now we'll populate that list with account id's
	// Why? because we're actually going to need those account id's here in just a second.
	// The trigger.new list is only going to contain the *new* project records. our rollup
	// needs to know all of them.
	for(Project__c p: Trigger.new) {
	  AccountIds.add(p.account__c); //this assumes your project__c lookup field to account is called Account__c
	}

	// Now we'll make a query, to get all the project records who's accountID is in the list of new project records.
	AggregateResult[] groupedResults = [SELECT count(id)projectCount, Account__c FROM Project__c WHERE Account__c in :AccountIds GROUP BY account__c]; 
	
	// Additionally, we'll need a group of account objects to update once we've gotten the counts. For this we'll use a map. 
	Map<id,Account> accountMap = new Map<Id,Account>([SELECT id, count_of_projects__c FROM Account WHERE Id in :AccountIds]);

	//Now that we have an account map, we can iterate over the grouped results, and set the account map's data to match. 
	for(AggregateResult ar: groupedResults) {
	  accountMap.get(ar.get('Account__c')).count_of_projects__c = ar.get('projectCount');
	}

	// Lastly, we'll need to update our account objects:
	try {
	  update accountMap.values();
	} catch(DmlException e) {
	  System.debug(e.getMessage());
	}

}

Ask questions!
 

All Answers

KevinPKevinP
This will likely do what you want/need:

http://andyinthecloud.com/2013/07/07/new-tool-declarative-rollups-for-lookups/ (http://andyinthecloud.com/2013/07/07/new-tool-declarative-rollups-for-lookups/" target="_blank)
Chad MoutesChad Moutes
I appreciate the response, but I am looking to write the Apex myself versus installing a package into my system.
KevinPKevinP
Chad,

My apologies. 

I'm going to assume you're familiar with the basics of trigger creation (where to go to create one, what the various contexts mean etc.)

Your trigger is going to fire whenever a record of a given type is updated/created/deleted/undeleted depending on how you setup the trigger. So, for the sake of explaination i'm going to assume you're writing this trigger on the project object. 

Here's some highly annontated code to help get you started.
 
trigger RollupProjectCountToAccountTrigger on Project__c (after insert) {

	//Salesforce is going to automatically give you a list of incoming project__c records in a variable called 
	//Trigger.new . Because we're running this trigger *after* insert you also have access to the Trigger.NewMap variable
	//which is a Map record type of <id,Project__c> -- because it uses ID's as the keys, the newMap variable is only avialable 
	//after insert and during update calls when the system has assigned the id.

	// lets kick this off by creating a list of id's that we can use to store account id's. 
	List<Id> AccountIds = new List<Id>();

	// now we'll populate that list with account id's
	// Why? because we're actually going to need those account id's here in just a second.
	// The trigger.new list is only going to contain the *new* project records. our rollup
	// needs to know all of them.
	for(Project__c p: Trigger.new) {
	  AccountIds.add(p.account__c); //this assumes your project__c lookup field to account is called Account__c
	}

	// Now we'll make a query, to get all the project records who's accountID is in the list of new project records.
	AggregateResult[] groupedResults = [SELECT count(id)projectCount, Account__c FROM Project__c WHERE Account__c in :AccountIds GROUP BY account__c]; 
	
	// Additionally, we'll need a group of account objects to update once we've gotten the counts. For this we'll use a map. 
	Map<id,Account> accountMap = new Map<Id,Account>([SELECT id, count_of_projects__c FROM Account WHERE Id in :AccountIds]);

	//Now that we have an account map, we can iterate over the grouped results, and set the account map's data to match. 
	for(AggregateResult ar: groupedResults) {
	  accountMap.get(ar.get('Account__c')).count_of_projects__c = ar.get('projectCount');
	}

	// Lastly, we'll need to update our account objects:
	try {
	  update accountMap.values();
	} catch(DmlException e) {
	  System.debug(e.getMessage());
	}

}

Ask questions!
 
This was selected as the best answer
Bennie CloudyBennie Cloudy
Kevin,

I think we need to object typecast here, the line 27 would be
accountMap.put((Id)ar.get('account__c'),(Integer)ar.get('projectcount'));
Chad MoutesChad Moutes
In line 16 you say "assuming that account__c is my lookup field" well it is Company_Name__c but my question is for the rest of the trigger you keep refferencing Account__c are you using that as the Lookup field, or the object. Because the object is just the standard account. 
KevinPKevinP
@bennie,

You're right, type casting would be helpful here.
KevinPKevinP
@chad, 

Yes, where you see me using "Account__c" thats the lookup *field*, which in your case would actually be "company_Name__c"
Chad MoutesChad Moutes
Alright, so I wrote the trigger and when i went to save it I recived this error " Compile Error: Incompatible key type Object for MAP<Id,Account> at line 14 column 9" My line 14 is your line 27, should i change it over to the one that Bennie suggested?
 
KevinPKevinP
@chad, 

Yes. use the version bennie posted.
Chad MoutesChad Moutes
trigger RollupProjectCountToAccountTrigger on Project__c (after insert) {

    List<Id> AccountIds = new List<Id>();
    
    for(project__c p: Trigger.new) {
        AccountIds.add(p.Company_Name__c);
    }
    
    AggregateResult[] groupedResults = [SELECT count(id)projectCount, Company_Name__c FROM Project__c WHERE Company_Name__c in :AccountIds GROUP BY Company_Name__C];
    
    Map<id,Account> accountMap = new Map<Id,Account>([SELECT id, Total_Projects__c FROM Account WHERE Id in :AccountIds]); 
    
    for(AggregateResult ar: groupedResults) {
        accountMap.put((Id)ar.get('Company_Name__c'),(Integer)ar.get('projectcount'));
    }
    
    try {
        update accountMap.values();
    }   catch(DmlException e) {
        System.debug(e.getMessage());
    }

}
Now I'm receiving this error : Compile Error: Incompatible value type Integer for MAP<Id,Account> at line 14 column 9
KevinPKevinP
@chad,

I appologize. 

line 14 should read:
 
accountMap.get((Id)ar.get('Company_Name__c')).project_count_field__c = ar.get('projectcount'));

where your "project_count_field__c" is replaced by the fieldname you want to store your rollup count in on account.
Chad MoutesChad Moutes
accountMap.get((Id)ar.get('Company_Name__c')).Total_Projects__c = ar.get('projectcount');

Here is my line 14, im not getting an error saying : Illegal assignment from Object to Decimal at line 14 column 55
KevinPKevinP
you'll need to add a typecast to decimal after the = before the ar. ie:

= (decimal) ar.get('projectcount');
 
Chad MoutesChad Moutes
Wow! Kevin I can't thank you enough, it finally works lol. Now i just have to write the Test Class lol.
Chad MoutesChad Moutes
One more thing,

I would like to add a after delete to this trigger, because i want the count to go down just incase someone for some reason would have to delete a project?