SharePoint 2010 List Improvements – Column Validation and List Validation Settings !

Another great improvement in SharePoint 2010 is that now you can specify formulas that validate the columns based on other columns!

Let me take you through an example.

Here is a simple list called Scores

It stores the student scores for Paper 1 – Paper 5 , total score and the result. The scores are values between 1 to 100.

[ This is still doable without any formulas by setting the maximum and minimum values for the Number column. But I am using this example to illustrate the Column Validation. But there is still one advantage of using validation than using the maximum and minimum setting :) ]

Let us add a new item:

Looking at it, it is obvious that Paper 2 has a score of 150 and is invalid. So, we should be showing some error messages so that users get notified that the information entered is invalid.

Column Validation

In SharePoint, now columns have a new section called Column Validation where you can enter formulas (similar to Excel formulas) to pass validation.

You can enter the formula and the corresponding message that you want to display to the users if the validation fails. [You can customize the error message the way you want if you this validation than using the maximum and minimum settings :) ]

The formula is pretty similar to Excel. I also remember seeing some SharePoint functions (in the early builds), but the help is currently unavailable so we have to wait till the public beta is released.

Remember, this is a column validation and not list validation, so if its created at the site level, it will apply everywhere.

List Validation

You can do the same thing at the list level. You can access this in the List settings page:

The only difference is now you know what are the columns you would be validating against.

Validation in action

Here is validation in action:

Notice how it changes the focus and also displays the error message below that particular column.

Validation via code

For the developers out there, here is how you can set the validation formula and message via object model:

using (SPSite spSite = new SPSite("http://site"))
{
    using (SPWeb spWeb = spSite.RootWeb)
    {
        SPList lstScores = spWeb.Lists.TryGetList("Scores");

        if (lstScores != null)
        {
            SPFieldNumber fldPaper1 = 
                       lstScores.Fields.GetField("Paper 1") as SPFieldNumber;
            fldPaper1.ValidationFormula = 
                       "=AND([Paper 1]<=100,[Paper 1]>0)";
            fldPaper1.ValidationMessage = 
                       "Invalid score entered. Please enter a value between 1-100."; 
            fldPaper1.Update();                       
        }
    }
}

 

Note: Lists.TryGetList is a new method which you can safely use to get a SPList object. If the list doesn’t exists, it returns null.