function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

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

Need help bulkifying a statement

Got a trigger that should update number of cases for a set of accounts.
  
trigger CaseTrigger on Case (after insert, after update) 
{ 
Set<Account> accTemp=new Set<Account>(); 
List<Account> accTemp2 = new List<Account>(); 

// code to populate accTemp set with Accounts........ 
for(Account a : accTemp)
{ 
cases = [select id, accountid from Case WHERE accountid=:a.id AND isclosed!=true and createdDate >: System.Today()-365 LIMIT 90]; 
a.Nuber_Of_Open_Cases__c = cases.size(); 
accTemp2.add(a); 

} 
update(accTemp2); 

}


please help as i'm getting too many SOQL queries error when i do bulk upload.
Best Answer chosen by Visionary
JeffreyStevensJeffreyStevens
trigger CaseTrigger on Case (after insert, after update) {

// Get Account Ids from cases that fired the trigger
set<id> accountIds = new set<id>();
for(Case c :trigger.new) {
  accountIds.add(c.accountId);
}

// Count cases for each account & put in a map<AccountId,CaseCount>
list<aggregateRestult> casesAR = [SELECT accountId aid, count(id) count FROM Case WHERE accountId IN :accountIds ORDER By accountId GROUP BY accountId];
map<id,decimal> mAccountCaseCount = new map<id,decimal>();
for(AggregateResult ar :casesAR) {
  decimal count = decimal.valueOf(string.valueOf(ar.get('count')));
  mAccountCaseCount.put(string.valueof(AR.get('aid')), count);
}

// Get the accounts and update the number of open cases
list<Account> accountsToUpdate = [SELECT id,Number_Of_Open_Cases__c FROM Account WHERE id IN :accountIds];
for(Account a :accountsToUpdate) {
  a.Number_Of_OpenCases__c = mAccountCaseCount.get(a.id);
}

if(accountsToUpdate.size()>0) {
  update accountsToUpdate;
}

I think something like that will get you close. 

All Answers

JeffreyStevensJeffreyStevens
trigger CaseTrigger on Case (after insert, after update) {

// Get Account Ids from cases that fired the trigger
set<id> accountIds = new set<id>();
for(Case c :trigger.new) {
  accountIds.add(c.accountId);
}

// Count cases for each account & put in a map<AccountId,CaseCount>
list<aggregateRestult> casesAR = [SELECT accountId aid, count(id) count FROM Case WHERE accountId IN :accountIds ORDER By accountId GROUP BY accountId];
map<id,decimal> mAccountCaseCount = new map<id,decimal>();
for(AggregateResult ar :casesAR) {
  decimal count = decimal.valueOf(string.valueOf(ar.get('count')));
  mAccountCaseCount.put(string.valueof(AR.get('aid')), count);
}

// Get the accounts and update the number of open cases
list<Account> accountsToUpdate = [SELECT id,Number_Of_Open_Cases__c FROM Account WHERE id IN :accountIds];
for(Account a :accountsToUpdate) {
  a.Number_Of_OpenCases__c = mAccountCaseCount.get(a.id);
}

if(accountsToUpdate.size()>0) {
  update accountsToUpdate;
}

I think something like that will get you close. 
This was selected as the best answer
VisionaryVisionary
Worked like a charm with very minor tweaks (mainly aggregateResult - Group by has to be before Order by )... appreciate your help - new to triggers so this is helpful
JeffreyStevensJeffreyStevens
Great to hear.  Probably should mark the answer an best answer - so it shows as answered.