Validation in SharePoint 2010

Most of the developers faced issues with column validations in SharePoint 2003/2007. It is really painful to write the validation using custom approach like JavaScript or jQuery (I don’t know how to do this one ). SharePoint 2010 provides the following validations in Out of the Box. Items cannot be saved until these validation criteria are met and custom messages, which can be set for specific validation failure cases, are displayed to the user.

• • Column Level Validation

  • List Level Validation

1. Column Level Validation

We are already familiar with some sort of validation in column level; Date and Time field doesn’t allow us to input general text; Number doesn’t allow us to input text value, moreover, we can set range of the valid values (Max, Min).

Somewhat similar, Column Validation is the new feature of SharePoint 2010 which allows user to validate the input against the validation formula.

Fig: Validation formula and custom user message

Formula language of MS Excel is used as formula syntax. If Formula is empty (No matter User message exist or not) there will not be any validation. User can customize formula as he wants. The default validation failure message may vary according to the data type: The validation formula has evaluated to an error, Column Validation Failed etc. However, we can modify this message. For example if we have a Text type column and which can contain only countries participating in the world cup, then using column validation we can obligate user to put only permitted value.

Fig: Validation failure message

We can put validation on the built-in column also but remember that no all column type support column validation. Supported column types are: Single Line of Text, Choice (single), Number, Currency and Date and Time.

Note that, if we define a validation on site level, then any list using that column will contain same validation.

Enforce Unique Values

This is new feature in SharePoint 2010 as seems to be the part of Column Validation. It obligate user to input unique value for that particular list through folder also.

Fig: Setting Enforce Unique Values

Attempt to add duplicate entry for that column results an error message “This value already exists in the list.”

Field type those support this features are: Single line of text, Number, Currency, Date and Time, Choice (single), Lookup (single) and Managed Metadata (single).

If we attempt to set this property for a field in the list without uniqueness in that field, SharePoint will show an error message.

Fig: Enforce Unique Values setting error

Object Model

SPField.ValidationEcmaScript -> Read only. Gets an ECMAScript function that corresponds to the field’s validation formula.

SPField.ValidationFormula -> Indicates the formula referenced by the field and is evaluated when a list item is added or updated. This is our actual formula which we write while creating validation.

SPField.ValidationMessage-> Gets or sets a message to display to the user if validation fails for this field.

SPField.EnforceUniqueValues-> Gets or sets a Boolean value that specifies whether to enforce uniqueness in column values. The default is false.

Ref: http://msdn.microsoft.com/en-us/library/ee552095(v=office.14).aspx

Field Schema Changes

New node <Validation> is added in Field schema if a column has a validation. It contains the user message in ‘Message’ attribute. ‘Script’ attribute will be available and contains Ecma Script of validation formula if it is not default (Default Ecma script for each field is function(x) {return 1}). And it contains the formula text as <Validation> value.

E.g.:

<Field DisplayName=”PNumber” Required=”FALSE” EnforceUniqueValues=”FALSE” Indexed=”FALSE” SourceID=”{58315844-0c36-4fee-aa99-f254cfc40b9e}” StaticName=”PNumber” ColName=”float1″ RowOrdinal=”0″ Percentage=”FALSE” Version=”2“><Validation Message=”Must be greater than 100.” Script=”function(x) {return SP.Exp.Calc.valid (SP.Exp.Node.f (‘GT’,[SP.Exp.Node.a(0),SP.Exp.Node.v(100)]),x)}”>=PNumber&gt;100</Validation></Field>

2. List Validation

This is another sort of validation which is directly related to list. Main advantage of this validation is; we can use different columns at a time to build our validation formula. For e.g. we obligate two columns to have equal value. Syntax and Field Type supporting validation is same as in column validation. Default message ‘List Item Validation Failed’ will be displayed on the case of validation failure. However, we can change this message.

Fig: List validation setting

Now when user doesn’t meet the criteria of above validation, we get the custom user message as shown below

Fig: Validation failure (list and column)

Unlike column validation where error is thrown by SharePoint, we will get our own validation failure message in library.

Object Model

SPList.ValidationFormula-> Gets or sets a formula that is evaluated each time that a list item is added or updated. String must be of length less than 1024.

SPList.ValidationMessage-> Gets or sets the message that is displayed when validation fails for a list item. The maximum length of a validation message string is 1024.

More info: http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.splist_members(v=office.14).aspx

Schema

Two new nodes are added in the List Schema; <Validation> with an attribute ‘Message’ which contains user message if any and validation formula as value; <ValidationDisplayNames> node which contains validation formula as value.

Eg.

<?xml version=”1.0″ encoding=”utf-16″?>

<List DocTemplateUrl=”" DefaultViewUrl=”" MobileDefaultViewUrl=”" ID=”{D6B41E9F-3DB4-40A2-8CD6-A2999E218C98}” Title=”PCustomList” Description=”" ImageUrl=”/_layouts/images/itgen.png” Name=”{D6B41E9F-3DB4-40A2-8CD6-A2999E218C98}” BaseType=”0″ FeatureId=”{00BFEA71-DE22-43B2-A848-C05709900100}” ServerTemplate=”100″ ……>

<Validation Message=”PNumber must be greater than 100″>=PNumber&gt;100</Validation>

<ValidationDisplayNames>=PNumber&gt;100</ValidationDisplayNames>

<Fields>

<Field ID……….

SPListDataValidationException Object Model

SPListDataValidationException->The exception that is thrown when data for a list field or list item fails validation.

ErrorCode->Gets an integer that identifies the exception. (Inherited from SPException.)

ItemFailure->Gets an SPListDataValidationException.SPFailure object that contains information about a list item validation failure.

FieldFailures->Gets a collection of SPListDataValidationException.SPFailure objects that contain information about field validation failures.

For more information refer: http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.splistdatavalidationexception_members(v=office.14).aspx

Issues

• • Column validation doesn’t use regular expression; it may not feasible in all cases requiring validation. For example, if we need the Text type column which accepts only GUID, then it may be irritating or impossible to create such validation.

  • · There is not IntelliSense as in Microsoft Excel. So, it is somewhat tedious to write validation formula.

  • 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. The column validation could be used only to compare with static and defined values e.g. checking if it is greater than the current date etc.

  • Sometimes, it may happen that even there is no formula error and custom user message, SharePoint displays its own default message. This is because of inappropriate formula (which might be always false).

  • When there is validation failure in document library, it will give SharePoint error rather than displaying our custom (OR default) message. Note that this issue exists in OM also. This seems to fix on official release of SharePoint 2010. http://social.msdn.microsoft.com/Forums/en/sharepoint2010general/thread/b25febd3-003a-4ffe-aea9-09293fd0f722

Fig 3: Column validation failure message in document library

• • For List validation, we can write only one validation formula.

    • • What if the column and list level validations are in conflict? Example – at the list level, you require that [Text1] = [Text2], but each column has its 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.

Limitations

Conflicts and Resolution on SharePoint

• • What if the list level validation includes columns not included in a particular content type? 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.

  • What if you have both column level validation and list level validation? The column level formulas will be evaluated first, then the list formulas