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
Anthony Zirilli 10Anthony Zirilli 10 

Help Bulkifying Code. Problem with query inside loop.

Hi there, 

I desperatly need help tweaking this code. It works to pull in the active opportunity on the account level, but does not work when doing mass updates/uploads. I have the understading that the querry needs to be moved outside the loop but don't know how to do that exactly. 
trigger act_opp on Account (before insert, before update) {

map<Id, Account> acctmap = new map<Id, Account>();
for(Account a : trigger.new){
    acctmap.putAll([SELECT Id, (SELECT Id, Name FROM Opportunities WHERE (RecordType.Name = 'Membership Renewal' AND Active_Membership__c = 'Active') OR (RecordType.Name = 'Membership Prospect' AND Web_Expire_Date__c != null) LIMIT 1) FROM Account WHERE Id =: a.Id]);}
    
for(Account a : trigger.new){
    if(acctmap.get(a.Id).Opportunities.size() != 0){
        Opportunity opp = acctmap.get(a.Id).Opportunities[0];
        a.Active_Membership_Opportunity__c = opp.Id;} 
    else {a.Active_Membership_Opportunity__c = null;}
}
}

Any help would be greatly appreciated. Thank you!
Best Answer chosen by Anthony Zirilli 10
Abhishek BansalAbhishek Bansal
Hi Anthony,

There was a field missing in query.
 I have updated the code. Please replace your code from below code :
  
trigger act_opp on Account (before insert, before update) {
	List<Opportunity> oppList = new List<Opportunity>([SELECT Id, Name,AccountId FROM Opportunity WHERE AccountID in : trigger.new 
AND ((RecordType.Name = 'Membership Renewal' AND Active_Membership__c = 'Active') OR (RecordType.Name = 'Membership Prospect' AND Web_Expire_Date__c != null))]);
	Map<Id,Opportunity> accIdwithOpp = new Map<Id,Opportunity>();
	
	for(Opportunity opp : oppList){
		if(!accIdwithOpp.containsKey(opp.AccountId)){
			accIdwithOpp.put(opp.AccountId,opp);
		}
	}
	
	for(Account acc : trigger.new){
		if(accIdwithOpp.containsKey(acc.id)){
			acc.Active_Membership_Opportunity__c = accIdwithOpp.get(acc.id).id;
		}
		else{
			acc.Active_Membership_Opportunity__c = null;
		}
	}
}

Let me know if there is any issue.

Regards,
Abhishek

All Answers

Abhishek BansalAbhishek Bansal
Hi Anthony,

As per your requirement i have modified your trigger code.
Please update your trigger code from below code :
  
trigger act_opp on Account (before insert, before update) {
	List<Opportunity> oppList = new List<Opportunity>([SELECT Id, Name FROM Opportunitity WHERE AccountID in : trigger.new AND (RecordType.Name = 'Membership Renewal' AND Active_Membership__c = 'Active') OR (RecordType.Name = 'Membership Prospect' AND Web_Expire_Date__c != null)]);
	Map<Id,Opportunity> accIdwithOpp = new Map<Id,Opportunity>();
	
	for(Opportunity opp : oppList){
		if(!accIdwithOpp.containsKey(opp.AccountId)){
			accIdwithOpp.put(AccountId,opp);
		}
	}
	
	for(Account acc : trigger.new){
		if(accIdwithOpp.containsKey(acc.id)){
			acc.Active_Membership_Opportunity__c = accIdwithOpp.get(acc.id).id;
		}
		else{
			acc.Active_Membership_Opportunity__c = null;
		}
	}
}

Please let me know if you need more help on this.

Thanks,
Abhishek
Anthony Zirilli 10Anthony Zirilli 10
I'm getting this error: "Error: Compile Error: expecting right square bracket, found 'OR' at line 2 column 204"

Thank you for your help.
Abhishek BansalAbhishek Bansal
Hi,

Please change line no. 2 from below line :  
List<Opportunity> oppList = new List<Opportunity>([SELECT Id, Name FROM Opportunitity WHERE AccountID in : trigger.new AND ((RecordType.Name = 'Membership Renewal' AND Active_Membership__c = 'Active') OR (RecordType.Name = 'Membership Prospect' AND Web_Expire_Date__c != null))]);

Let me know if there is any issue.

Thanks,
Abhishek.
William TranWilliam Tran
There are lots of error in the code:

Opportunity spelled wrong, AND is missing proper parentheses, AccountId is referenced with opp.

I believe this fixed them all.

thx
  
trigger act_opp on Account (before insert, before update) {
	List<Opportunity> oppList = new List<Opportunity>([SELECT Id, Name FROM Opportunity WHERE AccountID in : trigger.new 
AND ((RecordType.Name = 'Membership Renewal' AND Active_Membership__c = 'Active') OR (RecordType.Name = 'Membership Prospect' AND Web_Expire_Date__c != null))]);
	Map<Id,Opportunity> accIdwithOpp = new Map<Id,Opportunity>();
	
	for(Opportunity opp : oppList){
		if(!accIdwithOpp.containsKey(opp.AccountId)){
			accIdwithOpp.put(opp.AccountId,opp);
		}
	}
	
	for(Account acc : trigger.new){
		if(accIdwithOpp.containsKey(acc.id)){
			acc.Active_Membership_Opportunity__c = accIdwithOpp.get(acc.id).id;
		}
		else{
			acc.Active_Membership_Opportunity__c = null;
		}
	}
}

As a common practice, if your question is answered, please choose 1 best answer. 
But you can give every answer a thumb up if that answer is helpful to you. 

Thanks
Anthony Zirilli 10Anthony Zirilli 10
Hi William, 

I put the code in and went to update the account record and recieved this error: Apex trigger act_opp caused an unexpected exception, contact your administrator: act_opp: execution of BeforeUpdate caused by: System.SObjectException: SObject row was retrieved via SOQL without querying the requested field: Opportunity.AccountId: Trigger.act_opp: line 7, column 1

Any thoughts?
Abhishek BansalAbhishek Bansal
Hi Anthony,

There was a field missing in query.
 I have updated the code. Please replace your code from below code :
  
trigger act_opp on Account (before insert, before update) {
	List<Opportunity> oppList = new List<Opportunity>([SELECT Id, Name,AccountId FROM Opportunity WHERE AccountID in : trigger.new 
AND ((RecordType.Name = 'Membership Renewal' AND Active_Membership__c = 'Active') OR (RecordType.Name = 'Membership Prospect' AND Web_Expire_Date__c != null))]);
	Map<Id,Opportunity> accIdwithOpp = new Map<Id,Opportunity>();
	
	for(Opportunity opp : oppList){
		if(!accIdwithOpp.containsKey(opp.AccountId)){
			accIdwithOpp.put(opp.AccountId,opp);
		}
	}
	
	for(Account acc : trigger.new){
		if(accIdwithOpp.containsKey(acc.id)){
			acc.Active_Membership_Opportunity__c = accIdwithOpp.get(acc.id).id;
		}
		else{
			acc.Active_Membership_Opportunity__c = null;
		}
	}
}

Let me know if there is any issue.

Regards,
Abhishek
This was selected as the best answer