[SalesForce] Validation Rule on Phone Field to allow only Numbers or Blank

I wanted to write a validation rule for default Phone field.

AND( 
    NOT(ISBLANK( Phone )), 
    NOT(ISNUMBER( Phone )) 
)

it works fine but allows only 9 digits.
What could be the problem?
When i Deactivate this validation rule, it accepts more than 10 digits or alphabets.
Why is the Number restriction enforced?

Best Answer

Ten digit numbers are automatically reformatted as '(###) ###-####', which means that ISNUMBER will return false (because it has spaces, parentheses, and a hyphen). Also, when you see the documentation, you'll see it also supports numbers like 2e2 or 123.123, which are both numbers, but not phone numbers.

So, what you're probably looking for, for a start, is two specific patterns that will match US and other numbers:

AND(NOT(REGEX(Phone,'\\d*')), NOT(REGEX(Phone,'\\(\\d{3}\\) \\d{3}-\\d{4}')))

You'll want to experiment with REGEX to determine which rule(s) you'd like to follow.

Related Solutions

[SalesForce] Regex Validation rule for telephone number

I would recommend a two step approach. First keep your length restrictions on hold and go and find a well tested working solution for phone numbers. You don't have to reinvent the wheel:

try as formula then something like:

NOT(  
  REGEX( SamplePhone__c, "^\\+([0-9 ]+)$" ) 
  &&  LEN(  SUBSTITUTE(SamplePhone__c , " ", "")   ) < 26  
  &&  LEN(  SUBSTITUTE(SamplePhone__c , " ", "")   ) > 6 
)

Replace the regex to the one of your choice and use nested SUBSTITUE(...) to remove other character you don't want to count against your Min and Max.

The idea is to decouple the regex and the length checks and combine them logically.

[SalesForce] Validation rule for phone number

This should do the trick given the requirements:

NOT(REGEX(Phone, "[0-9+\\-\\(\\)]*"))

But it will allow for some funky phone numbers that are definitely not valid, e.g. "+123-+()()()"

Best Answer

Related Solutions

[SalesForce] Regex Validation rule for telephone number

[SalesForce] Validation rule for phone number

Related Topic