You need to sign in to do that
Don't have an account?
yarram
System.LimitException: Too many SOQL queries: 101
Hi All,
i am getting too many SOQL queries error when my below class executed. i noticed that soql Queries inside the for loop but i don't have the idea HOW can I write the SOQL queries OUTSide the For LOOP as per my requirement. Below is my Util calss for my trigger. Please help me on this
public class OLIUtil{
public static void createProject(String OppID){
List<Attachment> aList;
List<Attachment> newAList=new List<Attachment>();
Project__c newProj;
List<Project_LI__c> PrjLIList=new List<Project_LI__c>();
List<OpportunityLineItem> OppProdList=[Select id,TotalPrice,Subtotal,Product2.Name,ProductCode,Product2.Id,Opportunity.Name,
from OpportunityLineItem where Opportunity.Id=:newOpp.Id];
// make sure we don't create duplicates project records
if ([select count() from Project__c where Project_Name__c = :NewOpp.Id] == 0)
{
newProj=new Project__c();
newProj.Project_Name__c=NewOpp.Id;
newProj.Project_Stage__c='In Progress';
insert newProj;
Project_LI__c newPrjLI;
for(OpportunityLineItem oppLI:OppProdList)
{
newPrjLI=new Project_LI__c();
newPrjLI.LI_Name__c=oppLI.Product2.Name;
newPrjLI.Project_Name__c=newProj.Id;
newPrjLI.LI_Number__c=oppLI.ProductCode;
newPrjLI.Quantity__c=oppLI.Quantity;
PrjLIList.add(newPrjLI);
}
insert PrjLIList;
List<Project_LI__c> addedLI=[select id,name,Project_Name__c,LI_Name__c from Project_LI__c where Project_Name__c=:newProj.Id];
for(Project_LI__c newAddedLI:addedLI){ // In this for Loop I have written 3 SOQL queries. How can I write these 3 SOQL s to Out side?
Project_LI__c ddd=[Select id,name,LI_Name__c,Project_Name__c from Project_LI__c where id=:newAddedLI.Id];
Product2 oppProd=[Select id,name from Product2 where name=:ddd.LI_Name__c];
aList=[Select Id,name,body,ParentId from Attachment where ParentId=:oppProd.Id];
if(aList.size()!=0){
for(Attachment aa:aList)
{
Attachment a=new Attachment();
a.name=aa.name;
a.body=aa.body;
a.ParentId=newAddedLI.id;
newAList.add(a);
}
}
}
insert newAList;
}
}
}
Thanks,
Yarram
i am getting too many SOQL queries error when my below class executed. i noticed that soql Queries inside the for loop but i don't have the idea HOW can I write the SOQL queries OUTSide the For LOOP as per my requirement. Below is my Util calss for my trigger. Please help me on this
public class OLIUtil{
public static void createProject(String OppID){
List<Attachment> aList;
List<Attachment> newAList=new List<Attachment>();
Project__c newProj;
List<Project_LI__c> PrjLIList=new List<Project_LI__c>();
List<OpportunityLineItem> OppProdList=[Select id,TotalPrice,Subtotal,Product2.Name,ProductCode,Product2.Id,Opportunity.Name,
from OpportunityLineItem where Opportunity.Id=:newOpp.Id];
// make sure we don't create duplicates project records
if ([select count() from Project__c where Project_Name__c = :NewOpp.Id] == 0)
{
newProj=new Project__c();
newProj.Project_Name__c=NewOpp.Id;
newProj.Project_Stage__c='In Progress';
insert newProj;
Project_LI__c newPrjLI;
for(OpportunityLineItem oppLI:OppProdList)
{
newPrjLI=new Project_LI__c();
newPrjLI.LI_Name__c=oppLI.Product2.Name;
newPrjLI.Project_Name__c=newProj.Id;
newPrjLI.LI_Number__c=oppLI.ProductCode;
newPrjLI.Quantity__c=oppLI.Quantity;
PrjLIList.add(newPrjLI);
}
insert PrjLIList;
List<Project_LI__c> addedLI=[select id,name,Project_Name__c,LI_Name__c from Project_LI__c where Project_Name__c=:newProj.Id];
for(Project_LI__c newAddedLI:addedLI){ // In this for Loop I have written 3 SOQL queries. How can I write these 3 SOQL s to Out side?
Project_LI__c ddd=[Select id,name,LI_Name__c,Project_Name__c from Project_LI__c where id=:newAddedLI.Id];
Product2 oppProd=[Select id,name from Product2 where name=:ddd.LI_Name__c];
aList=[Select Id,name,body,ParentId from Attachment where ParentId=:oppProd.Id];
if(aList.size()!=0){
for(Attachment aa:aList)
{
Attachment a=new Attachment();
a.name=aa.name;
a.body=aa.body;
a.ParentId=newAddedLI.id;
newAList.add(a);
}
}
}
insert newAList;
}
}
}
Thanks,
Yarram
Here latest code for opportuinty line item trigger.
Apex Trigger:
Thanks and Cheers,
Jigar
All Answers
Please try with below code, I have removed SOQL query from loop as well as add alternate for it.
Let me know if you have any question/issue.
Thanks and Cheers,
Jigar
---------> Jigar ----thank you very much. your solution is working me without limit exceed error. i need one more help from your side can you please help me the test class for code coverage of your solution.
John ---- thanks for your reply--your solution also working but attachments are not get inserted into the Project LI Records.
Jigar please help me on test class.
Thanks,
Yarram
Below is test class for the code.
Thanks and Cheers,
Jigar
thank you very much for giving the test class also, i will check this.
I have one more issue on my below trigger. Please help me on this.
Now i am getting the same Too many soql queries error on my below trigger when i give the quatity as 100 then below trigger will break down the OLIs as 100 records based on Quantity value and get insert 100 OLI records into the Opp.LineItem object.
HOW can I write the SOQL query OUTSide the For LOOP as per my requirement. Below is my trigger.
trigger QuantityBreakDownTrigger on OpportunityLineItem (before Insert) {
if(Trigger.isBefore){
if (trigger.isInsert) {
List<OpportunityLineItem> opplitemList=new List<OpportunityLineItem>();
OpportunityLineItem oli;
OpportunityLineItem newOLI1;
for(OpportunityLineItem NewOppLineItem:Trigger.new){// // In this for Loop I have written only one SOQL query(for Opportunity record).
opportunity newOpp=[Select id,name,stageName,Amount,CloseDate,Sample_Receipt__c,Study_Number__c, from Opportunity where Id=:NewOppLineItem.OpportunityId];////////////HOw can I write this 1 SOQL to Out side?
if(NewOppLineItem.Quantity>1){
for(Integer i=1; i<=NewOppLineItem.Quantity; i++){
OpportunityLineItem newOLI= new OpportunityLineItem ();
newOLI.ServiceDate=NewOppLineItem.ServiceDate;
newOLI.OpportunityId=newOpp.Id;
newOLI.PricebookEntryId=NewOppLineItem.PricebookEntryId;
newOLI.Quantity=1;
newOLI.UnitPrice=NewOppLineItem.UnitPrice;
newOLI.TotalPrice=NewOppLineItem.TotalPrice;
newOLI.Description=NewOppLineItem.Description;
newOLI.Discount=NewOppLineItem.Discount;
newOLI.Study_Number__c=newOpp.Study_Number__c;
newOLI.Sample_Receipt__c=newOpp.Sample_Receipt__c;
opplitemList.add(newOLI);
}
}
else{
NewOppLineItem.Study_Number__c=newOpp.Study_Number__c;
NewOppLineItem.Sample_Receipt__c=newOpp.Sample_Receipt__c;
}
}
insert opplitemList;
}
}
}
Thanks,
Yarram.
Below is the redesigned code for your trigger. You should use "After Insert" trigger for your requirement which is more secure the "Before Insert".
Apex Trigger:
Thanks and Cheers,
Jigar
Here latest code for opportuinty line item trigger.
Apex Trigger:
Thanks and Cheers,
Jigar
Thank you very much. trigger is working...
Thanks,
Yarram.
today i tried to implement the test class for OLIUtil (77% coverd) class as you mentioned the above test class.
i am not able to covers the Attachments code (line no : 63 to 72 if condition part) part and line no :50 to 57 part by usign your test class.
HOw can i achive/covers that if condition statements code?
please help me the code coverage part.
Thanks,
Yarram.
I am not able to find any other issue to cover those lines, based on my thinking it should cover these lines. but as you are saying that it is not covered need to put debug points and try to see that what is going on.
For debugging that code if you can send me your organization credential or if you have any other developer organization where you can create these apex class and test class, it would be great to have a look over there.
What would you prefere ?
Thanks and Cheers,
Jigar
thannks for your reply, i have cross checked my test class code and finally it was covered 100%.
thanks alot Jigar.
Thanks,
Yarram.
i am getting too many soql limt exceed error when my below class executed. Please help me on this. This is very urgent.
public class ProjectUtil{
public static void beUpdate(List<OpportunityLineItem > listOLIList){
Project_Assay__c PrjAssay;
for (OpportunityLineItem OppLI: listOLIList) {
PrjAssay=[select id,name,Assay_Name__c,Assay_Number__c,Invoice_Issue_Date__c,Invoice_Number__c,Lab_End_Date__c,Sales_Price__c,
Invoice_Amount_Payable__c,Lab_Start_Date__c,OppAssay__c,Project_Name__c,Quantity__c,Report_Date__c,Study_Number__c
from Project_Assay__c where OppAssay__c=:OppLI.OppAssay__c];
PrjAssay.Assay_Number__c=OppLI.ProductCode;
PrjAssay.Quantity__c=OppLI.Quantity;
PrjAssay.Study_Number__c=OppLI.Study_Number__c;
PrjAssay.OppAssay__c=OppLI.OppAssay__c;
PrjAssay.Sales_Price__c=OppLI.UnitPrice;
}
update PrjAssay;
}
}