How to do column validation in SharePoint

If you use metadata in your list or library, you then rely on your users to enter proper information into respective columns. But how do we assure that users add proper information? Well, let’s not kid ourselves – we can’t. However, what we can do is reduce the errors by doing validation on the data using Excel-like formulas. With this post, I would like to explain how to do metadata/column validation in SharePoint.

There are two types of validation you can perform on a SharePoint list or library:

• Column Validation and
• List/Library Validation

Column Validation

Column Validation ensures that the data was entered correctly in a given column.

Use Cases/Formulas

• When the user enters the renewal date for a policy document, it has to be a future date, not a date in the past. Formula: =[Renewal Date] > TODAY()
• When the user enters the phone number, it must be exactly 7 digits long. Formula: =LEN([Phone Number])=7

How to create Column Validation

1. Create a new column on a list or a library or edit the existing one
2. In my example, I will create a Renewal Date column on a Policy Document Library. I want to ensure that the date entered is a future date.
3. At the bottom of the column screen, click on More options
4. Next, click on Column validation
5. Next, enter a formula into the Formula field and a User message that the user will see if the entered data makes the formula return a false result (i.e., Date is in the past). The User message is optional. Click Save.
6. This is an example of user experience when interacting with a column field

Limitations

• Unlike a Calculated Column, you can’t reference other columns for this type of validation
• In case you are syncing the library to your computer, it will make the library read-only on your computer/Windows Explorer. You can still make the edits/changes in the browser, but not in the synchronized library. You will also see a lock appearing over the library/documents you synchronize. Please reference this explanation from Microsoft here.
• Column validation only works for new data entries. In other words, if you entered data before adding a validation formula, all the previous entries will stay as-is and will not be checked for errors/validated.

List/Library Validation

List/Library Validation ensures that the data was entered correctly with respect to other columns in the list or library. A big limitation of the Column Validation I mentioned above is that you can not reference other columns. This is where the list/library validation comes into play.

Use Cases

• When the user enters the Renewal Date for a document or item, it has to be a date prior to the Expiration Date (another existing column in a library). Formula: =[Renewal Date]<[Expiration Date]
• When the user enters expenses into a form, and the user exceeds the approved daily expense limit of $50. Formula: =IF([Total Expense]/[Number of Travel Days]<50, TRUE, FALSE)

How to create list/library Validation

1. On a list or library, create your custom columns first
2. Navigate to Gear Icon > List/Library Settings
3. From the settings screen, click on Validation settings
4. On the next screen, specify the Formula. In my case, I compare two date columns I have in a library. Just like with the Calculated Column, you have the existing column panel to help you pick the existing columns for your formula. Next, type in the user message that will appear if the formula returns a false result (the user enters the wrong date). Click Save.
5. And this is the warning/error message that appears when the user tries to enter a date that does not match the successful outcome of the formula

Limitations

• Just like with Column validation, List/Library validation only works for new data entries. In other words, if you entered data before adding a validation formula, all the previous entries will stay as-is and will not be checked for errors/validated.
• When a new document is uploaded into the library that has a validation formula set, it checks out a document. The user needs to fill in the field in order for Validation formula to pass (TRUE result) and then manually check in the document so it will be visible to everyone else
• When I tested this functionality on a document library, I could not get the error message to appear when filling out a field in the document information panel – it just failed to populate the erroneous date, but did not return an error message. It only appeared in Quick Edit mode, as shown in the image above – most likely, this is just a temporary bug.

Additional reading

Despite my Jewish mother telling me that I am smart, my IQ is not high enough to know the proper syntax of the formulas to use in SharePoint. So every time I need guidance on SharePoint formula syntax, I refer to this excellent article from Mike Smith. He also got an excellent book on Amazon with the formulas and syntax to follow.