+ Start a Discussion
P KumarP Kumar 

execution of BeforeInsert caused by: System.QueryException: List has more than 1 row for assignment to SObject Trigger.LeadEventDedupeTestTrigger

Hi I have tried to create a trigger to Lead Dedupe Management.
It worked perfectly when i inserted single record.
but when i upload through data loader am getting this error.

following is my code. Can any one help in createing the same for bulk inserts of lead through data loader.

==================================================================================
trigger LeadEventDedupeTestTrigger on Lead (before insert, before update)  {

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

    for(Integer i=0;i<Trigger.new.size();i++){
            if(Trigger.new[i].Email !=null && Trigger.new[i].FirstName !=null && Trigger.new[i].LastName !=null){
                Contact ContactList = [Select Id ,Email,FirstName,LastName,Company__c,Account_Number__c,PIN__c,Ship_To__c,
                                            Created_Date__c,Primary_Account_Owner__c,Primary_Account_Owner_ID__c,Primary_Sales_Function__c,
                                            First_Order_Date__c,OwnerId,Owner.Name,Turnover__c
                                            FROM Contact WHERE Email =: Trigger.new[i].Email AND FirstName =: Trigger.new[i].FirstName AND LastName =: Trigger.new[i].LastName]; 
                {
                    Trigger.new[i].Existing_Contact__c = TRUE;
                    Trigger.new[i].Contact_Created_Date__c = ContactList.Created_Date__c;
                    Trigger.new[i].Contact_ID__c = ContactList.Id;
                    Trigger.new[i].Account_Number__c = ContactList.Account_number__c;
                    Trigger.new[i].Ship_To__c = ContactList.Ship_To__c;
                    Trigger.new[i].Contact_Company__c = ContactList.Company__c;
                    Trigger.new[i].Primary_Account_Owner__c = ContactList.Primary_Account_Owner__c;
                    Trigger.new[i].Primary_Account_Owner_ID__c = ContactList.Primary_Account_Owner_ID__c;
                    Trigger.new[i].Contact_Primary_Sales_Function__c = ContactList.Primary_Sales_Function__c;
                    Trigger.new[i].First_Order_Date__c = ContactList.First_Order_Date__c;
                    Trigger.new[i].Contact_Owner_ID__c = ContactList.Owner.Name;
                    Trigger.new[i].Turnover__c = ContactList.Turnover__c;
                    Trigger.new[i].PIN__c = ContactList.PIN__c;
                    
                    if(Trigger.new[i].Lead_Created_Date__c > ContactList.Created_Date__c){
                  if(Trigger.new[i].LeadSource == 'Supplier Lead Referral' || Trigger.new[i].LeadSource == 'Supplier Live Inquiry'){
                      Trigger.new[i].OwnerId = ContactList.OwnerId;
                  }
              else{
                Trigger.new[i].addError('Lead already exist as a Contact, Account Number : ' + ContactList.Account_number__c + ' / PIN : ' + ContactList.PIN__c + '/ Contact ID : ' + ContactList.Id);
              }
                }
            }
        }
    }
}

Best Answer chosen by P Kumar
Amit Chaudhary 8Amit Chaudhary 8
I hope code which i frwd you on your email id will help you.

All Answers

Le NguyenLe Nguyen
Hi,

Because the query returned more than 1 record.

You should change your query :
 Contact ContactList = [Select Id ...

to  list<Contact> ContactList = [Select Id...

Regards,
Le
P KumarP Kumar
When i try to put list<contact>

am getting following error.

User-added image

Help me to come out of this error.

Regards,
Pranav
Jayant JadhavJayant Jadhav
Hi Pranav,

If you use list<Contact> ContactList = [Select Id...,   then below line will produce complitation error. 
Trigger.new[i].Contact_Created_Date__c = ContactList.Created_Date__c

Bcz ContactList is List type i.e we can array of records. You statement should be as below.
Trigger.new[i].Contact_Created_Date__c = ContactList[0].Created_Date__c

OR
You can use limit 1 in SOQL query and no further modification is required.

Contact ContactList = [Select Id ,Email,FirstName,LastName,Company__c,Account_Number__c,PIN__c,Ship_To__c,
                                            Created_Date__c,Primary_Account_Owner__c,Primary_Account_Owner_ID__c,Primary_Sales_Function__c,
                                            First_Order_Date__c,OwnerId,Owner.Name,Turnover__c
                                            FROM Contact WHERE Email =: Trigger.new[i].Email AND FirstName =: Trigger.new[i].FirstName AND LastName =: Trigger.new[i].LastName limit 1];


Note: Plz consider trigger best practice before inplementation of any trigger. In your code you are using SOQL query in for loop which is not a best practice to write a code. 
Amit Chaudhary 8Amit Chaudhary 8
Please try below code :-
trigger LeadEventDedupeTestTrigger on Lead (before insert, before update)  {

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

    for(Integer i=0;i<Trigger.new.size();i++)
	{
        if(Trigger.new[i].Email !=null && Trigger.new[i].FirstName !=null && Trigger.new[i].LastName !=null)
		{
            Contact ContactList = [Select Id ,Email,FirstName,LastName,Company__c,Account_Number__c,PIN__c,Ship_To__c,
                                            Created_Date__c,Primary_Account_Owner__c,Primary_Account_Owner_ID__c,Primary_Sales_Function__c,
                                            First_Order_Date__c,OwnerId,Owner.Name,Turnover__c
                                            FROM Contact WHERE Email =: Trigger.new[i].Email AND FirstName =: Trigger.new[i].FirstName AND LastName =: Trigger.new[i].LastName limit 1] ;
            {
                    Trigger.new[i].Existing_Contact__c = TRUE;
                    Trigger.new[i].Contact_Created_Date__c = ContactList.Created_Date__c;
                    Trigger.new[i].Contact_ID__c = ContactList.Id;
                    Trigger.new[i].Account_Number__c = ContactList.Account_number__c;
                    Trigger.new[i].Ship_To__c = ContactList.Ship_To__c;
                    Trigger.new[i].Contact_Company__c = ContactList.Company__c;
                    Trigger.new[i].Primary_Account_Owner__c = ContactList.Primary_Account_Owner__c;
                    Trigger.new[i].Primary_Account_Owner_ID__c = ContactList.Primary_Account_Owner_ID__c;
                    Trigger.new[i].Contact_Primary_Sales_Function__c = ContactList.Primary_Sales_Function__c;
                    Trigger.new[i].First_Order_Date__c = ContactList.First_Order_Date__c;
                    Trigger.new[i].Contact_Owner_ID__c = ContactList.Owner.Name;
                    Trigger.new[i].Turnover__c = ContactList.Turnover__c;
                    Trigger.new[i].PIN__c = ContactList.PIN__c;
                    
                    if(Trigger.new[i].Lead_Created_Date__c > ContactList.Created_Date__c){
                  if(Trigger.new[i].LeadSource == 'Supplier Lead Referral' || Trigger.new[i].LeadSource == 'Supplier Live Inquiry'){
                      Trigger.new[i].OwnerId = ContactList.OwnerId;
                  }
              else{
                Trigger.new[i].addError('Lead already exist as a Contact, Account Number : ' + ContactList.Account_number__c + ' / PIN : ' + ContactList.PIN__c + '/ Contact ID : ' + ContactList.Id);
              }
                }
            }
        }
    }
}
Please let us know if this will help you
 
P KumarP Kumar

Hi Amith,

After i insert 200+ Test Records, there were 47 Success and 171 Errors occured.

The error is LeadEventDedupeTestTrigger: System.LimitException: Too many SOQL queries: 101

Data Loader Lead Inserts Acknowledgement

Can anyone help me out for not to get this error.

Regards,
Pranav

Amit Chaudhary 8Amit Chaudhary 8
Hi Pranav.

Please send me your email id i will send code due to some error i am not able to post the code.
Amit Chaudhary
amit.chaudhary21@gmail.com
 
P KumarP Kumar

Hi Amit,

I have sent you test mail.

this is my email id : mail.pranava@gmail.com

Regards,
Pranav

Jayant JadhavJayant Jadhav
@Pranava,

This is bacause in your trigger you have used SOQL in for loop and if we inset the records using data loader with batch size 200 you for loop will be getting executed 200 time i.e SOQL is getting executed 200 time but as per SFDC governer limit we can use only 100 SOQL query per transaction.

Solution for this is we have to write our SOQL query out site for loop. Also if you don't want to change your code the while using data load change the batch size equal to 60 0r 80.

Goto data loader--> settings--> Batch size =60.

Try this and let me know 
 
Amit Chaudhary 8Amit Chaudhary 8
I hope code which i frwd you on your email id will help you.
This was selected as the best answer