You need to sign in to do that
Don't have an account?
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;
}
}
All Answers
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.
Thanks a lot Brother!!!! Ur reply was just too good, this worked like a charm!!!!