In SharePoint Lists and libraries, we have multiple metadata columns which are used for taking inputs from the users. We need some actions for checking and verifying the submitted data  for accuracy on the list form. We call them field validation where we can mark few fields required and can have few fields types where chances of invalid entries are very low like choice field, Date field, people picker etc.

There could be a custom requirement for validations on text fields, like validation on a text field. Today I am taking example of a Student ID number which will have a specific format “NAAANNNNN” (N-Number and A-Alphabet) and we will apply a custom field validation on it.

Column Validation

There are two types of validations available in SharePoint list, Column Validation and List Validation. I will discuss about List based validation in next blog post.

I have created a list for Students, added few columns including Student ID column. I have added Student ID as single line text column.

Now you need to Edit the list column. You can go to list settings à click on Student ID column or can also access it directly from list view as shown below:

Add Custom Validation

Scroll down on column settings and go to column validation, add the validation formula there, in my case I have used the below column validation.

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

Add the user message and save the changes on the list

Test Validation

Now add a new item to the list and if entered value is not in proper format, you will get the validation failed message on the list form.

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