+ Start a Discussion
Rob Morris (bostonmorris)Rob Morris (bostonmorris) 

APEX Trigger to Link an Account to Lead in Related Field if Name or email domain match an Existing Account

Hey guys,

I need a APEX Trigger to link an Account to Lead in Related Field if Name or email domain match an Existing Account. 

I look at both the Company name on the lead and check that for a match with the account name or the email domain on the lead matches with the website domain.

I want to modify the below to work if the lead company name is even contained in the Account name or if the lead email domain is contained in the Website (or website domain)

What I have works if exact match but not if it is only part of the name or website domain.

ie: if lead company name is Salesforce and Account name is Salesforce Inc or lead email domain is: example.com and website domain is example.com/help it will not work. 


Here is what I have so far:

trigger addAccount on Lead (before Insert, before Update)
{


 List<string> companies=new list<string>();

 For (lead l:trigger.new){
   companies.add(l.company);
  }

 List<Account> leadAccountIds=[Select Id, Name FROM Account WHERE Name IN: companies];

 Map<String, Id> acctNameId=new Map<String, Id>();

 For (Account a:leadAccountIds){
   acctNameId.put(a.name,a.Id);
  }
 
 For (Lead l2:trigger.new){
  if(acctNameId.containsKey(l2.company)){
    l2.Lead_Account__c=acctNameId.get(l2.company);
   }

  }
 
  
 Map<String, Id> domains = new Map<String, Id>();
 for(Lead record: Trigger.new) {
    domains.put(record.Domain__c, null);
 }
 for(Account record: [SELECT Domain__c FROM Account WHERE Domain__c IN :domains.keySet()]) {
    domains.put(record.Domain__c, record.Id);
 }
 for(Lead record: Trigger.new) {
    if(domains.get(record.Domain__c) != null) {
        record.Lead_Account__c = domains.get(record.Domain__c);
    }
  }
}

Any help is appreciated.
Terence_ChiuTerence_Chiu
Hey Rob. I am understanding your problem correctly, you want to figure out how to do partial name or domain matches between the lead and account object. Have you thought about using formula fields on the Account object to generate a more a clean version of both fields to be used for comparison purposes?

So if you did have a values in Domain__c that is example.com/help you can have a formula that only pulls the string "example" from that field .

LEFT(SUBSTITUTE( Website , "www.", ""), LEN(Website) - 
FIND(".", SUBSTITUTE( Website , "www.", "")) - 1)


The above formula replaces any url that contains "www." and replaces it with an empty string. We then find the position of the reamining '.' character and using the index position we pull only the characters to left of the '.' character.

For issues with the Account name, you can create another formula field that replaces characters like spaces, string values like "Inc", "Corp", "Corporation" with an empy string so that you have a cleaned version of the name. 

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( Name , " ", ""), "Inc", "") , "Corporation", ""), "LLC", "")

 
In your trigger code, you would have to do similiar cleaning by using String.indexOf(), String.replace(), String.left() methods on the company name and email domains. In your queries you would use the cleaned versions of the lead field values and the account formula fields.
 
Rob Morris (bostonmorris)Rob Morris (bostonmorris)

Terence, thanks for taking the time to review and this is a very good solution however it would require I know all the unnecessary values in order for it to work properly. for example what if the website is http://us.example.com then neither of our formulas would work. I am hoping to find a formula that can look in the field and see if the email domain string 'example.com' is found in the website domain, that way it should work 99% of the time. I don't care where inthe string it is as long as all characters appear in that order.

Terence_ChiuTerence_Chiu
From a purely code perspective you could change you queries so that instead of using " IN " you can use the "LIKE" operator. You will need to build the query statement as a string and use the Database.query method. This will retrieve partial matches for you, however, you will most likely be unable to use maps. Instead you can nest a for loop within the Lead for loop and check if the lead domain is contained within the account domain using String.contains method.The nested loops will mean the trigger may take longer to run, but this way you can match for partials. You may have to tweak this by only matching X left most characters or X right most characters. Below is a rough idea of how to approach it.  
 
trigger addAccount on Lead (before Insert, before Update)
{


    List<string> companies=new list<string>();
    String companyQuery = ‘SELECT Id, Name FROM Account WHERE’;
     String domainQuery = ‘SELECT Id, Domain__c FROM Account WHERE’;

 For (lead l:trigger.new){
    //companies.add(l.company);
    companyQuery += ‘ Name LIKE ’ + ‘\’%’ + l.company + ‘%\’’ + ‘ OR ’;
    domainQuery +=  ‘ Domain__c LIKE ’ + ‘\’%’ + l.domain__c + ‘%\’’ + ‘ OR ’;
  }
companyQuery = companyQuery.left(companyQuery.length() - 4);
domainQuery  = domainQuery.left(domainQuery.length() - 4);

 //List<Account> leadAccountIds = [Select Id, Name FROM Account WHERE Name IN: companies];
List<Account> leadAccountIds = (List<Account>) Database.query(companyQuery);


 //Map<String, Id> acctNameId=new Map<String, Id>();

 /*For (Account a:leadAccountIds){
    acctNameId.put(a.name,a.Id);
 }*/
 
 For (Lead l2:trigger.new){
 /* if(acctNameId.containsKey(l2.company)){
    l2.Lead_Account__c=acctNameId.get(l2.company);
   }*/
   For(Account acc : leadAccountIds)
	if(acc.Name.contains(l2.Company) || l2.Company.contains(acc.Name){
		l2.Lead_Account__c = acc.Id;
	}

  }
 
  
 //Map<String, Id> domains = new Map<String, Id>();
 /*for(Lead record: Trigger.new) {
    domains.put(record.Domain__c, null);
 }*/
 /*for(Account record: [SELECT Domain__c FROM Account WHERE Domain__c IN :domains.keySet()]) {
    domains.put(record.Domain__c, record.Id);
 }*/

 List<Account> domainAccs = (List<Account>) Database.query(domainQuery);

 for(Lead record: Trigger.new) {
    /*if(domains.get(record.Domain__c) != null) {
        record.Lead_Account__c = domains.get(record.Domain__c);
    }*/

    for(Account acc : domainAccs){
	if(acc.Domain__c.contains(record.Domain__c) || record.Domain__c.contains(acc.Domain__c){
		record.Lead_Account__c = acc.Id
	}
    } 

  }
}