Many a times in a Sharepoint list we want to have one of the columns to act as a unique column. But there is no easy way to do it.
There are a couple of ways to make this happen,
Use a custom field which behaves like a unique column.
Use an event handler to check for uniqueness.
I normally prefer the use of event handler for this purpose. Event handler gives an easy way to make any or many column in a list to behave as if they can contain only unique data. Below is an example of the code required in the event handler.
const string _QUERY = @"<Query><Where><Eq><FieldRef Name""{0}"" /><Value Type=""Text"">{1}</Value></Eq></Where></Query>";
public override void ItemAdding(SPItemEventProperties properties)
{
using (SPWeb web = properties.OpenWeb())
{
/*get the current list*/
SPList list = web.Lists[properties.ListId];
string columnName = "";
foreach (SPField fld in list.Fields)
{
if (fld.Description.Contains("[$UNIQUE$]"))
{
columnName = fld.InternalName;
break;
}
}
if (properties.AfterProperties[columnName] != null)
{
string currentValue = properties.AfterProperties[columnName].ToString();
SPQuery _query = new SPQuery();
_query.Query = string.Format(_QUERY, columnName, currentValue);
SPListItemCollection itemsWithSameValue = list.GetItems(_query);
if (itemsWithSameValue.Count > 0)
{
properties.Cancel = true;
properties.ErrorMessage = "There is already an item with the same value(" + currentValue + ") for the column / Field(" + columnName + ") in this list.";
}
}
}
}
This will create an event handler for the adding event and stop any new addition of record if the value already exists. Remember to make this work properly, you will need another event for updating event to stop user from adding same value by editing a record.
thanks Vikram