I have written couple of blog posts on column validation on SharePoint List and Libraries. Validations are very important when you need clean input from the user on SharePoint lists. Till SharePoint 2016, it was easy to use custom list forms using SharePoint Designer and we can add custom validations on input controls, or some time based on business requirements, organizations were using Infopath forms to take user input with proper validation.
Today in this blog post I will be showing you how to apply multiple validations on list form with the help of out of the box column and list validation options.
I have a created a SharePoint list for Students and have few columns:
- Full Name (text field)
- Email (text field)
- StudentID (text field)
- Application Start Date (Date field)
- Application End Date (Date field)
I will apply Date validation, Date compare validation, email format validation and custom validation for Student ID.
For applying each column validation, you need to list settings, click on field name under columns and scroll down to add custom validation formula section. And add below validations
Application start date must be greater than Today’s date. Enter below formula for Application Start Date column:
Enter below formula and a validation message for Email field:
For StudentID, we have a specific format “NAAANNNNN” (N-Number and A-Alphabet). Use below formula:
=AND(LEN(Student ID)=9,ISNUMBER(VALUE(MID(Student ID,1,1))),NOT(ISNUMBER(VALUE(MID(Student ID,2,3)))),ISNUMBER(VALUE(MID(Student ID,5,5))),AND(MID(Student ID,2,1)>="A",MID(Student ID,2,1)<="Z"),AND(MID(Student ID,3,1)>="A",MID(Student ID,3,1)<="Z"),AND(MID(Student ID,4,1)>="A",MID(Student ID,4,1)<="Z"))
Unlike calculated columns, we cannot have multiple field comparison possible in column validation but we can do this using List Validation Settings. Go to list settings and click on Validation settings under General.
Here we will compare that Application End date must be greater than application start date, you can select fields from the Insert Panel.
=[ Application End Date] > [Application Start Date]
Now your validations are complete test your application to verify if all the validations are working properly.
When you test the form verification, it will validate the list verification first and then will check for the column validations. I have added validation a few controls for SharePoint list, you can add multiple validations as per your and can modify the formulas as per your requirements.