You need to sign in to do that
Don't have an account?
Thayalan Guhathashan 11
System.LimitException
Hi there,
The code works for normal scenario with single lead, but the code does not support bulkifying. When Multiple leads try to convert same time, I am getting a "System.LimitException: Too many SOQL queries" error.
trigger LeadIntegrationTrigger on Lead (after update, after insert)
{
List<String> LeadNames = new List<String>{};
Set<ID> Oppid =new Set<ID>();
List<Contact>lcon = New List<Contact>();
List<OpportunityLineItem> ListLine = New List<OpportunityLineItem>();
List<Opportunity> OppList = New List<Opportunity>();
if (Trigger.isInsert) {
for(Lead aLead: Trigger.new){
if((aLead.isconverted==false) && (aLead.Status == 'Qualified - Enrolled')) {
Database.LeadConvert lc = new database.LeadConvert();
lc.setLeadId(aLead.Id);
lc.convertedStatus = 'Qualified - Enrolled';
lc.setDoNotCreateOpportunity(false);
Database.LeadConvertResult lcr = Database.convertLead(lc);
System.assert(lcr.isSuccess());
//aLead.IsConverted=false;
}
}
}
if (Trigger.isUpdate ) {
List<OpportunityStudent__c> aNewStudents = new List<OpportunityStudent__c>();
Map<Id, Lead> aLeadToCheck = new Map<Id, Lead>();
for(Integer x = 0; x < Trigger.new.size(); x++)
{
if((Trigger.new[x].isconverted==false) && (Trigger.new[x].Status == 'Qualified - Enrolled'&&Trigger.new[x].Status!=Trigger.old[x].Status)) {
Database.LeadConvert lc = new database.LeadConvert();
lc.setLeadId(Trigger.new[x].Id);
lc.convertedStatus = 'Qualified - Enrolled';
lc.setDoNotCreateOpportunity(false);
Database.LeadConvertResult lcr = Database.convertLead(lc);
System.assert(lcr.isSuccess());
}
if (Trigger.new[x].IsConverted == true && Trigger.oldMap.get(Trigger.new[x].Id).IsConverted == false&& Trigger.new[x].ConvertedOpportunityId != null && Trigger.new[x].ConvertedContactId != null){
aLeadToCheck.put(Trigger.new[x].ConvertedOpportunityId, Trigger.new[x]);
}
}
//Auto add student on Opportuntiy
List<Opportunity> anOppList = [SELECT Id, AccountId, Account.Name,account.recordtype.name FROM Opportunity WHERE Id IN :aLeadToCheck.keySet()];
for (Opportunity anOpp: anOppList){
if (anOpp.AccountId != null){
Lead aLead = aLeadToCheck.get(anOpp.Id);
//update Opportunity Detail
anOpp.Training_Program__c=aLead.Product__c;
OppList.add(anOpp);
//
Contact con=[SELECT id,Avetmiss_At_School__c,Contact_Method__c,Avetmiss_Country_Of_Birth__c,
Avetmiss_Disability__c,Avetmiss_Disability_List__c,Gender__c,Avetmiss_Highest_School_Level__c,
Avetmiss_Indigenous_Status__c,Avetmiss_Labour_Force_Status__c,Avetmiss_Learner_Unique_Identifier__c,
Avetmiss_Main_Language__c,Avetmiss_Nationality__c,Avetmiss_Prior_Education__c,Prior_Education__c,Avetmiss_Prior_Education_List__c,
Avetmiss_School_Identifier__c,Avetmiss_Spoken_English_Proefficiency__c,Avetmiss_Year_Highest_School_Level__c,BirthDate
FROM contact WHERE id=:aLead.ConvertedContactId];
system.debug('Cloning');
//Clone data to personAccount
con.Avetmiss_At_School__c=aLead.Avetmiss_At_School__c;
con.Avetmiss_Year_Highest_School_Level__c = aLead.Avetmiss_Year_Highest_School_Level__c;
lcon.add(con);
// end clone data
//Add New PricebookEntry
if(aLead.Product__c!=null){
PricebookEntry Pricebook=[SELECT id FROM PricebookEntry WHERE Product2Id=:aLead.Product__c Limit 1];
}
OpportunityStudent__c aNewStudent = new OpportunityStudent__c();
aNewStudent.Contact__c = aLead.ConvertedContactId;
aNewStudent.Opportunity__c = aLead.ConvertedOpportunityId;
Oppid.add(aNewStudent.Opportunity__c);
aNewStudents.add(aNewStudent);
system.debug('@@@@@ CCCC');
}
else{
Oppid.add(anOpp.id);
}
}
//Prevent Duplicate Manage student [Person Acccount]
try{
List <OpportunityContactRole> Role=[SELECT id,opportunityid FROM OpportunityContactRole WHERE Opportunityid in : Oppid];
delete Role;
}
catch(System.QueryException e){}
insert aNewStudents;
//insert ListLine;
update OppList;
Update lcon;
List<OpportunityStudent__c> ListStudent=[SELECT id,Opportunity__c,Contact__c FROM OpportunityStudent__c WHERE Opportunity__c in:Oppid];
IF(ListStudent.size()>1){
delete ListStudent[1];
}
}
}
The code works for normal scenario with single lead, but the code does not support bulkifying. When Multiple leads try to convert same time, I am getting a "System.LimitException: Too many SOQL queries" error.
trigger LeadIntegrationTrigger on Lead (after update, after insert)
{
List<String> LeadNames = new List<String>{};
Set<ID> Oppid =new Set<ID>();
List<Contact>lcon = New List<Contact>();
List<OpportunityLineItem> ListLine = New List<OpportunityLineItem>();
List<Opportunity> OppList = New List<Opportunity>();
if (Trigger.isInsert) {
for(Lead aLead: Trigger.new){
if((aLead.isconverted==false) && (aLead.Status == 'Qualified - Enrolled')) {
Database.LeadConvert lc = new database.LeadConvert();
lc.setLeadId(aLead.Id);
lc.convertedStatus = 'Qualified - Enrolled';
lc.setDoNotCreateOpportunity(false);
Database.LeadConvertResult lcr = Database.convertLead(lc);
System.assert(lcr.isSuccess());
//aLead.IsConverted=false;
}
}
}
if (Trigger.isUpdate ) {
List<OpportunityStudent__c> aNewStudents = new List<OpportunityStudent__c>();
Map<Id, Lead> aLeadToCheck = new Map<Id, Lead>();
for(Integer x = 0; x < Trigger.new.size(); x++)
{
if((Trigger.new[x].isconverted==false) && (Trigger.new[x].Status == 'Qualified - Enrolled'&&Trigger.new[x].Status!=Trigger.old[x].Status)) {
Database.LeadConvert lc = new database.LeadConvert();
lc.setLeadId(Trigger.new[x].Id);
lc.convertedStatus = 'Qualified - Enrolled';
lc.setDoNotCreateOpportunity(false);
Database.LeadConvertResult lcr = Database.convertLead(lc);
System.assert(lcr.isSuccess());
}
if (Trigger.new[x].IsConverted == true && Trigger.oldMap.get(Trigger.new[x].Id).IsConverted == false&& Trigger.new[x].ConvertedOpportunityId != null && Trigger.new[x].ConvertedContactId != null){
aLeadToCheck.put(Trigger.new[x].ConvertedOpportunityId, Trigger.new[x]);
}
}
//Auto add student on Opportuntiy
List<Opportunity> anOppList = [SELECT Id, AccountId, Account.Name,account.recordtype.name FROM Opportunity WHERE Id IN :aLeadToCheck.keySet()];
for (Opportunity anOpp: anOppList){
if (anOpp.AccountId != null){
Lead aLead = aLeadToCheck.get(anOpp.Id);
//update Opportunity Detail
anOpp.Training_Program__c=aLead.Product__c;
OppList.add(anOpp);
//
Contact con=[SELECT id,Avetmiss_At_School__c,Contact_Method__c,Avetmiss_Country_Of_Birth__c,
Avetmiss_Disability__c,Avetmiss_Disability_List__c,Gender__c,Avetmiss_Highest_School_Level__c,
Avetmiss_Indigenous_Status__c,Avetmiss_Labour_Force_Status__c,Avetmiss_Learner_Unique_Identifier__c,
Avetmiss_Main_Language__c,Avetmiss_Nationality__c,Avetmiss_Prior_Education__c,Prior_Education__c,Avetmiss_Prior_Education_List__c,
Avetmiss_School_Identifier__c,Avetmiss_Spoken_English_Proefficiency__c,Avetmiss_Year_Highest_School_Level__c,BirthDate
FROM contact WHERE id=:aLead.ConvertedContactId];
system.debug('Cloning');
//Clone data to personAccount
con.Avetmiss_At_School__c=aLead.Avetmiss_At_School__c;
con.Avetmiss_Year_Highest_School_Level__c = aLead.Avetmiss_Year_Highest_School_Level__c;
lcon.add(con);
// end clone data
//Add New PricebookEntry
if(aLead.Product__c!=null){
PricebookEntry Pricebook=[SELECT id FROM PricebookEntry WHERE Product2Id=:aLead.Product__c Limit 1];
}
OpportunityStudent__c aNewStudent = new OpportunityStudent__c();
aNewStudent.Contact__c = aLead.ConvertedContactId;
aNewStudent.Opportunity__c = aLead.ConvertedOpportunityId;
Oppid.add(aNewStudent.Opportunity__c);
aNewStudents.add(aNewStudent);
system.debug('@@@@@ CCCC');
}
else{
Oppid.add(anOpp.id);
}
}
//Prevent Duplicate Manage student [Person Acccount]
try{
List <OpportunityContactRole> Role=[SELECT id,opportunityid FROM OpportunityContactRole WHERE Opportunityid in : Oppid];
delete Role;
}
catch(System.QueryException e){}
insert aNewStudents;
//insert ListLine;
update OppList;
Update lcon;
List<OpportunityStudent__c> ListStudent=[SELECT id,Opportunity__c,Contact__c FROM OpportunityStudent__c WHERE Opportunity__c in:Oppid];
IF(ListStudent.size()>1){
delete ListStudent[1];
}
}
}
for (Opportunity anOpp: anOppList){
if (anOpp.AccountId != null){
Lead aLead = aLeadToCheck.get(anOpp.Id);
//update Opportunity Detail
anOpp.Training_Program__c=aLead.Product__c;
OppList.add(anOpp);
//
Contact con=[SELECT id,Avetmiss_At_School__c,Contact_Method__c,Avetmiss_Country_Of_Birth__c,
Avetmiss_Disability__c,Avetmiss_Disability_List__c,Gender__c,Avetmiss_Highest_School_Level__c,
Avetmiss_Indigenous_Status__c,Avetmiss_Labour_Force_Status__c,Avetmiss_Learner_Unique_Identifier__c,
Avetmiss_Main_Language__c,Avetmiss_Nationality__c,Avetmiss_Prior_Education__c,Prior_Education__c,Avetmiss_Prior_Education_List__c,
Avetmiss_School_Identifier__c,Avetmiss_Spoken_English_Proefficiency__c,Avetmiss_Year_Highest_School_Level__c,BirthDate
FROM contact WHERE id=:aLead.ConvertedContactId];
system.debug('Cloning');
//Clone data to personAccount
con.Avetmiss_At_School__c=aLead.Avetmiss_At_School__c;
I moved the query out from the loop, still I am getting the System.LimitException: Too many SOQL queries: 101. through the process builder:
trigger LeadIntegrationTrigger on Lead (after update, after insert)
{
List<String> LeadNames = new List<String>{};
Set<ID> Oppid =new Set<ID>();
List<Contact>lcon = New List<Contact>();
List<OpportunityLineItem> ListLine = New List<OpportunityLineItem>();
List<Opportunity> OppList = New List<Opportunity>();
//After Save and status = Qualifield will auto convert
if (Trigger.isInsert) {
for(Lead aLead: Trigger.new){
//try{
if((aLead.isconverted==false) && (aLead.Status == 'Qualified - Enrolled')) {
Database.LeadConvert lc = new database.LeadConvert();
lc.setLeadId(aLead.Id);
lc.convertedStatus = 'Qualified - Enrolled';
lc.setDoNotCreateOpportunity(false);
Database.LeadConvertResult lcr = Database.convertLead(lc);
System.assert(lcr.isSuccess());
//aLead.IsConverted=false;
}
//}
// Validate If Phone Lead wrong
//catch(System.DmlException ee){
// aLead.addError('Phone number must contains 10 digit and start with 0 Or Gender is not input');
//}
}
}
if (Trigger.isUpdate ) {
List<OpportunityStudent__c> aNewStudents = new List<OpportunityStudent__c>();
Map<Id, Lead> aLeadToCheck = new Map<Id, Lead>();
Map<Id, Lead> aContactToCheck = new Map<Id, Lead>();
for(Integer x = 0; x < Trigger.new.size(); x++)
{
if((Trigger.new[x].isconverted==false) && (Trigger.new[x].Status == 'Qualified - Enrolled'&&Trigger.new[x].Status!=Trigger.old[x].Status)) {
Database.LeadConvert lc = new database.LeadConvert();
lc.setLeadId(Trigger.new[x].Id);
lc.convertedStatus = 'Qualified - Enrolled';
lc.setDoNotCreateOpportunity(false);
Database.LeadConvertResult lcr = Database.convertLead(lc);
System.assert(lcr.isSuccess());
}
if (Trigger.new[x].IsConverted == true && Trigger.oldMap.get(Trigger.new[x].Id).IsConverted == false&& Trigger.new[x].ConvertedOpportunityId != null && Trigger.new[x].ConvertedContactId != null){
aLeadToCheck.put(Trigger.new[x].ConvertedOpportunityId, Trigger.new[x]);
aContactToCheck.put(Trigger.new[x].ConvertedContactId, Trigger.new[x]);
}
}
//Auto add student on Opportuntiy
List<Opportunity> anOppList = [SELECT Id, AccountId, Account.Name,account.recordtype.name FROM Opportunity WHERE Id IN :aLeadToCheck.keySet()];
List<Contact> acontactList = [SELECT Id,Avetmiss_At_School__c,Contact_Method__c,Avetmiss_Country_Of_Birth__c,
Avetmiss_Disability__c,Avetmiss_Disability_List__c,Gender__c,Avetmiss_Highest_School_Level__c,
Avetmiss_Indigenous_Status__c,Avetmiss_Labour_Force_Status__c,Avetmiss_Learner_Unique_Identifier__c,
Avetmiss_Main_Language__c,Avetmiss_Nationality__c,Avetmiss_Prior_Education__c,Prior_Education__c,Avetmiss_Prior_Education_List__c,
Avetmiss_School_Identifier__c,Avetmiss_Spoken_English_Proefficiency__c,Avetmiss_Year_Highest_School_Level__c,BirthDate
FROM Contact WHERE Id IN :aContactToCheck.keySet()];
for(Contact aCon:acontactList) {
if (aCon.Id != NULL){
Lead myLead = aContactToCheck.get(aCon.Id);
aCon.Avetmiss_At_School__c=myLead.Avetmiss_At_School__c;
aCon.Birthdate=myLead.Birthdate__c;
// Making Mandatory 'Email' as the Contact Method
aCon.Contact_Method__c= 'Email';
aCon.Avetmiss_Country_Of_Birth__c=myLead.Avetmiss_Country_Of_Birth__c;
aCon.Avetmiss_Disability__c=myLead.Avetmiss_Disability__c;
aCon.Avetmiss_Disability_List__c=myLead.Avetmiss_Disability_List__c;
aCon.Gender__c=myLead.Gender__c;
aCon.Avetmiss_Highest_School_Level__c=myLead.Avetmiss_Highest_School_Level__c;
aCon.Avetmiss_Indigenous_Status__c = myLead.Avetmiss_Indigenous_Status__c;
aCon.Avetmiss_Labour_Force_Status__c= myLead.Avetmiss_Labour_Force_Status__c;
aCon.Avetmiss_Learner_Unique_Identifier__c = myLead.Avetmiss_Learner_Unique_Identifier__c;
aCon.Avetmiss_Main_Language__c = myLead.Avetmiss_Main_Language__c;
aCon.Avetmiss_Nationality__c = myLead.Avetmiss_Nationality__c;
aCon.Avetmiss_Prior_Education__c = myLead.Avetmiss_Prior_Education__c;
aCon.Town_of_Birth__c = myLead.Town_of_Birth__c;
aCon.State_of_Birth__c = myLead.State_of_Birth__c;
aCon.MobilePhone = myLead.MobilePhone;
aCon.Email = myLead.Email;
aCon.Login__c = myLead.Email;
aCon.Prior_Education__c = myLead.Prior_Education__c;
aCon.Disability__c= myLead.Disability__c;
aCon.Avetmiss_Prior_Education_List__c = myLead.Avetmiss_Prior_Education_List__c;
aCon.Avetmiss_School_Identifier__c = myLead.Avetmiss_School_Identifier__c;
aCon.Avetmiss_Spoken_English_Proefficiency__c = myLead.Avetmiss_Spoken_English_Proefficiency__c;
aCon.Avetmiss_Year_Highest_School_Level__c = myLead.Avetmiss_Year_Highest_School_Level__c;
}
lcon.add(aCon);
}
for (Opportunity anOpp: anOppList){
if (anOpp.AccountId != null){
Lead aLead = aLeadToCheck.get(anOpp.Id);
//update Opportunity Detail
anOpp.Course_Amount__c=aLead.Course_Amount__c;
anOpp.Course_Start_Date__c=aLead.Course_Start_Date__c;
anOpp.Course_End_Date__c=aLead.Course_End_Date__c;
anOpp.Training_Program__c=aLead.Product__c;
OppList.add(anOpp);
/*
//
Contact con=[SELECT id,Avetmiss_At_School__c,Contact_Method__c,Avetmiss_Country_Of_Birth__c,
Avetmiss_Disability__c,Avetmiss_Disability_List__c,Gender__c,Avetmiss_Highest_School_Level__c,
Avetmiss_Indigenous_Status__c,Avetmiss_Labour_Force_Status__c,Avetmiss_Learner_Unique_Identifier__c,
Avetmiss_Main_Language__c,Avetmiss_Nationality__c,Avetmiss_Prior_Education__c,Prior_Education__c,Avetmiss_Prior_Education_List__c,
Avetmiss_School_Identifier__c,Avetmiss_Spoken_English_Proefficiency__c,Avetmiss_Year_Highest_School_Level__c,BirthDate
FROM contact WHERE id=:aLead.ConvertedContactId];
system.debug('Cloning');
//Clone data to personAccount
con.Avetmiss_At_School__c=aLead.Avetmiss_At_School__c;
con.Birthdate=aLead.Birthdate__c;
// Making Mandatory 'Email' as the Contact Method
con.Contact_Method__c= 'Email';
con.Avetmiss_Country_Of_Birth__c=aLead.Avetmiss_Country_Of_Birth__c;
con.Avetmiss_Disability__c=aLead.Avetmiss_Disability__c;
con.Avetmiss_Disability_List__c=aLead.Avetmiss_Disability_List__c;
con.Gender__c=aLead.Gender__c;
con.Avetmiss_Highest_School_Level__c=aLead.Avetmiss_Highest_School_Level__c;
con.Avetmiss_Indigenous_Status__c = aLead.Avetmiss_Indigenous_Status__c;
con.Avetmiss_Labour_Force_Status__c= aLead.Avetmiss_Labour_Force_Status__c;
con.Avetmiss_Learner_Unique_Identifier__c = aLead.Avetmiss_Learner_Unique_Identifier__c;
con.Avetmiss_Main_Language__c = aLead.Avetmiss_Main_Language__c;
con.Avetmiss_Nationality__c = aLead.Avetmiss_Nationality__c;
con.Avetmiss_Prior_Education__c = aLead.Avetmiss_Prior_Education__c;
con.Town_of_Birth__c = aLead.Town_of_Birth__c;
con.State_of_Birth__c = aLead.State_of_Birth__c;
con.MobilePhone = aLead.MobilePhone;
con.Email = aLead.Email;
con.Login__c = aLead.Email;
con.Prior_Education__c = aLead.Prior_Education__c;
con.Disability__c= aLead.Disability__c;
con.Avetmiss_Prior_Education_List__c = aLead.Avetmiss_Prior_Education_List__c;
con.Avetmiss_School_Identifier__c = aLead.Avetmiss_School_Identifier__c;
con.Avetmiss_Spoken_English_Proefficiency__c = aLead.Avetmiss_Spoken_English_Proefficiency__c;
con.Avetmiss_Year_Highest_School_Level__c = aLead.Avetmiss_Year_Highest_School_Level__c;
lcon.add(con);
// end clone data
*/
//Add New PricebookEntry
if(aLead.Product__c!=null){
PricebookEntry Pricebook=[SELECT id FROM PricebookEntry WHERE Product2Id=:aLead.Product__c Limit 1];
/*PricebookEntry Pricebook = new PricebookEntry();
Pricebook.Pricebook2Id=Pbook.id;
Pricebook.Product2Id=aLead.Product__c;
Pricebook.UseStandardPrice=true;
insert Pricebook;
*/
//
//Add Product Line
/*
OpportunityLineItem OppLi = new OpportunityLineItem();
OppLi.OpportunityId=anOpp.id;
OppLi.PricebookEntryId=Pricebook.id;
OppLi.Quantity=1;
OppLi.UnitPrice=1000;
ListLine.add(OppLi);
*/
}
OpportunityStudent__c aNewStudent = new OpportunityStudent__c();
aNewStudent.Contact__c = aLead.ConvertedContactId;
aNewStudent.Opportunity__c = aLead.ConvertedOpportunityId;
Oppid.add(aNewStudent.Opportunity__c);
aNewStudents.add(aNewStudent);
system.debug('@@@@@ CCCC');
}
else{
Oppid.add(anOpp.id);
}
}
//Prevent Duplicate Manage student [Person Acccount]
try{
List <OpportunityContactRole> Role=[SELECT id,opportunityid FROM OpportunityContactRole WHERE Opportunityid in : Oppid];
delete Role;
}
catch(System.QueryException e){}
insert aNewStudents;
//insert ListLine;
update OppList;
update lcon;
List<OpportunityStudent__c> ListStudent=[SELECT id,Opportunity__c,Contact__c FROM OpportunityStudent__c WHERE Opportunity__c in:Oppid];
IF(ListStudent.size()>1){
delete ListStudent[1];
}
}
}