USE CASE: Ensuring All Salesforce Phone Number Fields Are Consistent in Format

Problem: Default rules for entering phone numbers result in wild inconsistencies

For standard Phone fields on Salesforce^®, the standard validation is fairly weak. Users can enter up to 40 characters, and can also enter as few as 1. Salesforce also allows users to enter non-number characters in Phone fields, such as ‘x’ or ‘ext’, or even uncommon digits, such as K or &.

This situation can result in a database filled with inconsistencies, affecting sales and customer service. Here’s where Data Quality Helper’s Validation Rules can help.

It is important that data entry of phone numbers by users in Salesforce follows a consistent and repeatable pattern. The use of RegEx is just one of many ways that Data Quality Helper’s Validation Rules can be beneficial to a Salesforce org as well as its Admin(s) and users.

A regular expression (RegEx) is a sequence of characters that defines a search pattern, primarily for use in pattern matching with strings. It is a powerful tool for finding, validating, and manipulating text based on complex rules, and is supported by a wide variety of programming languages and text editors. Data Quality Helper’s flexible Validation Rules can be configured to take advantage of this functionality when it comes to creating consistent Phone fields.
For example, let’s say your company only wants 10-digit phone numbers to be entered on the Account object’s Phone field — if the Account is located in the United States.  

1. Navigate to the Data Quality Helper App, then click Validation Rule Configuration.
2. Now that you have accessed the Data Validation Rules menu, click New Rule to create a new validation rule. 
3. Next, enter a rule name. In this example, we are going to create a validation rule on the Phone field on the Account object, so choose Account as the object of the rule. 
4. Then, add a warning message, something like ‘This is not a valid US phone number,’ and click Next.
    
5. Select Account Phone as the field, then choose ‘Does Not Match Regex’ as the Criteria Operator. 
6. This will cause a validation issue to be raised whenever the Phone field’s value does not match the Regex expression we will enter next. For this example, enter into the comparison value input the following characters:        ^\d{10}$
7. This Regex expression means:
   
   • ^: This asserts the position at the beginning of the string. This ensures that the pattern must match from the very start of the input.
   • \d: This is a shorthand character class that matches any digit (equivalent to [0-9]).
   • {10}: This is a quantifier that specifies the preceding element (\d) must occur exactly 10 times. 
   • $: This asserts the position at the end of the string. This ensures that the pattern must match up to the very end of the input, preventing any extra characters after the 10 digits.
8. Click ‘Add To Criteria’ to add this criteria to the Validation Rule.
    
9. Now, to only implement this for Accounts based in the US, we will select Billing Country as the next field. Choose ‘Equals’ as the Criteria Operator, then enter ‘US’ as the Comparison Value. Click ‘Add To Criteria’ to add this criteria to the Validation Rule, then click ‘Save’.

Now, anytime a user attempts to enter a phone number with any amount other than 10 digits for an Account with the Billing Country of the United States, the user will be warned that the phone number is not a valid US phone number and a Data Quality Helper Issue record will be created.

But remember, if the user would still like to save the phone number with a non-10-digit value, the user can bypass the warning because of the flexibility of Data Quality Helper’s Soft Validation Rules (assuming the Admin allows for a soft validation option when creating the rule).
And that’s not all: The RegEx settings on the rule can be extended further to allow for an optional extension (ext, ext., x, or #) after the 10-digit number:

• ^\d{10}(?:\s*(?:ext\.?|x|#)\s*\d+)?$

Besides enhanced data validation, this app also offers duplicate data detection and resolution as well as data archival and storage management that exceeds default Salesforce functionality. Plus, don’t forget to take advantage of the list view that shows all validation, and duplication issues detected by the rules Admins created. Install the free version today!