You need to sign in to do that
Don't have an account?
Javagal
Soql Error : too many Queries 101
Hi , I am Getting an error to the old code since we made some changes to the other classes. below is the test Stack Trace. Any help will be really appriciated.
-------------------------------------------------------------------
Error Message System.LimitException: Too many SOQL queries: 101
Class.leadTriggerHandler.assignOwner:line 86, column 1
Class.leadTriggerHandler.onBeforeInsert:line 36, column 1
Trigger.LeadsTrigger: line 4, column 1
Class.leadTriggerHandler.assignOwner: line 86, column 1
Class.leadTriggerHandler.onBeforeInsert: line 36, column 1
Trigger.LeadsTrigger: line 4, column 1
-------------------------------------------------------------------
Error Message System.LimitException: Too many SOQL queries: 101
Class.leadTriggerHandler.assignOwner:line 86, column 1
Class.leadTriggerHandler.onBeforeInsert:line 36, column 1
Trigger.LeadsTrigger: line 4, column 1
public without sharing class leadTriggerHandler { public static void onBeforeInsert(list<Lead> newLeads){ list<Lead> leadsToProcess = new list<Lead>(); for(Lead l : newLeads){ /* Check if owner is already populated and not currentUser and zipcode is not null, Do_Not_Reassign__c is false only then drive the lead through custom assignment rule. */ if((l.OwnerId == null || l.OwnerId == userinfo.getUserId()) && l.Zip_Postal_Code__c != null && (l.Products__c != null || l.Facility_Type__c != null) && (l.Do_Not_Reassign__c == null || l.Do_Not_Reassign__c == false)){ leadsToProcess.add(l); }else if(l.Zip_Postal_Code__c == null || (l.Products__c == null && l.Facility_Type__c == null) ){ //no ZipCode match (or) no product and no facility type - assign the defaultleadOwner. l.OwnerId = SystemIdUtility.KCIUSUserId; } } if(!leadsToProcess.isEmpty()){ assignOwner(leadsToProcess); } } public static void onBeforeUpdate(list<Lead> newLeads, map<Id,Lead> oldValuesMap){ list<Lead> leadsToProcess = new list<Lead>(); for(Lead l : newLeads){ if(l.Zip_Postal_Code__c != null && (l.Zip_Postal_Code__c!=oldValuesMap.get(l.Id).Zip_Postal_Code__c || l.Facility_Type__c!=oldValuesMap.get(l.Id).Facility_Type__c)){ leadsToProcess.add(l); } } if(!leadsToProcess.isEmpty()){ assignOwner(leadsToProcess); } } private static void assignOwner(list<Lead> leadList){ try{ set<String> leadZipCodeSet = new set<String>(); list<Lead> clonedLeadsToInsert = new list<Lead>(); for(lead l : leadList){ if(string.valueOf(l.Zip_Postal_Code__c).length() > 5){ leadZipCodeSet.add(string.valueOf(l.Zip_Postal_Code__c).left(5)); }else{ leadZipCodeSet.add(string.valueOf(l.Zip_Postal_Code__c)); } } list<ZIP_Code__c> zipCodeList = [SELECT Name, Is_Active__c, KCI_Territory__c, KCI_Territory__r.Territory_Code__c, KCI_Territory__r.OwnerId FROM ZIP_Code__c where Name In : leadZipCodeSet and Is_Active__c = 'Y' ]; for(Lead l : leadList){ set<Id> leadOwnerIdSet = new set<Id>(); if(l.Products__c != null){ //Product is multiselect picklist and facility is single picklist. For each product and facility, get the JCA from the custom setting for(String product : String.ValueOf(l.Products__c).split(';')){ String JCA = ''; for(Lead_Assignment_Settings__c las : Lead_Assignment_Settings__c.getAll().values()){ if(product.equalsIgnoreCase(String.ValueOf(las.Product__c))){ if(l.Facility_Type__c != null && l.Facility_Type__c == String.ValueOf(las.Facility_Type__c)){ JCA = String.ValueOf(las.JCA__c); system.debug('@@@ CS Product & FT -- ' + Product + ' -- ' + l.Facility_Type__c); break; }else if(l.Facility_Type__c == null && las.Facility_Type__c == null){ JCA = String.ValueOf(las.JCA__c); system.debug('@@@ CS Product & FT -- ' + Product + ' -- ' + l.Facility_Type__c); } } } if(JCA != ''){ for(ZIP_Code__c zip : zipCodeList){ if( zip.Name == string.valueOf(l.Zip_Postal_Code__c).left(5) && zip.KCI_Territory__r.Territory_Code__c != null && zip.KCI_Territory__r.Territory_Code__c.startsWith(JCA)){ leadOwnerIdSet.add(zip.KCI_Territory__r.OwnerId); break; } } } system.debug('@@@ Leads Product & FT -- ' + Product + ' -- ' + l.Facility_Type__c); system.debug('@@@ JCA -- ' + JCA); system.debug('@@@ leadOwnerIdSet -- ' + leadOwnerIdSet); } } else if(l.Products__c == null && l.Facility_Type__c != null){ String JCA = ''; for(Lead_Assignment_Settings__c las : Lead_Assignment_Settings__c.getAll().values()){ if(las.Product__c == null){ if(l.Facility_Type__c != null && l.Facility_Type__c == String.ValueOf(las.Facility_Type__c)){ JCA = String.ValueOf(las.JCA__c); break; } } } if(JCA != ''){ for(ZIP_Code__c zip : zipCodeList){ if( zip.Name == string.valueOf(l.Zip_Postal_Code__c).left(5) && zip.KCI_Territory__r.Territory_Code__c != null && zip.KCI_Territory__r.Territory_Code__c.startsWith(JCA)){ leadOwnerIdSet.add(zip.KCI_Territory__r.OwnerId); break; } } } } if(!leadOwnerIdSet.isEmpty() && leadOwnerIdSet.size() == 1){ // only one owner idendified for the lead based on product, facility type and zipcode combination l.OwnerId = new list<Id>(leadOwnerIdSet)[0]; system.debug('>>>@@@ in lead owner Assignment - Owner = '+ l.OwnerId ); }else if(!leadOwnerIdSet.isEmpty() && leadOwnerIdSet.size() > 1){ //multiple owners identified, assign the first one to the current lead and create clones to assign to other owners. list<Id> leadOwnerIdList = new list<Id>(leadOwnerIdSet); for(integer i = 1; i < leadOwnerIdList.size(); i++){ Lead clonedLead = l.clone(false, true); clonedLead.OwnerId = leadOwnerIdList[i]; clonedLead.Do_Not_Reassign__c = true; clonedLeadsToInsert.add(clonedLead); } l.OwnerId = leadOwnerIdList[0]; system.debug('>>>@@@ in multi lead owner Assignment - Owner = '+ l.OwnerId ); }else{ //no owner matching the criteria assign owner to KCI US User l.OwnerId = SystemIdUtility.KCIUSUserId; } } if(!clonedLeadsToInsert.isEmpty()){ insert(clonedLeadsToInsert); } } catch(Exception e){ } } } ----------------------------- Trigger trigger LeadsTrigger on Lead (before insert, before update) { if(trigger.isInsert && trigger.isBefore){ leadTriggerHandler.onBeforeInsert(trigger.new); } if(trigger.isUpdate && trigger.isBefore){ leadTriggerHandler.onBeforeUpdate(trigger.new, trigger.oldMap); } } ---------------------------- /********************************************************************************************************************** * Class Name : LeadTriggerHandlerTest * Description : Testr Class for LeadTriggerHandler. **********************************************************************************************************************/ @isTest(SeeAllData=true) private class LeadTriggerHandlerTest { public static Map<String,User> testUsersMap; static testMethod void leadOwnerAssignmentTest() { Lead testLead = new Lead(); setupTestData(); test.startTest(); // Test null or blank zip code - owner should be default lead owner KCI US User testLead = createLead('ABThera','Acute',''); system.assertEquals(SystemIdUtility.KCIUSUserId, [select OwnerId from Lead where Id=:testLead.Id].OwnerId); // loop through all combinations as configured in Lead Assignment custom setting and create leads with the sample values for(Lead_Assignment_Settings__c las : Lead_Assignment_Settings__c.getAll().values()){ testLead = createLead(String.ValueOf(las.Product__c),String.ValueOf(las.Facility_Type__c),'63000'); if(Limits.getQueries() < Limits.getLimitQueries()-20){ if(testUsersMap.get(String.ValueOf(las.JCA__c)) != null){ system.assertEquals(testUsersMap.get(String.ValueOf(las.JCA__c)).Id, [select OwnerId from Lead where Id=:testLead.Id].OwnerId); } } } // update Lead and test Owner assignment. testLead.Products__c = 'Ulta'; testLead.Facility_Type__c = 'Acute'; update testLead; // Test multiple product testLead = createLead('Specialty Dressing;GRAFTJACKET','Acute','63000'); // Test non matching zipcode - then Lead Owner assigned should be KCI US User testLead = createLead('ABThera','Acute','99999'); system.assertEquals(SystemIdUtility.KCIUSUserId, [select OwnerId from Lead where Id=:testLead.Id].OwnerId); test.stopTest(); } private static Lead createLead(String product, String facilityType, String zipCode){ Lead newLead = new Lead(); newLead.FirstName = 'FirstName'; newLead.LastName = 'LastName'; newLead.Email = 'test@kci1.com'; newLead.Products__c = product; newLead.Facility_Type__c = facilityType; newLead.Zip_Postal_Code__c = zipCode; insert newLead; return newLead; } private static void setupTestData(){ //get test users testUsersMap = new Map<String,User>(); list<User> testTMVUsers=[Select Id,JCA__C, Employee_Id__c from User where IsActive=true and Employee_Id__c!=null and JCA__C='TMV' and Id !=:Userinfo.getUserId() limit 1]; if(testTMVUsers!= null && testTMVUsers.size()==1){ testUsersMap.put('TMV',testTMVUsers[0]); } list<User> testTSVUsers=[Select Id,JCA__C, Employee_Id__c from User where IsActive=true and Employee_Id__c!=null and JCA__C='TSV' and Id !=:Userinfo.getUserId() limit 1]; if(testTSVUsers!= null && testTSVUsers.size()==1){ testUsersMap.put('TSV',testTSVUsers[0]); } list<User> testSSMUsers=[Select Id,JCA__C, Employee_Id__c from User where IsActive=true and Employee_Id__c!=null and JCA__C='SSM' and Id !=:Userinfo.getUserId() limit 1]; if(testSSMUsers!= null && testSSMUsers.size()==1){ testUsersMap.put('SSM',testSSMUsers[0]); } //create Territory and Zip code for test user //create territory KCI_Territory__c testTerrTMV=new KCI_Territory__c(Name='terr-01', KCI_Legacy_Id__c='terr-001',Sales_Rep_Id__c=testUsersMap.get('TMV').employee_id__c, Territory_Code__c='TMV-test terr code',Territory_Start_Date__c=System.date.Today()-5, OwnerId=testUsersMap.get('TMV').Id); KCI_Territory__c testTerrTSV=new KCI_Territory__c(Name='terr-02', KCI_Legacy_Id__c='terr-002',Sales_Rep_Id__c=testUsersMap.get('TSV').employee_id__c, Territory_Code__c='TSV-test terr code',Territory_Start_Date__c=System.date.Today()-5, OwnerId=testUsersMap.get('TSV').Id); KCI_Territory__c testTerrSSM=new KCI_Territory__c(Name='terr-03', KCI_Legacy_Id__c='terr-003',Sales_Rep_Id__c=testUsersMap.get('SSM').employee_id__c, Territory_Code__c='SSM-test terr code',Territory_Start_Date__c=System.date.Today()-5, OwnerId=testUsersMap.get('SSM').Id); insert new list<KCI_Territory__c>{testTerrTMV,testTerrTSV,testTerrSSM}; //create zip codes ZIP_Code__c testZipTMV=new ZIP_Code__c(Name='63000', KCI_Legacy_Id__c='zip-001',KCI_Territory__c=testTerrTMV.Id,Zip_Start_Date__c=System.date.Today()-5); ZIP_Code__c testZipTSV=new ZIP_Code__c(Name='63000', KCI_Legacy_Id__c='zip-002',KCI_Territory__c=testTerrTSV.Id,Zip_Start_Date__c=System.date.Today()-5); ZIP_Code__c testZipSSM=new ZIP_Code__c(Name='63000', KCI_Legacy_Id__c='zip-003',KCI_Territory__c=testTerrSSM.Id,Zip_Start_Date__c=System.date.Today()-5); insert new list<ZIP_Code__c>{testZipTMV,testZipTSV,testZipSSM}; } }
Class.leadTriggerHandler.assignOwner: line 86, column 1
Class.leadTriggerHandler.onBeforeInsert: line 36, column 1
Trigger.LeadsTrigger: line 4, column 1
All Answers
Try the following code. Let me know if this solves your problem or not.
Regards,
Santosh Chitalkar
I think ,your below code is getting fired from your class which would be calling your trigger again and again.
Check your debug logs if this the case.
Solution:
Create a static member to avoid recursion and use it in your trigger as below.
Let us know if it helps you.
Also on test class its only passing if i login as default lead owner User. its not working with any other user. i am getting System.assertion failed in all cases. it hav not happend earlier . what cause this test class failed again.
Thank you if these were basic Questions my Apology !!
Javagal.
If you are getting SOQL 101 exception, generally it is because of recursive trigger.
So always check debug logs and look how many times your trigger is getting executed.