SharePoint List Validation


I had a need to be able validate a date against another date on a list when it was created, basically I wanted the second to date to be on or after the first date. Traditionaly, this sort of thing was handled through event receivers attached to the list, but it always felt like overkill.

It was only when looking through the List Settings page that i noticed the Validation settings section that i’ve never really looked at before. This section allows you to create a Formula (think Excel) using multiple colums. The formula is validated at list entry time and if the formula returns false, the user message you specify is displayed to the user.

My particular validation was around ensuring that the Position End Date (if populated) was on or after the Position Start Date, to do this through the Validation Settings page, I add to enter the following formula:

=OR([Position End Date]>=[Position Start Date],(COUNT([Position End Date])=0))

This is showing an OR condition, where the Position End Date must be on/after the Position End Date OR the Count of the number of entries in the Position End Date is 0. This was the only way I found of checking whether a date had been emptied as empty string or null didn’t seem to work, presumably because of DateTime type constraints.

This worked fine for manually configuring validation on lists that already exist, but if you want to do this at list provisioning time, you can add it in the list schema just after the ContentTypes node, just like this:

    <ValidationMessage=The position end date must be on or after the position start date>

=OR(ExpPositionEndDate >= ExpPositionStartDate,(COUNT(ExpPositionEndDate)=0))

</Validation>

Note, that you have to use the internal name for the columns when configuring through the schema.xml.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s