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
Geetha Chandran 1Geetha Chandran 1 

SOQL in for loop

Can someone please help me to get the SOQL (on opportunity object) from the for loop? I understand that we cannot have soql inside a for loop. Thanks a lot!!

      for (CampaignMember cm: cms) {
          oldcm= (CampaignMember)oldlist[i];
             //Update Opportunity Amount  
             o = [Select ID, Contact__c, CloseDate, CampaignID, OE_Program_of_Interest__c, Amount, Cvent_Balance_Due__c, Amount_Paid__c,       Description, StageName from Opportunity where Contact__c = :cm.ContactID and  Campaign.ID = :cm.CampaignId limit 1];       
             if(o.size() == 1){
                if(cm.Cost__c != oldcm.Cost__c){o[0].Amount = cm.Cost__c;}
                if(cm.Amount_Paid__c != oldcm.Amount_Paid__c){o[0].Amount_Paid__c = cm.Amount_Paid__c;}
                if(cm.Balance_Due__c != oldcm.Balance_Due__c){o[0].Cvent_Balance_Due__c = cm.Balance_Due__c;}                
                if(cm.Status != oldcm.Status && cm.Status == 'Registered'){o[0].StageName = 'Closed Won';}
                if(cm.Payment_Status__c != oldcm.Payment_Status__c && cm.Payment_Status__c == 'Paid in Full'){o[0].StageName = 'Closed Paid';} 
                if(cm.Status != oldcm.Status && (cm.Status == 'Cancelled' || cm.Status == 'Unattended' || cm.Status == 'Withdraw')){o[0].StageName = 'Closed Lost';}
                Updopps.add(o[0]);
             }   
      i++;      
      }
Best Answer chosen by Geetha Chandran 1
Amit Chaudhary 8Amit Chaudhary 8
Please check below post how to use map to avoid SOQL inside for loop
1) http://amitsalesforce.blogspot.com/2016/09/collection-in-salesforce-example-using.html

Please try below code. 
Set<ID> setContID = new Set<ID>();
Set<ID> setCampID = new Set<ID>();

for (CampaignMember cm: cms) 
{
	setContID.add(cm.ContactID);
	setCampID.add(cm.CampaignId);
	
}

List<Opportunity> listOpp = [ Select ID, Contact__c, CloseDate, CampaignID, OE_Program_of_Interest__c, Amount, Cvent_Balance_Due__c, Amount_Paid__c,       Description, StageName from Opportunity where Contact__c in :setContID and  CampaignID in :setCampID ];

Map<String,Opportunity> mapCampWiseOpp = new Map<String,Opportunity>();
for(Opportunity opp : listOpp)
{
	String key = opp.Contact__c+'-'+opp.CampaignID;
	mapCampWiseOpp.put(key,opp);
}


for (CampaignMember cm: cms) 
{
	oldcm= (CampaignMember)oldlist[i];
	 //Update Opportunity Amount  

	 String key = cm.ContactID +'-'+cm.CampaignId ;
	 
	If( mapCampWiseOpp.containsKey(key) )
	{
		o =  mapCampWiseOpp.get(key); 
		if(o != null )
		{
			if(cm.Cost__c != oldcm.Cost__c){o.Amount = cm.Cost__c;}
			if(cm.Amount_Paid__c != oldcm.Amount_Paid__c){o.Amount_Paid__c = cm.Amount_Paid__c;}
			if(cm.Balance_Due__c != oldcm.Balance_Due__c){o.Cvent_Balance_Due__c = cm.Balance_Due__c;}                
			if(cm.Status != oldcm.Status && cm.Status == 'Registered'){o.StageName = 'Closed Won';}
			if(cm.Payment_Status__c != oldcm.Payment_Status__c && cm.Payment_Status__c == 'Paid in Full'){o.StageName = 'Closed Paid';} 
			if(cm.Status != oldcm.Status && (cm.Status == 'Cancelled' || cm.Status == 'Unattended' || cm.Status == 'Withdraw')){o.StageName = 'Closed Lost';}
			
			Updopps.add(o);
		}   
	i++;      
}

Let us know if this will help you
 

All Answers

Amit Chaudhary 8Amit Chaudhary 8
Please check below post how to use map to avoid SOQL inside for loop
1) http://amitsalesforce.blogspot.com/2016/09/collection-in-salesforce-example-using.html

Please try below code. 
Set<ID> setContID = new Set<ID>();
Set<ID> setCampID = new Set<ID>();

for (CampaignMember cm: cms) 
{
	setContID.add(cm.ContactID);
	setCampID.add(cm.CampaignId);
	
}

List<Opportunity> listOpp = [ Select ID, Contact__c, CloseDate, CampaignID, OE_Program_of_Interest__c, Amount, Cvent_Balance_Due__c, Amount_Paid__c,       Description, StageName from Opportunity where Contact__c in :setContID and  CampaignID in :setCampID ];

Map<String,Opportunity> mapCampWiseOpp = new Map<String,Opportunity>();
for(Opportunity opp : listOpp)
{
	String key = opp.Contact__c+'-'+opp.CampaignID;
	mapCampWiseOpp.put(key,opp);
}


for (CampaignMember cm: cms) 
{
	oldcm= (CampaignMember)oldlist[i];
	 //Update Opportunity Amount  

	 String key = cm.ContactID +'-'+cm.CampaignId ;
	 
	If( mapCampWiseOpp.containsKey(key) )
	{
		o =  mapCampWiseOpp.get(key); 
		if(o != null )
		{
			if(cm.Cost__c != oldcm.Cost__c){o.Amount = cm.Cost__c;}
			if(cm.Amount_Paid__c != oldcm.Amount_Paid__c){o.Amount_Paid__c = cm.Amount_Paid__c;}
			if(cm.Balance_Due__c != oldcm.Balance_Due__c){o.Cvent_Balance_Due__c = cm.Balance_Due__c;}                
			if(cm.Status != oldcm.Status && cm.Status == 'Registered'){o.StageName = 'Closed Won';}
			if(cm.Payment_Status__c != oldcm.Payment_Status__c && cm.Payment_Status__c == 'Paid in Full'){o.StageName = 'Closed Paid';} 
			if(cm.Status != oldcm.Status && (cm.Status == 'Cancelled' || cm.Status == 'Unattended' || cm.Status == 'Withdraw')){o.StageName = 'Closed Lost';}
			
			Updopps.add(o);
		}   
	i++;      
}

Let us know if this will help you
 
This was selected as the best answer
Geetha Chandran 1Geetha Chandran 1
Thank you so much for your quick response! Truly appreciate it.

Quick question on the following three lines of code.

for (CampaignMember cm: cms)
      {
         setContID.add(cm.ContactID);
         setCampID.add(cm.CampaignId);    
      }
      List<Opportunity> listOpp = [ Select ID, Contact__c, CloseDate, CampaignID, OE_Program_of_Interest__c, Amount, Cvent_Balance_Due__c, Amount_Paid__c, Description, StageName from Opportunity where Contact__c in :setContID and CampaignID in :setCampID ];

How do we make sure that the above soql is retrieving opportunity record for the right combination of contactid and campaignid?

I am getting a complie error - Expression must be a list type: Opportunity from the following line:
if(o != null )
If I change o to an opportunity list, then I get compile error - Illegal assignment from Opportunity to List<Opportunity> from the following line:
o =  mapCampWiseOpp.get(key);

Greatly appreciate you help!!

Geetha
 
Geetha Chandran 1Geetha Chandran 1
Hello Amit,

Please disregard the question about the logic. I understand now how you are doing it. I now have to figure out the error I am getting.

Thanks again!
Geetha