Search This Blog

Monday 19 August 2013

SharePoint Lists Column Validation


In our day to day development life, we often come across validating business rules & some common validation related to Email, Phone numbers & Zip Code. In SharePoint foundation 2010, we can achieve validation using formula under Column Validation section. We will check in this post on how to validate Email, Phone numbers & Zip Code using SharePoint out of Box Column Validation formulas.
1.        Email Column Validation
Open list and go to List settings
Click on Email column.
Insert below formula for validating email
Formula:
=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])
            )
        )
    )
)

Enter [Email] = [Enter your SharePoint Column Name]
The above formula will automatically make this a required column since the validation doesn’t allow blank columns. An easy fix for this is to wrap the above formula in an OR statement with an ISBLANK function. So something like this:
=OR(ISBLANK([YourColumnName]), And Formula From Above)

2.       Phone Number Column Validation
Open list and go to List settings.
Click on Phone Number Column.
Insert below formulas into Column Validation
Formula
=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
        )
    )
)
Here [Phone] = [Enter your column name]
The above formula will automatically make this a required column since the validation doesn’t allow blank columns. An easy fix for this is to wrap the above formula in an OR statement with an ISBLANK function. So something like this: =OR(ISBLANK([YourColumnName]), And Formula From Above)

3.       Zip/Postal Code Validation

Open list and go to List settings.
Click on Zip/Postal Code Column.
Insert below formula into Column Validation
Formula

=OR([ZIP/Postal Code]="",LEN([ZIP/Postal Code])=5,
AND(EXACT(UPPER([ZIP/Postal Code]),LOWER([ZIP/Postal Code])),
LEN([ZIP/Postal Code])=10,NOT(ISERROR(FIND("-",[ZIP/Postal Code],6)))))
Here [Zip/Postal code] = [Enter your column name]
The above formula will automatically make this a required column since the validation doesn’t allow blank columns. An easy fix for this is to wrap the above formula in an OR statement with an ISBLANK function. So something like this: =OR (ISBLANK([YourColumnName]), And Formula From Above)

 References

No comments:

Post a Comment