Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
P KumarP Kumar 

System.LimitException: Too many SOQL queries: 101

Hi,

I want to update leads based on criteria once after the contact is insereted. This trigger is not working for my bulk inserts or updates. Am getting error 'System.LimitException: Too many SOQL queries: 101'. Can anyone help me out ?
There is other trigger on leads which will tag the contact details on lead object fields when lead is inserted or updated.
I suppose to deliver this by this friday, Very Urgent.


Trigger updateLeads1 on Contact (after insert, after update) {

Id rtID= [select Id,name from RecordType where name ='APAC - Open' limit 1].Id;

        for(Contact myContact : Trigger.new){
            if(myContact.email != null && myContact.FirstName != null && myContact.LastName != null) {
              for(Lead checkLead : [Select Id, FirstName, LastName, Email, Status, Ready_to_Convert__c, Country_code__c FROM Lead where RecordTypeId = :rtId AND Email = :myContact.Email AND FirstName = :myContact.FirstName AND LastName = :myContact.LastName limit 1]){
                    if(myContact.Turnover__c > 0.00){
                    updatelead(checkLead.id);
                }
             if((myContact.Turnover__c <= 0.00 || myContact.Turnover__c== null) && (checkLead.Country_code__c != 'India' || checkLead.Country_code__c != 'China' || checkLead.Country_code__c != 'Taiwan' || checkLead.Country_code__c != 'Hong Kong')){
                    updatelead1(checkLead.id);
                    }
                }
          }
    }
    
    public static void updatelead(Id leadId) {
    System.debug('Lead ID : '+ leadId);
    lead ld = [SELECT id FROM lead WHERE ID = :leadId];
    system.debug('Lead ID in Query :'+ld);
    ld.Status = 'Order Placed';
    ld.Ready_to_Convert__c = TRUE;
    try {
        update ld;             
        } catch(DMLException e) {   
        System.debug('The following exception has occurred: ' + e.getMessage()); 
        }
}

    public static void updatelead1(Id leadId) {
    System.debug('Lead ID : '+ leadId);
    lead ld = [SELECT id FROM lead WHERE ID = :leadId];
    system.debug('Lead ID in Query :'+ld);
    ld.Status = 'Account created';
    try {
        update ld;             
        } catch(DMLException e) {   
        System.debug('The following exception has occurred: ' + e.getMessage()); 
        }
    }
}

Regards,
Pranav

Best Answer chosen by P Kumar
Amit Chaudhary 8Amit Chaudhary 8
Hi  Pranava ,

Please try below code. 
Trigger updateLeads1 on Contact (after insert, after update) 
{
		Id rtID= [select Id,name from RecordType where name ='APAC - Open' limit 1].Id;
		Set<String> setEmailId = new Set<String>();
		Set<String> setFirstName = new Set<String>();
		Set<String> setLastName = new Set<String>();

        for(Contact myContact : Trigger.new)
		{
			if(myContact.Email != null && myContact.FirstName != null && myContact.LastName != null)
			{
				setEmailId.add(myContact.Email);
				setFirstName.add(myContact.FirstName);
				setLastName.add(myContact.LastName);
			}
		}
		
		List<lead> listLead = [ Select Id, FirstName, LastName, Email, Status, Ready_to_Convert__c, Country_code__c FROM Lead where RecordTypeId = :rtId AND Email = :setEmailId AND FirstName = :setFirstName AND LastName = :setLastName ] ;
		
		List<Lead> listUpdatelead = new List<Lead>();
		Set<String> setLeadID = new Set<String>(); // Added to check duplicate Lead
		
        for(Contact myContact : Trigger.new)
		{
            if(myContact.email != null && myContact.FirstName != null && myContact.LastName != null) 
			{
              for( Lead checkLead : listLead )
			  {
				if( checkLead.Email == myContact.email &&  checkLead.FirstName == myContact.FirstName && checkLead.LastName == myContact.LastName )
				{
                    if(myContact.Turnover__c > 0.00)
					{
						if(setLeadID.contains(checkLead.id) == false)
						{
							checkLead.Status = 'Order Placed';
							checkLead.Ready_to_Convert__c = TRUE;
							listUpdatelead.add(checkLead);
							setLeadID.add(checkLead.id);
						}	
					}
					if((myContact.Turnover__c <= 0.00 || myContact.Turnover__c== null) && (checkLead.Country_code__c != 'India' || checkLead.Country_code__c != 'China' || checkLead.Country_code__c != 'Taiwan' || checkLead.Country_code__c != 'Hong Kong'))
					{
						if(setLeadID.contains(checkLead.id) == false)
						{
							checkLead.Status = 'Account created';
							listUpdatelead.add(checkLead);
							setLeadID.add(checkLead.id);
						}	
                    }
				}	
              }
          }
        }
		
		if( listUpdatelead.size() > 0 )
		{
			try 
			{
				update listUpdatelead;             
			} 
			catch(DMLException e) 
			{   
				System.debug('The following exception has occurred: ' + e.getMessage()); 
			}
		}
}
Now your Contact trigger is perfect. If you still get any error then please check your lead trigger as well.
Please test this trigger by UI first and then by data loader.

NOTE: This code has not been tested and may contain typographical or logical errors

Please mark this as solution by selecting it as best answer if this solves your problem, So that if anyone has this issue this post can help

Thanks
Amit Chaudhary

All Answers

praveen murugesanpraveen murugesan
Hi Pranav,

Try to bring out the query outside the for loop. You can use map to store query value  and use map.values().

Thanks

Praveen Murugesan
Navneet kNavneet k
Remove this: for(Lead checkLead : [Select Id, FirstName, LastName, Email, Status, Ready_to_Convert__c, Country_code__c FROM Lead where RecordTypeId = :rtId AND Email = :myContact.Email AND FirstName = :myContact.FirstName AND LastName = :myContact.LastName limit 1])

Query within for loop is causing System.LimitException: Too many SOQL queries: 101

Use Set and map!
ManojjenaManojjena
Hi Pranav,

Please try with below code it will help .
  
Trigger updateLeads1 on Contact (after insert, after update) {
    List<Lead> lstLeadToUpdate=new List<Lead>();
	Id rtID= Lead.sObjectType.getDescribe().getRecordTypeInfosByName().get('APAC - Open').getRecordTypeId();
    for(Contact myContact : Trigger.new){
            if(myContact.email != null && myContact.FirstName != null && myContact.LastName != null) {
              for(Lead checkLead : [Select Id, FirstName, LastName, Email, Status, Ready_to_Convert__c, Country_code__c FROM Lead where RecordTypeId = :rtId AND Email = :myContact.Email AND FirstName = :myContact.FirstName AND LastName = :myContact.LastName limit 1]){
                    if(myContact.Turnover__c > 0.00){
                    updatelead(.id);
                }
             if((myContact.Turnover__c <= 0.00 || myContact.Turnover__c== null) && (checkLead.Country_code__c != 'India' || checkLead.Country_code__c != 'China' || checkLead.Country_code__c != 'Taiwan' || checkLead.Country_code__c != 'Hong Kong')){
                    updatelead1(checkLead.id);
                    }
                }
          }
    }
    try {
		update lstLeadToUpdate;             
	} catch(DMLException e) {   
		System.debug('The following exception has occurred: ' + e.getMessage()); 
   }
    public static void updatelead(Id leadId) {
		 Lead led=new Lead(id=leadId,Status = 'Order Placed',Ready_to_Convert__c = TRUE);
		 lstLeadToUpdate.add(led);
	 }

    public static void updatelead1(Id leadId) {
	 Lead led=new Lead(id=leadId,Status = 'Account created');
	  lstLeadToUpdate.add(led);
    }
}

Still if you are getting the same error ,Please let me know .

Thnaks 
Manoj
P KumarP Kumar

Hi Manoj,

i tried with your code. still am getting error, Please find the screenshot when i upload data through data loader.
am not into development. can anyone help me out.?
Error Screen Shot after Using Manoj Reply Code.

Regards,
Pranav
P KumarP Kumar

Hi Naveen,

Can you please modify the code with map and set that should update all the leads according to the contact details matching.

Waiting for you reply.

Regards,
Pranav
Amit Chaudhary 8Amit Chaudhary 8
Hi Pranav,
Please try with below code it will help . 
Trigger updateLeads1 on Contact (after insert, after update) 
{
		Id rtID= [select Id,name from RecordType where name ='APAC - Open' limit 1].Id;
		Set<String> setEmailId = new Set<String>();
		Set<String> setFirstName = new Set<String>();
		Set<String> setLastName = new Set<String>();

        for(Contact myContact : Trigger.new)
		{
			if(myContact.Email != null && myContact.FirstName != null && myContact.LastName != null)
			{
				setEmailId.add(myContact.Email);
				setFirstName.add(myContact.FirstName);
				setLastName.add(myContact.LastName);
			}
		}
		
		List<lead> listLead = [ Select Id, FirstName, LastName, Email, Status, Ready_to_Convert__c, Country_code__c FROM Lead where RecordTypeId = :rtId AND Email = :setEmailId AND FirstName = :setFirstName AND LastName = :setLastName ] ;
		
		List<Lead> listUpdatelead = new List<Lead>();
		
        for(Contact myContact : Trigger.new)
		{
            if(myContact.email != null && myContact.FirstName != null && myContact.LastName != null) 
			{
              for( Lead checkLead : listLead )
			  {
				if( checkLead.Email == myContact.email &&  checkLead.FirstName == myContact.FirstName && checkLead.LastName == myContact.LastName )
				{
                    if(myContact.Turnover__c > 0.00)
					{
						checkLead.Status = 'Order Placed';
						checkLead.Ready_to_Convert__c = TRUE;
						listUpdatelead.add(checkLead);
					}
					if((myContact.Turnover__c <= 0.00 || myContact.Turnover__c== null) && (checkLead.Country_code__c != 'India' || checkLead.Country_code__c != 'China' || checkLead.Country_code__c != 'Taiwan' || checkLead.Country_code__c != 'Hong Kong'))
					{
						checkLead.Status = 'Account created';
						listUpdatelead.add(checkLead);
                    }
				}	
              }
          }
        }
		
		if( listUpdatelead.size() > 0 )
		{
			try 
			{
				update listUpdatelead;             
			} 
			catch(DMLException e) 
			{   
				System.debug('The following exception has occurred: ' + e.getMessage()); 
			}
		}
}

NOTE: This code has not been tested and may contain typographical or logical errors

Please mark this as solution by selecting it as best answer if this solves your problem, So that if anyone has this issue this post can help

Thanks
Amit Chaudhary
praveen murugesanpraveen murugesan
Hi Pranava,

If you can't implement above code means you can insert 100 records at a time.

Thanks.

--Praveen Murugesan.
P KumarP Kumar

Hi Amith,

I tried with your code. but am getting following kind of errors.
I have inserted 800 contacts and 800 errors.
I think this code will not update the lead status as well.

Can you let me know why this is happening?

Amiths Code error when Contacts inserted with Data Loader

Regards,
Pranav
ManojjenaManojjena
Hi Pravnav,

I have already removed three Query and updated the code .  As you are comparing contact with all lead  as per condition .So it will be good if you will explain your requirment for which we can help you .

Thanks 
Manoj
 
P KumarP Kumar

Hi Manoj,

When contacts are inserted my leads should get tagged if same details are matching with the leads.

if email, firstname, lastname are matching then lead should update lead fields 'ready to convert' and status on contact turnover.

in the same way i have to write the trigger on lead to compare the contact details like email, firstname, lastname are matching then contact id should be updated on lead custom field.
On weekly basis we upload around 2000+ leads. so i need trigger which works for bulk inserts or updates.
Is it clear now? If yes please help me out on writing these triggers.

Regards,
Pranav
P KumarP Kumar
Hi Manoj,

When contacts are inserted my leads should get tagged if same details are matching with the leads.

if email, firstname, lastname are matching then lead should update lead fields 'ready to convert' and 'status' based on contact turnover.

in the same way i have to write the trigger on lead to compare the contact details like email, firstname, lastname are matching then contact id should be updated on lead custom field.
On weekly basis we upload around 2000+ leads. so i need trigger which works for bulk inserts or updates.
Is it clear now? If yes please help me out on writing these triggers.

Regards,
Pranav
Amit Chaudhary 8Amit Chaudhary 8
Hi  Pranava ,

Please try below code. 
Trigger updateLeads1 on Contact (after insert, after update) 
{
		Id rtID= [select Id,name from RecordType where name ='APAC - Open' limit 1].Id;
		Set<String> setEmailId = new Set<String>();
		Set<String> setFirstName = new Set<String>();
		Set<String> setLastName = new Set<String>();

        for(Contact myContact : Trigger.new)
		{
			if(myContact.Email != null && myContact.FirstName != null && myContact.LastName != null)
			{
				setEmailId.add(myContact.Email);
				setFirstName.add(myContact.FirstName);
				setLastName.add(myContact.LastName);
			}
		}
		
		List<lead> listLead = [ Select Id, FirstName, LastName, Email, Status, Ready_to_Convert__c, Country_code__c FROM Lead where RecordTypeId = :rtId AND Email = :setEmailId AND FirstName = :setFirstName AND LastName = :setLastName ] ;
		
		List<Lead> listUpdatelead = new List<Lead>();
		Set<String> setLeadID = new Set<String>(); // Added to check duplicate Lead
		
        for(Contact myContact : Trigger.new)
		{
            if(myContact.email != null && myContact.FirstName != null && myContact.LastName != null) 
			{
              for( Lead checkLead : listLead )
			  {
				if( checkLead.Email == myContact.email &&  checkLead.FirstName == myContact.FirstName && checkLead.LastName == myContact.LastName )
				{
                    if(myContact.Turnover__c > 0.00)
					{
						if(setLeadID.contains(checkLead.id) == false)
						{
							checkLead.Status = 'Order Placed';
							checkLead.Ready_to_Convert__c = TRUE;
							listUpdatelead.add(checkLead);
							setLeadID.add(checkLead.id);
						}	
					}
					if((myContact.Turnover__c <= 0.00 || myContact.Turnover__c== null) && (checkLead.Country_code__c != 'India' || checkLead.Country_code__c != 'China' || checkLead.Country_code__c != 'Taiwan' || checkLead.Country_code__c != 'Hong Kong'))
					{
						if(setLeadID.contains(checkLead.id) == false)
						{
							checkLead.Status = 'Account created';
							listUpdatelead.add(checkLead);
							setLeadID.add(checkLead.id);
						}	
                    }
				}	
              }
          }
        }
		
		if( listUpdatelead.size() > 0 )
		{
			try 
			{
				update listUpdatelead;             
			} 
			catch(DMLException e) 
			{   
				System.debug('The following exception has occurred: ' + e.getMessage()); 
			}
		}
}
Now your Contact trigger is perfect. If you still get any error then please check your lead trigger as well.
Please test this trigger by UI first and then by data loader.

NOTE: This code has not been tested and may contain typographical or logical errors

Please mark this as solution by selecting it as best answer if this solves your problem, So that if anyone has this issue this post can help

Thanks
Amit Chaudhary
This was selected as the best answer
P KumarP Kumar

Hi Amit,

This time i have deactivated the lead trigger berfore inserting contacts.

Now they got inserted, but leads status were not updated.

Regards,
Pranav
Amit Chaudhary 8Amit Chaudhary 8
I am happy see your contact Trigger issue is resolve. Please try to debug below code and check value of all system.debug values.
  
Trigger updateLeads1 on Contact (after insert, after update) 
{
		Id rtID= [select Id,name from RecordType where name ='APAC - Open' limit 1].Id;
		Set<String> setEmailId = new Set<String>();
		Set<String> setFirstName = new Set<String>();
		Set<String> setLastName = new Set<String>();

        for(Contact myContact : Trigger.new)
		{
			if(myContact.Email != null && myContact.FirstName != null && myContact.LastName != null)
			{
				setEmailId.add(myContact.Email);
				setFirstName.add(myContact.FirstName);
				setLastName.add(myContact.LastName);
			}
		}
		
		List<lead> listLead = [ Select Id, FirstName, LastName, Email, Status, Ready_to_Convert__c, Country_code__c FROM Lead where RecordTypeId = :rtId AND Email = :setEmailId AND FirstName = :setFirstName AND LastName = :setLastName ] ;
		
		List<Lead> listUpdatelead = new List<Lead>();
		Set<String> setLeadID = new Set<String>(); // Added to check duplicate Lead

		System.debug('-------listLead-------->'+listLead.size() );
		
        for(Contact myContact : Trigger.new)
		{
            if(myContact.email != null && myContact.FirstName != null && myContact.LastName != null) 
			{
              for( Lead checkLead : listLead )
			  {
				if( checkLead.Email == myContact.email &&  checkLead.FirstName == myContact.FirstName && checkLead.LastName == myContact.LastName )
				{
                    if(myContact.Turnover__c > 0.00)
					{
						if(setLeadID.contains(checkLead.id) == false)
						{
							checkLead.Status = 'Order Placed';
							checkLead.Ready_to_Convert__c = TRUE;
							listUpdatelead.add(checkLead);
							setLeadID.add(checkLead.id);
							System.debug('-------checkLead-------->'+checkLead);
						}	
					}
					if((myContact.Turnover__c <= 0.00 || myContact.Turnover__c== null) && (checkLead.Country_code__c != 'India' || checkLead.Country_code__c != 'China' || checkLead.Country_code__c != 'Taiwan' || checkLead.Country_code__c != 'Hong Kong'))
					{
						if(setLeadID.contains(checkLead.id) == false)
						{
							checkLead.Status = 'Account created';
							listUpdatelead.add(checkLead);
							setLeadID.add(checkLead.id);
							System.debug('-------checkLead-------->'+checkLead);
						}	
                    }
				}	
              }
          }
        }
		
		System.debug('-------Size-------->'+listUpdatelead.size());
		
		if( listUpdatelead.size() > 0 )
		{
			try 
			{	
				System.debug('--------------->'+listUpdatelead);
				update listUpdatelead;             
			} 
			catch(DMLException e) 
			{   
				System.debug('The following exception has occurred: ' + e.getMessage()); 
			}
		}
}


Please mark this as solution by selecting it as best answer if your contact trigger is solved. So that if anyone has this issue this post can help
 
Amit Chaudhary 8Amit Chaudhary 8
Please share debug log. May be some excpetion is comming. Please share log and try to upload one single record from UI then try dataLoader
Amit Chaudhary 8Amit Chaudhary 8
Hi,

You are getting recursive trigger issue

Please reffer below blog for same issue
http://amitsalesforce.blogspot.in/search/label/Trigger

Please let us know if this will help you