UniqueFieldReceiver using CAML

A nice example of how a small requirement can be come a big SharePoint task is the unique field problem.

The requirement would be best explained as analogy to the unique constraint on relational databases. If you want a field to be unique you need to make sure, that each element that is inserted has a different value.

I solved this by creating an event receiver for events

  • SPEventReceiverType.ItemAdding
  • SPEventReceiverType.ItemUpdating

You would expect this to be straight forward. You take the SPItemEventProperties.AfterProperties SPItemEventDataCollection (because ItemAdding and ItemUpdating are synchronous events) and check if an item exists with this property. If this is the case you terminate the insert / update action.

This is not that simple. The only properties included in the AfterProperties SPItemEventDataCollection are those that are in the add (/HttpEncodedListName/NewForm.aspx) / edit (/HttpEncodedListName/EditForm.aspx) forms. So choose the field and set the properties of the field (SPField.ShowInNewForm = true, SPField.ShowInEditForm = true) accordingly.

SPField.StaticName == “Title” is a good choice.

The next thing to consider is that you of course need an elevated web or you will only get those items that the current user can see.

SPSecurity.CodeToRunElevated = 
    using(SPWeb web = properties.OpenWeb())
      // your code here

You need to make a difference between inserting and updating. Inserting is a tad easier.
All you have to do is send the caml query to the database asking whether an item exists.

public static bool Exists(SPList list, 
                        string fieldName, 
                        string fieldValue, 
                        string fieldType)
  string queryString = 
    string.Format("<Where><Eq><FieldRef Name=\"{0}\" />" +
                  "<Value Type=\"{1}\">{2}</Value></Eq></Where>"
  SPQuery query = new SPQuery { Query = queryString };
  SPListItemCollection result = list.GetItems(query);
  if(result.Count > 0)
    return true;
  return false;

For the updating event you have a little more to consider. First of all: Did a “true” update on the field itself take place or are the two values (before / after) identical?
In this case you need to take the ID of the item into consideration. Either you check before / after images or you correct the caml query accordingly. This does not work however for the inserting action, because then the ID of course does not exist yet.

  <FieldRef Name="ID" /><Value Type="Counter">{3}</Value>
    <FieldRef Name="{0}" /><Value Type="{1}">{2}</Value>

{3} should be set using the id of the existing item (which to ignore).
You can also work on the SPListItemCollection, which I do not recommend for performance reasons (keep communication data (between database and front-end server) as small as possible) though.

This is essentially it. Make an exception for lookup and datetime fields when comparing before and after images.

The rest should be straight forward.

Adding your new eventreceiver to lists is done like this:

public static void Add(SPWeb web, 
                       string assemblyFullName, 
                       string classFullName)
  SPList list = web.Lists["MyExistingList"];

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

%d bloggers like this: