Column validations and List validations in SharePoint 2010

Introduction

List validation and column validation is one of the notable feature of SharePoint 2010. We will see how we can set validation to a list and to a column with formulas without using SharePoint designer
Taking an example of an EmployeeEntry list where employees will enter their data in the list; we will see how the validation rules can be set on this list


The list schema of EmployeeEntry list is as follows
Title - Single line of text
Description - Multiline of text
EmpId - Numeric column
EmpType - Choice [dropdown with values 'Permanent', 'Vendor']

Business Rules

Following are the business rules we are going to implement with the validation settings.
Rule 1> EmpId cannot be duplicate
Rule 2> EmpId cannot have decimal values and should allow only positive numbers
Rule 3> Employees of type ‘Vendor’ are not allowed to enter information

Validation settings

There are two types of validation settings, one at list level and another at column(field) level.

List level

After creating the list, go to the list settings page. We can observe validation SETTINGS OPTION under General Settings category.




When we click validation settings,the setting page will show us list level validations to set.

In the Insertcolumn we will have all the column list that are eligible for setting the validation.
(not all columns can be used to set validations)
In the formula section we will provide formula to validate columns data.
In the UserMessage section, we will provide the error message that will be poped out if invalid data is provided.

Field level settings

In the list settings page, under columns section click the column EmpId which will take us to column settings page.




In the Additional Column Settings page, we can see the option Enforce unique values: (the pointer 1 in the image)
This is the new option which enforces, unique values in the column.
Under column validation section, in Formula text box we will provide formula to validate the user input.
In User message text box, we will provide the error message that will be poped out, if invalid data is provided.

Formulas

In the list settings page, under columns section click the column EmpId which will take us to column settings page.

Rule 1> EmpId cannot be duplicate

To implement Rule 1, select ‘Yes’ radio button under Enforce unique values and click ‘Ok’ button. SharePoint will prompt that column is not indexed, do you want to index the column.
Click ok to index and it is good practice to index the column for better performance when you already know that column has unique values.
With this, SharePoint will enforce unique values on EmpId column. Users cannot save duplicate data in ‘EmpId’ column.

Rule 2>EmpId cannot have decimal values and should allow only positive numbers

In the list settings page, under columns section click the column ‘EmpId’ which will take us to column settings page.
Under ‘Additional Column Setttings’, there is small text box with Label ‘Min:’ under ‘you can specify a minimum and maximum allowed values:’ (Pointer 2 in the above column settings image)
Provide value ’1′ in the text box and click ok to save the values.

Now try to add a new item in the list and give empId as ‘-1′; try to save the data. SharePoint will give error message saying the ‘the value in the field must be greater than 1′.







So, our column is ready with unique values and positive values.
To implement the rule, not to allow decimal values; generally after seeing the column settings we can say that there is a dropdown list ‘Number of decimal places’ and select 0 in that. Of-course our assumption is true, but in reality this is not enforcement. SharePoint will only round the decimal values if we use that option.
For example if we select 0 decimal places and give the empId value as 1.2; it will be converted to 1 in the UI. But, in the backend we still has 1.2 as original data. Also, if we give value as 1.8 it will round to 2. So, this is not the option to enforce not to give decimal values.
Now comes our ‘Formula’ text box under Column Validation section. (Pointer 3 in the above column settings image)
In the Formula text box give the value as =EmpId=INT(EmpId)
In the UserMessage text box give the values as decimals are not allowed.
Now try to save new item with employee id ’1.2′. It will give error message decimals are not allowed

Rule 3> Employees of type ‘Vendor’ are not allowed to enter information

This is rule is more on the list column but not on single column.
Go to the list settings, click validation SETTINGS OPTION under General Settings category.
In the formula text box give =IF(EmpType=”Vendor”,FALSE,TRUE)
In user message give Vendors type is blocked currently
Now try to save new item selecting ‘Vendor’ in EmpType dropdown. It will give error message Vendors type is blocked currently

More Information

What ever the formula we provide, it should return true or false. If it returns true, the validation is success and if false it is a failure and gives error message.
Some more examples of formulas so that we can get familiar with syntax

=EmpId>10 //Allows only EmpId greater than 10

=IF(EmpId>10,TRUE,FALSE) //Allows only EmpId greater than 10, same as above but with IF condition.

=AND(EmpId>5,EmpId<10) //Allow EmpId to be in the range between 5 and 10

=IF(LEN(EmpId)>5,TRUE,FALSE) //Allows EmpId length greater than 5 digits only

=[EmpId]<> 11 //Restricts users not to give EmpId as 11

=[JoiningDate] < TODAY() //If JoiningDate is the date column, JoiningDate should be less than todays date

=IF(Cost>([Sell Price]-(Cost*(10/100))),FALSE,TRUE) //validaion to set Selling price should always be 10% more than cost price

Note: SharePoint does not allow regular expressions in validation formulas.