+ Start a Discussion
Ad.baylesAd.bayles 

Trigger to Assign Duplicates on Lead Insert - Help to make it bulk-safe

Hello guys,

 

I tried to write a trigger which will assign leads to existing account owner if any duplicate account, or contact owner, or lead owner.

 

I see that this trigger uses at least 3 sql queries ; How is it possible to reduce this ?

 

Here is my code below  :

 

trigger AutoAssignDupesOnLeadInsert on Lead (before insert) {


for ( Lead dupelead : Trigger.new){


for ( Integer i=0 ; i < Trigger.new.size(); i++){


for(Lead existinglead :[SELECT email, OwnerId FROM Lead WHERE email=:trigger.new[i].email AND IsDeleted = false ORDER BY CreatedDate DESC] ){
Id existingOwnerId3 = existinglead.OwnerId;
if(existinglead.email == dupelead.email){
dupelead.OwnerId = existingOwnerId3 ;
dupelead.status = 'Duplicate Lead Found';}
}
for(Contact existingctc :[SELECT email,OwnerId FROM Contact WHERE email=:trigger.new[i].email AND IsDeleted = false ORDER BY CreatedDate DESC]){
Id existingOwnerId2 = existingctc.OwnerId;
if(existingctc.email == dupelead.email){
dupelead.OwnerId = existingOwnerId2 ;
dupelead.status = 'Duplicate Contact Found';}
}
for(Account existingacct  :[SELECT Name,OwnerId FROM Account WHERE Name=:trigger.new[i].company AND IsDeleted = false ORDER BY CreatedDate DESC]){
Id existingOwnerId = existingacct.OwnerId;
if(existingacct.name == dupelead.company){
dupelead.OwnerId = existingOwnerId ;
dupelead.status = 'Duplicate Account Found';}
}

}}}

Best Answer chosen by Admin (Salesforce Developers) 
Rahul SharmaRahul Sharma

Hi Ad.bayles,

 

I reformatted your trigger to bulkify it.

Check if it helps you to understand the use of map. It may not be the exact solution but hope it helps a little.

 

trigger AutoAssignDupesOnLeadInsert on Lead (before insert) {
	Set<String> setLeadEmail = new Set<String>();
	Set<String> setLeadCompany = new Set<String>();
	for ( Lead objLead : Trigger.new){	
		//fetch the Email, needed for SOQL
		setLeadEmail.add(objLead.Email);
		//fetch the Company, needed for SOQL
		setLeadCompany.add(objLead.Company);
	}

	Map<String, Account> mapAccount = Map<String, Account>();
	//Create a map of Name, Account
	for(Account a : [SELECT Name, OwnerId FROM Account WHERE Name=:setLeadCompany AND IsDeleted = false ORDER BY CreatedDate DESC]){
		mapAccount.put(a.Name, a);
	}
	//Create a map of Email, Contact
	Map<String, Contact> mapContact = Map<String, Contact>();
	for(Contact c : [SELECT email,OwnerId FROM Contact WHERE email=:setLeadEmail AND IsDeleted = false ORDER BY CreatedDate DESC]){
		mapContact.put(c.email, c);
	}
	//Create a map of Email, Lead
	Map<String, Lead> mapLead = new Map<String, Lead>();
	for(Lead l : [SELECT email, OwnerId FROM Lead WHERE email=:setLeadEmail AND IsDeleted = false ORDER BY CreatedDate DESC]){
		mapLead.put(l.email, l);
	}

	for(Lead l : Trigger.new){
		//Check if Company is present in map
		if(mapAccount.containsKey(l.Company)){
			l.OwnerId = mapAccount.containsKey(l.Company).OwnerId;
			l.status = 'Duplicate Lead Found';
		}
		//Check if Email is present in map
		else if(mapContact.containsKey(l.Email)){
			l.OwnerId = mapContact.containsKey(l.Email).OwnerId;
			l.status = 'Duplicate Lead Found';
		}
		//Check if Email is present in map
		else if(mapLead.containsKey(l.Email)){
			l.OwnerId = mapLead.containsKey(l.Email).OwnerId;
			l.status = 'Duplicate Lead Found';
		}
	}
}

 

All Answers

Rahul SharmaRahul Sharma

Hi Ad.bayles,

The problem is use of queries inside a for loop and and also there are 3 level for-loop.

Using maps will help you getting the queries out of the loop and avoiding the 3 level loops.

Ad.baylesAd.bayles

Hello Rahul,

Thank you for your answer , much appreciated ;  I understand that I need to make a Map outside of the for loop , but cannot get the right syntax to make it work :

Rahul SharmaRahul Sharma

Hi Ad.bayles,

 

I reformatted your trigger to bulkify it.

Check if it helps you to understand the use of map. It may not be the exact solution but hope it helps a little.

 

trigger AutoAssignDupesOnLeadInsert on Lead (before insert) {
	Set<String> setLeadEmail = new Set<String>();
	Set<String> setLeadCompany = new Set<String>();
	for ( Lead objLead : Trigger.new){	
		//fetch the Email, needed for SOQL
		setLeadEmail.add(objLead.Email);
		//fetch the Company, needed for SOQL
		setLeadCompany.add(objLead.Company);
	}

	Map<String, Account> mapAccount = Map<String, Account>();
	//Create a map of Name, Account
	for(Account a : [SELECT Name, OwnerId FROM Account WHERE Name=:setLeadCompany AND IsDeleted = false ORDER BY CreatedDate DESC]){
		mapAccount.put(a.Name, a);
	}
	//Create a map of Email, Contact
	Map<String, Contact> mapContact = Map<String, Contact>();
	for(Contact c : [SELECT email,OwnerId FROM Contact WHERE email=:setLeadEmail AND IsDeleted = false ORDER BY CreatedDate DESC]){
		mapContact.put(c.email, c);
	}
	//Create a map of Email, Lead
	Map<String, Lead> mapLead = new Map<String, Lead>();
	for(Lead l : [SELECT email, OwnerId FROM Lead WHERE email=:setLeadEmail AND IsDeleted = false ORDER BY CreatedDate DESC]){
		mapLead.put(l.email, l);
	}

	for(Lead l : Trigger.new){
		//Check if Company is present in map
		if(mapAccount.containsKey(l.Company)){
			l.OwnerId = mapAccount.containsKey(l.Company).OwnerId;
			l.status = 'Duplicate Lead Found';
		}
		//Check if Email is present in map
		else if(mapContact.containsKey(l.Email)){
			l.OwnerId = mapContact.containsKey(l.Email).OwnerId;
			l.status = 'Duplicate Lead Found';
		}
		//Check if Email is present in map
		else if(mapLead.containsKey(l.Email)){
			l.OwnerId = mapLead.containsKey(l.Email).OwnerId;
			l.status = 'Duplicate Lead Found';
		}
	}
}

 

This was selected as the best answer
Ad.baylesAd.bayles

It's great, works perfect / exactly as wanted. Many thanks Rahul, you got the logic.

 

So  I understand that :

  • Above all, I create a new set , where i'll store the string that I want to compare (as many sets as there are fields to be compared)
  • Then I need tomake a 1st 'for' Loop, where I'll store values in my maps
  • And finally Loop through all leads processed by the trigger and insert my keys.

->True ?

 

I made some little changes to the code so as to make it ready to use :

 

 

 

 

trigger AutoAssignDupesOnLeadInsert on Lead (before insert) {
    Set<String> setLeadEmail = new Set<String>();
    Set<String> setLeadCompany = new Set<String>();
    for ( Lead objLead : Trigger.new){  
        //fetch the Email, needed for SOQL
        setLeadEmail.add(objLead.Email);
        //fetch the Company, needed for SOQL
        setLeadCompany.add(objLead.Company);
    }

    Map<String,Account> mapAccount = new Map<String, Account>();
    //Create a map of Name, Account
    for(Account a : [SELECT Name, OwnerId FROM Account WHERE Name=:setLeadCompany AND IsDeleted = false ORDER BY CreatedDate DESC]){
        mapAccount.put(a.Name, a);
    
    }
    //Create a map of Email, Contact
    Map<String, Contact> mapContact = new Map<String, Contact>();
    for(Contact c : [SELECT email,OwnerId FROM Contact WHERE email=:setLeadEmail AND IsDeleted = false ORDER BY CreatedDate DESC]){
        mapContact.put(c.email, c);
    }
    //Create a map of Email, Lead
    Map<String, Lead> mapLead = new Map<String, Lead>();
    for(Lead l : [SELECT email, OwnerId FROM Lead WHERE email=:setLeadEmail AND IsDeleted = false ORDER BY CreatedDate DESC]){
        mapLead.put(l.email, l);
    }

    for(Lead l : Trigger.new){
        //Check if Company is present in map
        if(mapAccount.containsKey(l.Company)){
            l.OwnerId = mapAccount.get(l.Company).OwnerId;
            l.status = 'Duplicate Account Found';
        }
        //Check if Email is present in map
        else if(mapContact.containsKey(l.Email)){
            l.OwnerId = mapContact.get(l.Email).OwnerId;
            l.status = 'Duplicate Contact Found';
        }
        //Check if Email is present in map
        else if(mapLead.containsKey(l.Email)){
            l.OwnerId = mapLead.get(l.Email).OwnerId;
            l.status = 'Duplicate Lead Found';
        }
    }
}

Rahul SharmaRahul Sharma

Good that you understand and it helped to you.

Made a typos. :P

Cheers!