How To perform unrestricted List Item Validation

Problem

For some time I’ve been teased by the new SharePoint 2010 List Item Validation setting and the other day I had a problem that I thought could be solver by using ListItem Validation:
“An end-user adds or edits a List Item with business data that needs to be compliant with Business Rules”
Unfortunately I found List Item Validation inadequate in the first business case I could have use for it, but a Brainfart™ also provided me with an adequate workaround. 

Common Solution

List Item Validation can easily be configured in SharePoint 2010 Lists. For a great sample of how Validation can be configured have a look at Chack’s post. Also Be aware that you can use the full set of Calculated Field Formulas which is a powerful and not too document addition to common ListItem Validation. 

Problems with the common solution

As with many new features in the extensive development platform that SharePoint 2010 is there’s normally a catch. I actually wanted to perform validation on the following:
If Tender r.b. Submitted = Yes then Project Type and Proposal Responsible  can’t be empty.
image
Apparently there are many unsupported Column/Field Types for validation. So basically out of 9 fields only 4 of them can be used in a validation expression while any attempt to write column names of unsupported columns will give you an error:
One or more column references are not allowed, because the columns are defined as a data type that is not supported in formulas.”
image
At Least the following Column types can’t be used for validation:

  • Multipe lines of text
  • Person or Group
  • Lookup
  • Managed Metadata
Adequate workaround

When there’s a problem there’s normally also a solution. At first I wanted to see if I could use an event receiver on the content type / List Definition too do some sort of server validation and return a friendly error message. To my surprise List Item Validation is not part of the Server Object Model.
Another thing also surprised me. List Item Validation could have been implemented by Microsoft as a part of the 
eventing pipeline, but it’s actually performed outside of it and even worse it’s done after the Before Events (e.g ItemAdding or ItemUpdating)
image
Luckily that also became the solution to my problem. While you could perform validation and cancel the event in the before event which would give the user an ugly error message and make him loose all his changes. The real simple solution is to add a Field to the list which is invisible to the end user

  1. <Field Type="Boolean"
  2.        DisplayName="IsValid"
  3.        EnforceUniqueValues="FALSE"
  4.        Indexed="FALSE"
  5.        ID="{18ae8eb2-a353-445c-a4dc-2024884539bd}"
  6.        SourceID="{ca6f2a2a-53ed-49a9-8cbc-fbcc94400ec7}"
  7.        StaticName="IsValid"
  8.        Name="IsValid"
  9.        ColName="bit1"
  10.        RowOrdinal="0"
  11.        ShowInEditForm="FALSE"
  12.        ShowInListSettings="FALSE"
  13.        ShowInNewForm="FALSE"
  14.        ShowInVersionHistory="FALSE"
  15.        ShowInViewForms="TRUE"
  16.        ShowInDisplayForm="TRUE"
  17.        Version="1"
  18.        Overwrite="TRUE">
  19.   <Default>1</Default>
  20. </Field>

In any before event create your own Validation Method which only have your coding skill/imagination as limits but which will toogle the IsValid column to a false state.

  1. //Be aware: I use a lot of extension methods to hide som undesired SharePoint complexity
  2. //from business rules so this wan’t compile without those methods
  3. private static bool ValidEnquiry(SPItemEventProperties properties)
  4. {
  5.     if (properties.GetListItemBoolean(ColumnNames.Enquiries.TenderToBeSubmitted) &&
  6.         (properties.AfterProperties.IsNullOrEmpty(ColumnNames.Enquiries.DisciplineReference) ||
  7.             properties.AfterProperties.IsNullOrEmpty(ColumnNames.Enquiries.ProposalResponsible)))
  8.     {
  9.         properties.AfterProperties[ColumnNames.Enquiries.IsValid] = "0";
  10.         return false;
  11.     }
  12.     return true;
  13. }

In the validation settings formula enter =IsValid=”Yes” and a nicely formulated user message
image
End result when I forgot to enter proposal responsible:
image

Conclusion

Adequate workaround but requires coding, proper deployment unit’s etc but Highly required for medium complex scenarios. Enjoy

Advertisements