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.

Scenario

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.

Column Validations

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

Application start date must be greater than Today’s date. Enter below formula for Application Start Date column:

=StartDate>TODAY()

Email Verification

Enter below formula and a validation message for Email field:

=IF(LEN(Email)<=0,TRUE,AND(ISERROR(FIND(" ",Email,1)),IF(ISERROR(FIND("@",Email,2)),FALSE,AND(ISERROR(FIND("@",#NAME?,FIND("@",Email,2)+1)),IF(ISERROR(FIND(".",Email,FIND("@",Email,2)+2)),FALSE,FIND(".",Email,FIND("@",Email,2)+2)&lt;LEN(Email))))))

Custom Text Format Validation

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"))

List Validation

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.

Adnan, a distinguished professional, boasts an impressive track record as a Microsoft MVP, having achieved this prestigious recognition for the eighth consecutive year since 2015. With an extensive career spanning over 18 years, Adnan has honed his expertise in various domains, notably excelling in SharePoint, Microsoft 365, Microsoft Teams, the .Net Platform, and Microsoft BI. Presently, he holds the esteemed position of Senior Microsoft Consultant at Olive + Goose. Notably, Adnan served as the MCT Regional Lead for the Pakistan Chapter from 2012 to 2017, showcasing his leadership and commitment to fostering growth within the tech community. His journey in the realm of SharePoint spans 14 years, during which he has undertaken diverse projects involving both intranet and internet solutions for both private and government sectors. His impact has transcended geographical boundaries, leaving a mark on projects in the United States and the Gulf region, often collaborating with Fortune 500 companies. Beyond his roles, Adnan is a dedicated educator, sharing his insights and knowledge as a trainer. He also passionately advocates for technology, frequently engaging with the community through speaking engagements in various forums. His multifaceted contributions exemplify his dedication to the tech field and his role in driving its evolution.

Leave a Reply