Tuesday 21 January 2014

Column Validation in SharePoint 2010 - Email and Phone Number Validation

Column Validation, I feel, is a relatively less used feature of SharePoint. If used well it can save time and lessen dependency on SharePoint Designer.

Basic validations such as date range validations or number validations can be performed by using simple expressions as well as data format validations for emails and phone numbers can be performed.

Expression for Phone validation -
=AND(
    LEN([Phone])=14,
    IF(ISERROR(FIND("(", [Phone],1)),
        FALSE,
        (FIND("(", [Phone]) = 1)
    ),
    IF(ISERROR(FIND(")", [Phone],5)),
        FALSE,
        (FIND(")", [Phone], 5) = 5)
    ),
    IF(ISERROR(FIND(" ", [Phone],6)),
        FALSE,
        (FIND(" ", [Phone], 6) = 6)
    ),
    IF(ISERROR(FIND("-", [Phone],10)),
        FALSE,
        (FIND("-", [Phone], 10) = 10)
    ),
    IF(ISERROR(1*CONCATENATE(MID([Phone], 2, 3), MID([Phone], 7, 3), MID([Phone], 11, 4))),
        FALSE,
        AND(
            1*CONCATENATE(MID([Phone], 2, 3), MID([Phone], 7, 3), MID([Phone], 11, 4)) > 1000000000,
            1*MID([Phone], 2, 3) <> 911,
            1*MID([Phone], 7, 3) <> 911,
            1*MID([Phone], 7, 3) <> 555
        )
    )
)

Expression for Email validation -
=AND(
    ISERROR(FIND(" ", [Email],1)),
    IF(ISERROR(FIND("@", [Email],2)),
        FALSE,
        AND(
            ISERROR(FIND("@",[Email], FIND("@", [Email],2)+1)),
            IF(ISERROR(FIND(".", [Email], FIND("@", [Email],2)+2)),
                FALSE,
                FIND(".", [Email], FIND("@", [Email],2)+2) < LEN([Email])
            )
        )
    )
)


I have taken these expressions from "The Chris Kent" blog. Please do visit that blog for a detailed explanation of these expressions. The links are as follows:
Finally, the pros and cons of this approach are as follows:

Pros -
  • Implementation is simple - just place the expression in the Column Validation text box
  • No customization required - no javascript/jQuery, or any other type of custom code is required.
  • Modification or management is simple
  • Replication across lists is simple
Cons -
  • Evaluating expressions for simple validations is also a tedious task
  • Due to the complexity of expressions there are limitations to the depth to which the validation can be performed. It is less flexible.
  • Error messages are displayed as field validators rather than javascript alerts (users usually prefer alerts)
- Huzefa Mala

No comments:

Post a Comment