Column Validation in SharePoint 2010 !

The ability to easily validate the column values entered into list items is a welcome addition to SharePoint 2010. I'd like to walk thru 2 simple example s of how to use column validation, at the column level, then at the list level. I'll then point out some specific notes about this feature.

Simple Example #1 - Column Level

1. Create a new column on a list, and click "Column Validation":

2. Add a validation Formula, and a message that will display if validation fails:

3. Create a new item, and enter a value that will not validate. Click OK. You will see your failure message:

Simple Example #2 - List Level

1. In the List Settings, click on "Validation Settings"

2. Enter a validation formula to apply to this list. Click Save.

3. Create a new list item, entering in values that will not validate. Click OK. The item does not save, but the User Message doesn't appear either. I assume this is a bug, and it ought to be fixed before RTM.

Field Types

In this simple example we used a "Single Line of Text" field type. The following field types can be validated with the "Column Validation" feature:

• Single Line of Text


• Choice (single only)


• Number


• Currency


• Date & Time

Formulas

1. You can only compare column values to one another in a list level validation.


2. A validation formula at the column level cannot include any other columns besides itself. For example, [Column1]>[Column2] is an invalid formula and SharePoint will not allow it to be used at the column level. In this case, you want to use list-level validation.


3. There is only one formula available at the list level.


4. The formula syntax is similar to that used in Calculated Columns

There are a few dozen functions available - I have not tried every one. One interesting thing to note is that SharePoint 2010 would not allow me to use the TODAY function, even when validating a Date field. [DateColumn]>TODAY is not a valid validation formula.

Conflicts

1. What if you have both column level validation and list level validation?




1. The column level formulas will be evaluated first, then the list formulas

2. What if the column and list level validations are in conflict?




1. Example - at the list level, you require that [Text1] = [Text2], but each column has it's own validation; [Text1]="AAA", and [Text2]="BBB". In this case, it will be impossible to actually submit a list item. The column validations are evaluated first, but if the values validate here, they will of course fail the list validation.

3. What if the list level validation includes columns not included in a particular content type?




1. If a column used in the list formula isn't available in the current content type, validation will always fail. This means that if you have multiple content types in your list, you should not validate at the list level for a column that is not included in all content types on the list. These columns can only be validated at the column level.

Site Columns

1. You can set validation on custom columns of the field types listed above


2. You can override the validation of a site column at the list level

Items To Investigate

1. How does validation behave with page fields in a Pages library on a publishing site?


2. How does validation behave in Office client applications?


3. Is validation available in the Site Directory? Does it work at site creation time?


4. Can we use regular expressions in a validation formula?