Validations In SharePoint

SharePoint column validations for SSN and Email

1. SharePoint validation formula for SSN (Social Security Number) number

Considering column ‘SSN’ as single line of text and length as 11 characters

Formula

=IF(Len(SSN) = 11,
AND(
MID([SSN],4,1)=”-”,
MID([SSN],7,1)=”-”,
CODE(MID([SSN],1,1))>47,
CODE(MID([SSN],1,1))<58,
CODE(MID([SSN],2,1))>47,
CODE(MID([SSN],2,1))<58,
CODE(MID([SSN],3,1))>47,
CODE(MID([SSN],3,1))<58,
CODE(MID([SSN],5,1))>47,
CODE(MID([SSN],5,1))<58,
CODE(MID([SSN],5,1))>47,
CODE(MID([SSN],5,1))<58,
CODE(MID([SSN],6,1))>47,
CODE(MID([SSN],6,1))<58,
CODE(MID([SSN],8,1))>47,
CODE(MID([SSN],8,1))<58,
CODE(MID([SSN],9,1))>47,
CODE(MID([SSN],9,1))<58,
CODE(MID([SSN],10,1))>47,
CODE(MID([SSN],10,1))<58,
CODE(MID([SSN],11,1))>47,
CODE(MID([SSN],11,1))<58
)
,
False)

This formula will enforce users to give proper SSN value. SSN (Social Security Number) will be of the format XXX-XX-XXXX where X is any number
Example of valid SSN number is 987-65-4320. Any other inputs like abc-65-4320 will give error message

2. SharePoint validation formula for Email

Considering column ‘Email’ as single line of text type.

Formula

=IF(ISERROR(FIND(“@”,[Email]) – FIND(“.”,[Email]) > 0),FALSE,
(LEN(LEFT([Email],FIND(“@”,[Email])-1))>0)
+(LEN(RIGHT([Email],LEN([Email])-FIND(“.”,[Email],FIND(“@”,[Email]))))>0)
+(LEN(MID([Email],FIND(“@”,[Email])+1,FIND(“.”,[Email],FIND(“@”,[Email]))-FIND(“@”,[Email])-1))>0)
+(ISERROR(FIND(” “,[Email]))=TRUE)
=4
)

This formula will enforce ‘Email’ value should be valid email
If I give adi@gmail.com as input it will allow, but if I give the value as adi@gmail , it will not allow

One point you have to observe in the formulas is
If we use a formula FIND(“@”, [Email]) will return index where ‘@’ is available in [Email] column input value.
But, if ‘@’ is not available in the input value then it will return error “The validation formula has evaluated to an error”.
So, we have to use ISERROR function so that our formula will execute only after proper inputs

Note that, these are column level validations as formula involves with column itself not with another column. So, validation rule should be provided at
List Settings > select column under columns > Add formula under Column Validation

You can refer list of ASCII codes here http://www.asciitable.com/

SharePoint column validation examples for Capital Letters

In this post we will cover few more SharePoint column validation examples implementing Capital Letters scenario with their formulas.
You can check my earlier post http://adicodes.com/capital-letter-validation-in-sharepoint/ with detailed example for capital letter validation

1. SharePoint validation – first two letters should be capital letters

Considering column ‘FirstName’ as single line of text type

Formula

=AND(
CODE(UPPER(MID(FirstName,1,1)))=CODE(MID(FirstName,1,1)),
CODE(UPPER(MID(FirstName,2,1)))=CODE(MID(FirstName,2,1))
)

This formula will enforce first two letters of ‘FirstName’ column should be in capital letters
If I give ADiseshu as input it will allow, but if I give the value as Adiseshu it will not allow

2. SharePoint validation – all letters should be in capital letters

Considering column ‘LastName’ as single line of text type and ‘LastName’ length as 5 letters.
For each digit we are adding code line to check. If we have more digits, then add for them also.

Formula

=AND(
CODE(UPPER(MID(LastName,1,1)))=CODE(MID(LastName,1,1)),
CODE(UPPER(MID(LastName,2,1)))=CODE(MID(LastName,2,1)),
CODE(UPPER(MID(LastName,3,1)))=CODE(MID(LastName,3,1)),
CODE(UPPER(MID(LastName,4,1)))=CODE(MID(LastName,4,1)),
CODE(UPPER(MID(LastName,5,1)))=CODE(MID(LastName,5,1))
)

This formula will enforce ‘LastName’ value should be in capital letters.
If I give DASARI as input it will allow, but if I give the value as Dasari it will not allow

Note that, these are column level validations as formula involves with column itself not with another column. So, validation rule should be provided at
List Settings > select column under columns > Add formula under Column Validation

You can refer list of ASCII codes here http://www.asciitable.com/

Capital Letter Validation in SharePoint

Hope my series of SharePoint validation articles helps everyone to resolve their issues right from basics to advanced.
You can check my other series of posts

How to implement validations in Detail http://adicodes.com/column-validations-and-list-validations-in-sharepoint-2010/

How to implement Date Validation based on a scenario
http://adicodes.com/date-validation-in-sharepoint/

In this post we will cover the below scenario. This scenario is one of the basics to understand more complex validations further

Scenario – First letter of the user input should be capital letter

I am using the following List Column and type in the example
FirstName – Single line of text

Formula and validation message setting

This is column level validation as formula involves with itself not with another column. So, validation rule should be provided at
List Settings > select column FirstName under columns > Add formula under Column Validation
(FirstName is the column we are implementing according to our scenario)

Validation Message

Formula

=CODE(UPPER(MID(FirstName,1,1)))=CODE(MID(FirstName,1,1))
MID(FirstName,1,1) – this gets the first letter from the user input. In our case, according to the above input (adiseshu), it gets ‘a’.
UPPER(MID(FirstName,1,1)) – converts ‘a’ to ‘A’
CODE(UPPER(MID(FirstName,1,1))) – get the ASCII of ‘A’

This ASCII code should be equal to the value given by the user. If they are not equal, it is an invalid input.
You can refer list of ASCII codes here http://www.asciitable.com/

Date Validation in SharePoint

In this post we will see how to implement SharePoint validations taking example of a scenario. I have observed many users got struck implementing some basic validations. Hope my series of SharePoint validation articles helps everyone to resolve their issues right from basics to advanced.
If you are new to implement validations in SharePoint, please do check this article to understand how we can implement validations http://adicodes.com/column-validations-and-list-validations-in-sharepoint-2010/.

Scenario – StartDate Should be greater than DueDate

I am using following List Columns and their type in the example
StartDate – Date and Time
DueDate – Date and Time

Formula

=DueDate>StartDate

Formula and validation message setting

Formula will be inserted from list settings > validation settings option. This is list level validation as formula involves with two different columns

Validation Message

List Cascade and Restrict delete

Introduction

SharePoint 2010 has introduced enforcing relationship behaviour on the list. We will see in this post what are the two behaviours that come up with the list

Consider the scenario that we have two custom lists; Category list and Product list.
Category list is the master(parent) list.Product(child) list has a lookup column which refers one column of Category list (say for example CategoryType column)

So, when we have data in Product list which refers Category list data; what behaviour the list should entertain is what that comes with enforcing relationship behaviour.
When trying to create a lookup column, following are the two options that come up in the RelationShip section.

Restrict Delete

If we select the radio button Restrict delete; that means that users cannot delete the data in Category list if the data is referenced.
They can only delete the data in the parent list only if the referenced data in the child list is completely deleted. So, if we want to delete an item in Category (ex:’Cat1′) we have to delete the Product list data that is using ‘Cat1′.
Sharepoint will give the following error if we try to delete until all references are deleted

Cascade Delete

If we select the radio button Cascade delete; that means that users can delete the data in Category list though the data is referenced.
But, once we delete the item in the Category list all the items that referred in the Product list will also be deleted.
While deleting it will give popup message which list is referencing its data and that data will also be deleted as in the following image

Column validations and List validations in SharePoint 2010

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.

You can refer this link for formula syntax

Formulas and functions

Conclusion

Hope you got some good understanding on one of the good feature of SharePoint 2010. Post a comment, if you are looking for any specific formulas