2

I'm looking at receiving SMS (text) messages in Salesforce and the formatting of the "From" mobile phone number (in a Twilio Webhook call) is:

+35386xxxxxxx

(where 353 is the country code in this case).

By default, a Contact.MobilePhone field can contain lots of extra formatting characters: spaces, hyphens and braces (often added by the platform) or indeed just about any character. So querying to find the Contact may well fail because of that extra formatting e.g.:

353 86 xxx xxxx

One approach that comes to mind is to add a formula field (that could have an index added if I understand Force.com SOQL Best Practices: Nulls and Formula Fields correctly) that holds a cleaned up version of the number and match to that. But are there better approaches?

PS

I also now see that a series of SUBSTITUTE calls for the most-likely non-numeric characters is all that can be done in a formula field unless I am missing something there too.

PPS

Any thoughts on using a query like this (that will presumably always require a full table scan) in an org with say a million Contacts?

select Id
from Contact
where MobilePhone like '%3%5%3%8%6%x%x%x%x%x%x%x%'

Based on this answer, SOSL instead looks like the way to go.

2 Answers 2

2

Unfortunately there is no shorter way directly during query for this situation. I faced similar problem and tried my best to avoid creating another field but Phone type is salesforce has been set with this property for any value entered via the UI less than 11 digits. Here is the link for detailed conditions when you have country code and + in the beginning and how salesforce behaves.

Only two workaround you could do :

  • Create a text/formula field that copies the value without any formatting and removing white spaces. Need to be careful if its formula field and data size is large you will face indexing issues.
  • Make the formatting in your code before you make the call out to twilio, you can use the nice bunch of String methods available in apex like trim, deletewhitespace etc..
1
  • Thanks Anil. Think the indexing part is OK - added a link about that to the question. As this is an incoming call - Twilio is calling Salesforce - it is determining the format and I just have to deal with that formatting.
    – Keith C
    Commented May 15, 2016 at 8:17
0

For now I'm going with SOSL for this based on Sebastian Kessel's answer here. Works fine in small test orgs; will see how it goes in bigger orgs in the future. Here is the current code:

// Find the Contact(s) with matching mobile phone numbers
public class FindContactsListener implements Listener {

    public void process(Message m) {

        // Using SOSL to avoid non-selective query problem escpecially with a wildcarded like term such as '%3%3%5%...'.
        // See https://salesforce.stackexchange.com/questions/100518/how-to-soql-query-contacts-by-phone-number-when-there-are-more-than-100-000-cont

        // Allow arbitrary characters between the numbers
        String phone = removeNonDigits(m.phone);
        String match = addSoslWildcards(phone);

        // SOSL can't filter the explicit field so do that in code
        List<List<SObject>> results = [find :match in phone fields returning Contact(Id, Name, MobilePhone)];
        for (Contact c : (List<Contact>) results[0]) {
            if (!String.isEmpty(c.MobilePhone) && removeNonDigits(c.MobilePhone) == phone) {
               m.whoIds.add(c.Id);
               m.whoNames.add(c.Name);
            }
        }
    }

    private String removeNonDigits(String s) {
        return s != null ? s.replaceAll('[^\\d]', '') : null;
    }

    private String addSoslWildcards(String s) {
        String r = '*';
        for (Integer i = 0; i < s.length(); i++) {
            r += s.substring(i, i + 1) + '*';
        }
        return r;
    }
}

using:

public class Message {
    public String phone;
    public String text;
    public Id[] whoIds = new Id[] {};
    public String[] whoNames = new String[] {};
}

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .