Sign Up ›

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

Performing Select Statement using a StartsWith field expression??

I am currently construting a dupe checker on the phone field on lead insert.  One of the thingsi did was created a field for a stripped phone number, so it only contains numbers and no characters/symbols (performed on line 15 below).  However, this field still contains the Extension if the user defined that number in the Phone field.  That means that a user may go in and define a duplicate phone number on a new lead, which won't get caught because the duplicate lead has a phone extension on it.  Because of this, i would like to add a StartsWith condition to my select statement.  Code below:

 

1 trigger DupeBlockLeadPhone on Lead (before insert) {
2
3    Map<String, Lead> leadMapPhone1 = new Map<String, Lead>();
4    
5    for (Lead lead : System.Trigger.new) {
6    
7        // Make sure the phone is not null
8        if (lead.Phone != null && ){
9    
10            // Make sure another new lead isn't also a duplicate  
11           if (leadMapPhone1.containsKey(lead.Phone)) {
12                lead.Phone.addError('Another new lead has the ' + 'same Phone address.');
13            } else {
14                string strphone = lead.phone;
15                strphone = strphone.replaceAll('\\D', '');
16                leadMapPhone1.put(strphone, lead);
17            }
18        }
19    }
20	
21	//Search the system for matching phone numbers
22	for (Lead lead : [SELECT StrippedPhone__c, Company, Name FROM Lead
23                      WHERE StrippedPhone__c IN :leadMapPhone1.KeySet() AND IsConverted = false]) {
24        Lead newLead = leadMapPhone1.get(lead.StrippedPhone__c);
25        newLead.Phone.addError('A lead with this Phone ' + 'already exists.' + '<br/>' + '<a href="https://na4.salesforce.com/' 
26                               + lead.ID + '" target="_blank">' 
27                               + lead.Name + ' - ' 
28                               + lead.Company + '</a>');
29        System.Debug('Existing Phone ' + lead.StrippedPhone__c);
30        System.Debug('New Phone ' + leadMapPhone1.keySet());
31    }
32 }

Line 23 is where I need to put the StartsWith condition.  The way I want it to read is:  SELECT FROM LEAD WHERE the Phone in leadMapPhone1 StartsWith the existing lead.Phone.

 

I am not well versed in APEX; any ideas?

Starz26Starz26

Since you are doing a keyset search, you cannot use the LIKE syntax.

 

I would suggest in line 15, you string down the phone number to be the first 10 digits or 11 if it starts with a 1.

 

You could add both the 10 and 11 digit number to the key set to cover cases where they may enter a 1 vs not entering a 1.

 

 

This assumes you are using all us based numbers