+ Start a Discussion
shrey.tyagi88@tcs.comshrey.tyagi88@tcs.com 

Get soql outside for loop please help!!!

Hi All,

      I have a requirement here. It says that for a given case look for its cert, and if that cert has multiple moratotriums then create new TimeLine records on that case.

 

Case---Look Up---->>Cert<<-----Look Up----Moratoriums

 

I have a code written below , just need to get a soql outside for loop. Please help

 

    if(Trigger.isAfter && Trigger.isInsert){
      List<Timeline_Entries__c> TimeLineList = new List<Timeline_Entries__c> ();
      List<Moratorium__c> MoratoriumList = new List<Moratorium__c>();
      
      for(Case cs:Trigger.new){
        MoratoriumList=[Select Name,Cert_No__c,Start_Date__c,Stop_Date__c from Moratorium__c where Cert_No__c=:cs.Cert_Number__c]  ;
          
          for (integer x=0;x<MoratoriumList.size();x++){
              integer count=0;
              // To compare moratorium x with others in list. Comparison always goes fwd. i.e x is compared with x+1, x+2 so on never with x-1
              for(integer z=x+1;z<MoratoriumList.size();z++){
                      count++;
                      
                      if((MoratoriumList[x].Start_Date__c>MoratoriumList[z].Start_Date__c)&&(MoratoriumList[x].Stop_Date__c>MoratoriumList[z].Stop_Date__c)&&(MoratoriumList[z].Stop_Date__c>MoratoriumList[x].Start_Date__c)){
                      
                      // Perform some action using components of x,z, current case i.e cs.Field__c       
                      }
                      else if((MoratoriumList[x].Start_Date__c<MoratoriumList[z].Start_Date__c)&&(MoratoriumList[x].Stop_Date__c<MoratoriumList[z].Stop_Date__c)&&(MoratoriumList[x].Stop_Date__c>MoratoriumList[z].Start_Date__c)){
                       // Perform some action using components of x,z, current case i.e cs.Field__c    
                      }
                      else if((MoratoriumList[x].Start_Date__c==MoratoriumList[z].Start_Date__c)&&(MoratoriumList[x].Stop_Date__c==MoratoriumList[z].Stop_Date__c)){
                       // Perform some action using components of x,z, current case i.e cs.Field__c    
                      }
                      else if((MoratoriumList[x].Start_Date__c<MoratoriumList[z].Start_Date__c)&&(MoratoriumList[x].Stop_Date__c>MoratoriumList[z].Stop_Date__c)){
                       // Perform some action using components of x,z, current case i.e cs.Field__c    
                      }
                      else if((MoratoriumList[x].Start_Date__c>MoratoriumList[z].Start_Date__c)&&(MoratoriumList[x].Stop_Date__c<MoratoriumList[z].Stop_Date__c)){
                       // Perform some action using components of x,z, current case i.e cs.Field__c   
                      }
                     else if(count==MoratoriumList.size()-1){
                       // Perform some action using components of x,z, current case i.e cs.Field__c    
                          
                      }
              }
        
           
           }
      }
    if(TimeLineList.size()>0){
     insert TimeLineList;
    }   
  }

Best Answer chosen by Admin (Salesforce Developers) 
Saikishore Reddy AengareddySaikishore Reddy Aengareddy

 if(Trigger.isAfter && Trigger.isInsert){
      List<Timeline_Entries__c> TimeLineList = new List<Timeline_Entries__c> ();
      List<Moratorium__c> MoratoriumList = new List<Moratorium__c>();
	  
	  //Map of cert numbers to list of moratorium__c
	  Map<string,List<Moratorium__c> certMoratoriumMap = new Map<string,List<Moratorium__c>();
	  set<string> certNums = new set<string>();
	  for(case c : Trigger.new){
		if(c.Cert_Number__c <> null)
			certNums.add(c.Cert_Number__c);
	  }
	  
	  List<Moratorium__c> temp;
      for(Moratorium__c m: [Select Name,Cert_No__c,Start_Date__c,Stop_Date__c from Moratorium__c where Cert_No__c IN :certNums]){
		
		temp = new List<Moratorium__c>();
		
		if(!certMoratoriumMap.containsKey(m.cert_No__C)){
			temp.add(m);
			certMoratoriumMap.put(m.cert_No__c,temp);
		}else{
			temp = certMoratoriumMap.get(m.cert_No__c);
			temp.add(m);
			certMoratoriumMap.put(m.cert_No__c,temp);
		}
	  }
      for(Case cs:Trigger.new){
        //MoratoriumList=[Select Name,Cert_No__c,Start_Date__c,Stop_Date__c from Moratorium__c where Cert_No__c=:cs.Cert_Number__c]  ;
          
		  if(!certMoratoriumMap.containsKey(cs.cert_Number__c))
			continue;
		  else
			MoratoriumList = certMoratoriumMap.get(cs.cert_Number__c);
			
          for (integer x=0;x<MoratoriumList.size();x++){
              integer count=0;
              // To compare moratorium x with others in list. Comparison always goes fwd. i.e x is compared with x+1, x+2 so on never with x-1
              for(integer z=x+1;z<MoratoriumList.size();z++){
                      count++;
                      
                      if((MoratoriumList[x].Start_Date__c>MoratoriumList[z].Start_Date__c)&&(MoratoriumList[x].Stop_Date__c>MoratoriumList[z].Stop_Date__c)&&(MoratoriumList[z].Stop_Date__c>MoratoriumList[x].Start_Date__c)){
                      
                      // Perform some action using components of x,z, current case i.e cs.Field__c       
                      }
                      else if((MoratoriumList[x].Start_Date__c<MoratoriumList[z].Start_Date__c)&&(MoratoriumList[x].Stop_Date__c<MoratoriumList[z].Stop_Date__c)&&(MoratoriumList[x].Stop_Date__c>MoratoriumList[z].Start_Date__c)){
                       // Perform some action using components of x,z, current case i.e cs.Field__c    
                      }
                      else if((MoratoriumList[x].Start_Date__c==MoratoriumList[z].Start_Date__c)&&(MoratoriumList[x].Stop_Date__c==MoratoriumList[z].Stop_Date__c)){
                       // Perform some action using components of x,z, current case i.e cs.Field__c    
                      }
                      else if((MoratoriumList[x].Start_Date__c<MoratoriumList[z].Start_Date__c)&&(MoratoriumList[x].Stop_Date__c>MoratoriumList[z].Stop_Date__c)){
                       // Perform some action using components of x,z, current case i.e cs.Field__c    
                      }
                      else if((MoratoriumList[x].Start_Date__c>MoratoriumList[z].Start_Date__c)&&(MoratoriumList[x].Stop_Date__c<MoratoriumList[z].Stop_Date__c)){
                       // Perform some action using components of x,z, current case i.e cs.Field__c   
                      }
                     else if(count==MoratoriumList.size()-1){
                       // Perform some action using components of x,z, current case i.e cs.Field__c    
                          
                      }
              }
        
           
           }
      }
    if(TimeLineList.size()>0){
     insert TimeLineList;
    }   
  }

 

All Answers

Grazitti InteractiveGrazitti Interactive

Hi Shrey,

 

Please first create a set of ids of cert number__c  then query outside loop as following.

 

if(Trigger.isAfter && Trigger.isInsert){//no requirement to write this.
List<Timeline_Entries__c> TimeLineList = new List<Timeline_Entries__c> ();
List<Moratorium__c> MoratoriumList = new List<Moratorium__c>();
List<String> certNoSet = new List<String>();//Use List<Integer> if data type of field Cert_No__c is Number and so on for other data type.
for(Case cs:Trigger.new){
certNoSet.add(cs.Cert_Number__c);
}
MoratoriumList=[Select Name,Cert_No__c,Start_Date__c,Stop_Date__c from Moratorium__c where Cert_No__c IN :certNoSet] ;
for(Case cs:Trigger.new){
//MoratoriumList=[Select Name,Cert_No__c,Start_Date__c,Stop_Date__c from Moratorium__c where Cert_No__c=:cs.Cert_Number__c] ;

for (integer x=0;x<MoratoriumList.size();x++){
integer count=0;
// To compare moratorium x with others in list. Comparison always goes fwd. i.e x is compared with x+1, x+2 so on never with x-1
for(integer z=x+1;z<MoratoriumList.size();z++){
count++;

if((MoratoriumList[x].Start_Date__c>MoratoriumList[z].Start_Date__c)&&(MoratoriumList[x].Stop_Date__c>MoratoriumList[z].Stop_Date__c)&&(MoratoriumList[z].Stop_Date__c>MoratoriumList[x].Start_Date__c)){

// Perform some action using components of x,z, current case i.e cs.Field__c
}
else if((MoratoriumList[x].Start_Date__c<MoratoriumList[z].Start_Date__c)&&(MoratoriumList[x].Stop_Date__c<MoratoriumList[z].Stop_Date__c)&&(MoratoriumList[x].Stop_Date__c>MoratoriumList[z].Start_Date__c)){
// Perform some action using components of x,z, current case i.e cs.Field__c
}
else if((MoratoriumList[x].Start_Date__c==MoratoriumList[z].Start_Date__c)&&(MoratoriumList[x].Stop_Date__c==MoratoriumList[z].Stop_Date__c)){
// Perform some action using components of x,z, current case i.e cs.Field__c
}
else if((MoratoriumList[x].Start_Date__c<MoratoriumList[z].Start_Date__c)&&(MoratoriumList[x].Stop_Date__c>MoratoriumList[z].Stop_Date__c)){
// Perform some action using components of x,z, current case i.e cs.Field__c
}
else if((MoratoriumList[x].Start_Date__c>MoratoriumList[z].Start_Date__c)&&(MoratoriumList[x].Stop_Date__c<MoratoriumList[z].Stop_Date__c)){
// Perform some action using components of x,z, current case i.e cs.Field__c
}
else if(count==MoratoriumList.size()-1){
// Perform some action using components of x,z, current case i.e cs.Field__c

}
}


}
}
if(TimeLineList.size()>0){
insert TimeLineList;
}
}

 

If my post helped you please hit Kudos.
If it solved your issue, thanks for marking it as Solution.

Saikishore Reddy AengareddySaikishore Reddy Aengareddy

 if(Trigger.isAfter && Trigger.isInsert){
      List<Timeline_Entries__c> TimeLineList = new List<Timeline_Entries__c> ();
      List<Moratorium__c> MoratoriumList = new List<Moratorium__c>();
	  
	  //Map of cert numbers to list of moratorium__c
	  Map<string,List<Moratorium__c> certMoratoriumMap = new Map<string,List<Moratorium__c>();
	  set<string> certNums = new set<string>();
	  for(case c : Trigger.new){
		if(c.Cert_Number__c <> null)
			certNums.add(c.Cert_Number__c);
	  }
	  
	  List<Moratorium__c> temp;
      for(Moratorium__c m: [Select Name,Cert_No__c,Start_Date__c,Stop_Date__c from Moratorium__c where Cert_No__c IN :certNums]){
		
		temp = new List<Moratorium__c>();
		
		if(!certMoratoriumMap.containsKey(m.cert_No__C)){
			temp.add(m);
			certMoratoriumMap.put(m.cert_No__c,temp);
		}else{
			temp = certMoratoriumMap.get(m.cert_No__c);
			temp.add(m);
			certMoratoriumMap.put(m.cert_No__c,temp);
		}
	  }
      for(Case cs:Trigger.new){
        //MoratoriumList=[Select Name,Cert_No__c,Start_Date__c,Stop_Date__c from Moratorium__c where Cert_No__c=:cs.Cert_Number__c]  ;
          
		  if(!certMoratoriumMap.containsKey(cs.cert_Number__c))
			continue;
		  else
			MoratoriumList = certMoratoriumMap.get(cs.cert_Number__c);
			
          for (integer x=0;x<MoratoriumList.size();x++){
              integer count=0;
              // To compare moratorium x with others in list. Comparison always goes fwd. i.e x is compared with x+1, x+2 so on never with x-1
              for(integer z=x+1;z<MoratoriumList.size();z++){
                      count++;
                      
                      if((MoratoriumList[x].Start_Date__c>MoratoriumList[z].Start_Date__c)&&(MoratoriumList[x].Stop_Date__c>MoratoriumList[z].Stop_Date__c)&&(MoratoriumList[z].Stop_Date__c>MoratoriumList[x].Start_Date__c)){
                      
                      // Perform some action using components of x,z, current case i.e cs.Field__c       
                      }
                      else if((MoratoriumList[x].Start_Date__c<MoratoriumList[z].Start_Date__c)&&(MoratoriumList[x].Stop_Date__c<MoratoriumList[z].Stop_Date__c)&&(MoratoriumList[x].Stop_Date__c>MoratoriumList[z].Start_Date__c)){
                       // Perform some action using components of x,z, current case i.e cs.Field__c    
                      }
                      else if((MoratoriumList[x].Start_Date__c==MoratoriumList[z].Start_Date__c)&&(MoratoriumList[x].Stop_Date__c==MoratoriumList[z].Stop_Date__c)){
                       // Perform some action using components of x,z, current case i.e cs.Field__c    
                      }
                      else if((MoratoriumList[x].Start_Date__c<MoratoriumList[z].Start_Date__c)&&(MoratoriumList[x].Stop_Date__c>MoratoriumList[z].Stop_Date__c)){
                       // Perform some action using components of x,z, current case i.e cs.Field__c    
                      }
                      else if((MoratoriumList[x].Start_Date__c>MoratoriumList[z].Start_Date__c)&&(MoratoriumList[x].Stop_Date__c<MoratoriumList[z].Stop_Date__c)){
                       // Perform some action using components of x,z, current case i.e cs.Field__c   
                      }
                     else if(count==MoratoriumList.size()-1){
                       // Perform some action using components of x,z, current case i.e cs.Field__c    
                          
                      }
              }
        
           
           }
      }
    if(TimeLineList.size()>0){
     insert TimeLineList;
    }   
  }

 

This was selected as the best answer
shrey.tyagi88@tcs.comshrey.tyagi88@tcs.com

Thanks a lot Brother!!!! Ur reply was just too good, this worked like a charm!!!!