function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
BmartBmart 

System.QueryException: List has no rows for assignment to SObject:

Hi, I'm trying to create a apex trigger on the Lead object to associate a Lead with an existing account. To do this I created a custom lookup field on Lead called Account (Account__c) and upon creating or updating a lead I want to query the Account object and find an existing account where the name of the account is equal to the name of the Company on the Lead. I only want 1 item returned and if the account doesn't exist I don't want the trigger to do anything. This code I have written works exactly how I want it to work, but only after update. When I try to do it on before insert it gives me the list has no rows for assignment. I'm sure there's probably a better way to do this, so if anyone can assist me I would greatly appreciate it! Thank you in advance for your help.
Trigger Account_Lookup_for_Campaign_Member on Lead (before update) {

Set<Id> acctIds = new Set<Id>();  {

       for (Lead l : Trigger.new)

           acctIds.add(l.id);

       }

 

List <Account> AccName=[Select id, name from Account Where Name =:[SELECT Company FROM Lead WHERE Id =:AcctIds].company limit 1];
     
  

       
    for (Lead acctadd : Trigger.new)  {
      
         if (!AccName.isEmpty () && acctadd.account__c == null)
     
            acctadd.Account__c = accname[0].id;            
      
        }

}

Best Answer chosen by Bmart
Vinit_KumarVinit_Kumar
Ok ,try this code :-

trigger Account_Lookup_for_Campaign_Member on Lead (before insert, before update) {

List<String> accN = new List<String>();  

       for (Lead l : Trigger.new)  
		{
           accN.add(l.company);   

       }

   	

 List <Account> AccList=[Select id, name from Account Where Name in:accN];
 Map <String,Id> accMap= new Map<String,Id>();
for(Account a:accList)
{
	accMap.put(a.name,a.id);
} 
   
          
    for (Lead acctadd : Trigger.new)  {

         if (!accMap.isEmpty () && acctadd.account__c == null)
			{
				acctadd.Account__c = accMap.get(acctadd.company);               
			}
}

All Answers

BmartBmart
This is the full trigger. 
trigger Account_Lookup_for_Campaign_Member on Lead (before insert, before update) {

Set<Id> acctIds = new Set<Id>();  {

       for (Lead l : Trigger.new)  

           acctIds.add(l.id);   

       }

   	

 List <Account> AccName=[Select id, name from Account Where Name =:[SELECT Company FROM Lead WHERE Id =:AcctIds].company limit 1];
        
    	
   
          
    for (Lead acctadd : Trigger.new)  {
         
         if (!AccName.isEmpty () && acctadd.account__c == null)
        
            acctadd.Account__c = accname[0].id;               
         
    	   }

}
Vinit_KumarVinit_Kumar
Try below code :-

trigger Account_Lookup_for_Campaign_Member on Lead (before insert, before update) {

List<String> accN = new List<String>();  

       for (Lead l : Trigger.new)  
		{
           accN.add(l.company);   

       }

   	

 Map <Id,Account> AccMap=new Map<Id,Account>([Select id, name from Account Where Name in:accN]);
        
    	
   
          
    for (Lead acctadd : Trigger.new)  {
         
         if (!AccMap.isEmpty () && acctadd.account__c == null)
        
            acctadd.Account__c = AccMap.get(acctadd.id).id;               
         
    	   }

}

If this helps,please mark it as best answer to help others :)
Arunkumar RArunkumar R
List has no rows for assignment to SObject, It generally happen your assigning list of records but the particular list does't contain any records. This is the reason your issues.

you can check list size like  AccMap.size()>0 before assigning list value .
BmartBmart
Hi and thank you both Vinit_Kumar and Arunkumar R for your input.   While I understand the no rows assignment my confusion was that my code works on update, but not on insert, so I was trying to understand why that would be the case. 

Vinit your code looks much better organized than mine, I'm not a developer, so I'm still learning and getting better. I appreciate your input here. I tried your code, but I get the following error.

Error:Apex trigger Account_Lookup_for_Campaign_Member caused an unexpected exception, contact your administrator: Account_Lookup_for_Campaign_Member: execution of BeforeUpdate caused by: System.NullPointerException: Attempt to de-reference a null object: Trigger.Account_Lookup_for_Campaign_Member: line 22, column 1

The test I did was on a existing Lead where the Company name equals "Brian Client Test" to match an existing account named "Brian Client Test", so I know for sure that there should be a match. Vinit in your code you used a map instead of a list, just trying to better understand when to use a map vs a list, but for a map to be useful isn't there supposed to be a lookup relationship established on the record? In this case I'm trying to establish the relationship and once I have that I will then use the lookup relationship to pull in information to the campaign member (separate code that I have working if i manually set the lookup relationship). Again I appreciate your time to reply to my question and look forward to additional help. Thank you. 
Vinit_KumarVinit_Kumar
This is no direct relationship between Lead and Account.Now,I understood your scenario,try below code,this should work :-

trigger Account_Lookup_for_Campaign_Member on Lead (before insert, before update) {

List<String> accN = new List<String>();  

       for (Lead l : Trigger.new)  
		{
           accN.add(l.company);   

       }

   	

 List <Account> AccList=[Select id, name from Account Where Name in:accN];
        
    	
   
          
    for (Lead acctadd : Trigger.new)  {
	
		for(integer i=0;i<AccList.size();i++)
         {
         if (!AccList.isEmpty () && acctadd.account__c == null)
			{
            acctadd.Account__c = AccList[i].id;               
			}
}
}
The reason I changed it to list coz in Map there is key,value pair.Now,as we don't have direct relationship between Lead and Account we can't define a Key here.

If this helps,please mark it as best answer to help others :)
BmartBmart
Vinit thank you! That code worked perfectly! I guess my initial query for the account ID was not the most effecient way to get the ID. I'll further study the code you wrote, so I can know how to do this going forward. Thank you again for your help! 
Vinit_KumarVinit_Kumar
Happy to help :)
BmartBmart
Hi Vinit, thanks for previously helping me with this. One thing I've noticed is that this works great when updating 1 record at a time via the UI, but when using data loader to update records in mass it is attaching the wrong accounts. It's almost as if it's only taking the initial value per update and applying it to that set of leads that are being updated. Can you please assist ? Thank you.
Vinit_KumarVinit_Kumar
Try below code,

This should take care of your issue :-  
trigger Account_Lookup_for_Campaign_Member on Lead (before insert, before update) {

List<String> accN = new List<String>();  

       for (Lead l : Trigger.new)  
		{
           accN.add(l.company);   

       }

   	

 //List <Account> AccList=[Select id, name from Account Where Name in:accN];
 // Map <String,Account> accMap= new Map<String,Account>([Select id, name from Account Where Name in:accN]);   	
   
          
    for (Lead acctadd : Trigger.new)  {

         if (!accMap.isEmpty () && acctadd.account__c == null)
			{
				acctadd.Account__c = accMap.get(acctadd.company).id;               
			}
}
BmartBmart
Hi Vinit, thank you for your continued assistance. I noticed in the new code that you commented out the map and list. I removed // and saved the trigger since it wouldn't let me save. After saving the trigger and trying to manually edit a lead I get the following error 

"Error:Apex trigger Account_Lookup_for_Campaign_Member caused an unexpected exception, contact your administrator: Account_Lookup_for_Campaign_Member: execution of BeforeUpdate caused by: System.NullPointerException: Attempt to de-reference a null object: Trigger.Account_Lookup_for_Campaign_Member: line 21, column 1"
Vinit_KumarVinit_Kumar
Bmart,

You need to debug the issue by setting the debug logs on and the correct code is below :-

trigger Account_Lookup_for_Campaign_Member on Lead (before insert, before update) {

List<String> accN = new List<String>();  

       for (Lead l : Trigger.new)  
		{
           accN.add(l.company);   

       }

   	

 //List <Account> AccList=[Select id, name from Account Where Name in:accN];
 Map <String,Account> accMap= new Map<String,Account>([Select id, name from Account Where Name in:accN]);   	
   
          
    for (Lead acctadd : Trigger.new)  {

         if (!accMap.isEmpty () && acctadd.account__c == null)
			{
				acctadd.Account__c = accMap.get(acctadd.company).id;               
			}
}

Put some debug statements and see what's coming as null.I am assuming you don't have any Account which is equal Lead company name.
BmartBmart
Hi Vinit and thank you for your responses. I'm not a developer and slowly learning how to code, so it's very much appreciated! I did a few things to test the trigger. I created an account to match the name of a lead already in the system, so that I can know for sure that there should be a match. I then went to the lead and manually (not using dataloader) clicked on edit and saved the lead, when I do that I get the error "System.NullPointerException: Attempt to de-reference a null object: Trigger.Account_Lookup_for_Campaign_Member: line 21, column 1". I then check the log and it seems to be getting a value, but for some reason still giving this null error.  below is the log.

12:09:11.258 (258760173)|CODE_UNIT_STARTED|[EXTERNAL]|01qe00000000RUc|Account_Lookup_for_Campaign_Member on Lead trigger event BeforeUpdate for [00Q3000000OZMGH]
12:09:11.259 (259084455)|SYSTEM_CONSTRUCTOR_ENTRY|[3]|<init>()
12:09:11.259 (259113369)|SYSTEM_CONSTRUCTOR_EXIT|[3]|<init>()
12:09:11.259 (259152692)|SYSTEM_METHOD_ENTRY|[5]|LIST<Lead>.iterator()
12:09:11.259 (259175492)|SYSTEM_METHOD_EXIT|[5]|LIST<Lead>.iterator()
12:09:11.259 (259188521)|SYSTEM_METHOD_ENTRY|[5]|system.ListIterator.hasNext()
12:09:11.259 (259208070)|SYSTEM_METHOD_EXIT|[5]|system.ListIterator.hasNext()
12:09:11.259 (259306759)|SYSTEM_METHOD_ENTRY|[7]|LIST<String>.add(Object)
12:09:11.259 (259335446)|SYSTEM_METHOD_EXIT|[7]|LIST<String>.add(Object)
12:09:11.259 (259345629)|SYSTEM_METHOD_ENTRY|[5]|system.ListIterator.hasNext()
12:09:11.259 (259358769)|SYSTEM_METHOD_EXIT|[5]|system.ListIterator.hasNext()
12:09:11.263 (263768121)|SOQL_EXECUTE_BEGIN|[14]|Aggregations:0|select id, name from Account where Name IN :tmpVar1
12:09:11.274 (274454901)|SOQL_EXECUTE_END|[14]|Rows:1
12:09:11.274 (274696967)|SYSTEM_METHOD_ENTRY|[17]|LIST<Lead>.iterator()
12:09:11.274 (274729850)|SYSTEM_METHOD_EXIT|[17]|LIST<Lead>.iterator()
12:09:11.274 (274743627)|SYSTEM_METHOD_ENTRY|[17]|system.ListIterator.hasNext()
12:09:11.274 (274762112)|SYSTEM_METHOD_EXIT|[17]|system.ListIterator.hasNext()
12:09:11.274 (274813471)|SYSTEM_METHOD_ENTRY|[19]|MAP<String,Account>.isEmpty()
12:09:11.274 (274842958)|SYSTEM_METHOD_EXIT|[19]|MAP<String,Account>.isEmpty()
12:09:11.274 (274917076)|SYSTEM_METHOD_ENTRY|[21]|MAP<String,Account>.get(Object)
12:09:11.274 (274944842)|SYSTEM_METHOD_EXIT|[21]|MAP<String,Account>.get(Object)
12:09:11.274 (274994113)|EXCEPTION_THROWN|[21]|System.NullPointerException: Attempt to de-reference a null object
12:09:11.275 (275153404)|FATAL_ERROR|System.NullPointerException: Attempt to de-reference a null object

Trigger.Account_Lookup_for_Campaign_Member: line 21, column 1
12:09:11.275 (275177686)|FATAL_ERROR|System.NullPointerException: Attempt to de-reference a null object
Vinit_KumarVinit_Kumar
Ok ,try this code :-

trigger Account_Lookup_for_Campaign_Member on Lead (before insert, before update) {

List<String> accN = new List<String>();  

       for (Lead l : Trigger.new)  
		{
           accN.add(l.company);   

       }

   	

 List <Account> AccList=[Select id, name from Account Where Name in:accN];
 Map <String,Id> accMap= new Map<String,Id>();
for(Account a:accList)
{
	accMap.put(a.name,a.id);
} 
   
          
    for (Lead acctadd : Trigger.new)  {

         if (!accMap.isEmpty () && acctadd.account__c == null)
			{
				acctadd.Account__c = accMap.get(acctadd.company);               
			}
}


This was selected as the best answer
BmartBmart
Thanks so much Vinit! That did the trick. Since I had previously used dataloader and it incorrectly added the wrong account values to leads I made a minor change to the trigger to compare the mapped value to the current account value and if not a match then replace it with the mapped value. I then ran a dataloader update on about 20k leads and it updated everything correctly. I really appreciate you taking time out of your schedule to assist me on this issue, It is greatly appreciated. I hope other's can benefit from this thread who have similar needs. Thanks! 

trigger Account_Lookup_for_Campaign_Member on Lead (before insert, before update) {

List<String> accN = new List<String>();  

       for (Lead l : Trigger.new)  
		{
           accN.add(l.company);   

       }

   	

 List <Account> AccList=[Select id, name from Account Where Name in:accN];
 Map <String,Id> accMap= new Map<String,Id>();
for(Account a:accList)
{
	accMap.put(a.name,a.id);
} 
   
          
    for (Lead acctadd : Trigger.new)  {

        		
			if (!accMap.isEmpty () && acctadd.account__c != accMap.get(acctadd.company))
			{
				acctadd.Account__c = accMap.get(acctadd.company);               
			}
}
}
Vinit_KumarVinit_Kumar
Happy to help :)