Custom Error Messages For Invalid Phone Number Inputs In Sharepoint Column Validation
Validating Phone Numbers in SharePoint Columns
Validating phone number inputs in SharePoint columns is important to ensure high quality standardized data. Validation can prevent invalid or improperly formatted numbers from being entered. Key reasons to validate phone numbers include:
- Ensure users enter 10 digit phone numbers in a standard (XXX) XXX-XXXX format. This standardized format makes reports and downstream usage easier.
- Prevent invalid characters like letters or special characters. Only allow numbers, parentheses, dashes, spaces, and optional extensions.
- Catch inputs that are the wrong length, such as too short or too long to be valid phone numbers for your location.
By validating on initial entry, invalid data can be prevented from making its way into SharePoint lists and libraries. This helps ensure high quality and consistent data later on.
Using Column Validation in SharePoint
SharePoint provides column-level and list-level validation options to restrict inputs. Column validations check the value as it is entered into a single column. List validation allows creating validation formulas across multiple columns.
Use column validation for simple single column checks like phone number formats. Use list validation for more complex logic across fields. List validation runs when items are initially added or updated.
Both column and list validation support custom error messages when values fail the check. Parameters can be passed to error messages to output dynamic text.
Creating a Phone Number Validation Formula
A regex (regular expression) pattern is commonly used to validate phone number formats in column validation. The regex defines the required structure and allowed character set.
A basic phone number regex allows the following format:
- (XXX) XXX-XXXX
- XXX-XXX-XXXX
Parentheses, spaces, and dashes are optional in most standard formats. Specifying quantifiers like {3} checks for the exact number of digits for each segment.
To allow additional special characters that may appear, such as an extension, the character class can be adjusted:
- [0-9(). -]{10,14}
This allows 10 to 14 digits to support a 4 digit extension. Other common special characters that could be allowed include , * #.
Example Validation Formula
Here is an example regex formula that could be used for column validation:
=AND(LEN([Column1])>=10,LEN([Column1])<=14),AND(REGEXMATCH([Column1],"[0-9(). -]{10,14}"))
This checks that the input value is between 10 and 14 characters, and that it matches the defined regular expression of valid characters and lengths.
Displaying Custom Error Messages
When validation fails, SharePoint displays a default error message that the value did not pass the column validation restrictions. This message can be replaced with a custom error using the ValidationMessage parameter:
=AND(LEN([Column1])>=10,LEN([Column1])<=14),AND(REGEXMATCH([Column1],"[0-9(). -]{10,14}")),[Column1],ValidationMessage="Phone number must be provided in a valid format like (555) 555-5555 or 555-555-5555"]
Dynamic Error Message Parameters
Dynamic error messages can be displayed using validation parameters. Pass column references or text strings as parameters.
Example dynamic error message:
=AND(LEN([Column1])>=10,LEN([Column1])<=14),AND(REGEXMATCH([Column1],"[0-9(). -]{10,14}")),[Column1],ValidationMessage="The phone number entered in the "&[Column1InternalName]&" column is invalid. Please enter a 10 to 14 digit number with allowed formats like (555) 555-5555"]
Setting Validation on Phone Columns
The validation formula can be applied to a new or existing column in list settings. Under Column Validation, enter the formula and error message parameters to set the restriction.
Test validation by adding items with invalid phone numbers in the list form and verifying the error message appears on entry.
If the validation is not working correctly, check the following:
- Formula syntax - Validation settings do not show an error if the formula logic contains errors
- Match column data type - Text validation works on single line of text columns
- View item edit form - Validation occurs on item entry/update, not visible by default
Considerations for Multiple Phone Columns
If validating multiple phone number columns, the validation logic will need to be applied to each column individually. The same formula can be reused across columns easily:
Column1 validation formula:
=AND(LEN([Column1])>=10,LEN([Column1])<=14),AND(REGEXMATCH([Column1],"[0-9(). -]{10,14}")), [Column1], ValidationMessage="Invalid phone format entered"]
Column2 validation formula:
=AND(LEN([Column2])>=10,LEN([Column2])<=14),AND(REGEXMATCH([Column2],"[0-9(). -]{10,14}")), [Column2], ValidationMessage=" Invalid phone format entered"
Example Validation Setup
Here is an example phone number validation setup that could be used in a Contacts list with a PhoneNumber column.
Validation Formula
=AND(
LEN([PhoneNumber]) >=10,
LEN([PhoneNumber]) <= 14,
REGEXMATCH([PhoneNumber], "^\(?\d{3}\)?[-.\s]?\d{3}[-.\s]?\d{4}$")
), [PhoneNumber], ValidationMessage="Phone number must be provided in a valid format like (555) 555-5555 or 5555555555. Please enter a valid 10-14 digit phone number."
This breaks down to:
- Check length is 10 to 14 digits
- Match regex pattern for formats like (555) 555-5555
- Pass invalid number to error message
- Display custom error message example
Extending Sample Validation
To extend this to additionally allow phone extensions, update the length check and regex pattern:
=AND(
LEN([PhoneNumber]) >= 10,
LEN([PhoneNumber]) <= 18
REGEXMATCH([PhoneNumber], "^\(?\d{3}\)?[-.\s]?\d{3}[-.\s]?\d{4}(\se?x?t?(\d*))?$")
)
This allows extensions after the phone number specified as ext. XXXX or xXXXX.
Additional Validation Options
Some additional options to consider for robust phone number validation include:
List Validation for Cross-Field Logic
Use list validation if the phone number needs to be checked against other columns like Mobile Number to prevent duplicates.
Column Formatting JSON
Apply additional input formatting and transformations like deleting special characters using the Column Formatting JSON options.
Client-Side Rendering with Power Apps
For dynamic UI effects like highlighting fields red or showing messages during input, use Power Apps client-side rendering with custom column formatting.
Conclusion
Validating phone number inputs in SharePoint is an important step to ensure clean consistent formatted data in lists and libraries. Using column restrictions paired with custom error messaging creates a smooth input experience guiding users to provide valid information.
The regex patterns and SharePoint validation options covered provide flexible tools to validate common phone number formats, extensions, and special characters according to individual needs. When setting up validation, test with different invalid inputs to ensure the logic covers all the edge cases you need to catch.
