List Event Handlers In SP 2013 To Perform Duplicate Check

List Event Handlers In SP 2013 To Perform Duplicate Check

Posted by Sophina Dillard on Friday, 22 November 2013 0

While implementing practical solutions as a SharePoint consultant, the need to perform duplicate checks of different lists is a very common scenario.

Here is a real world example you may come across. Your client needs two lists, one to store regional information and one to store country specific information. The regional list, along with different information pertaining to a region, will also store an acronym for that region (e.g. EMEA, APAC, etc.). Similarly, the country list also will store the country acronym (FR, RU, IN, etc.) among other relevant country information. And this acronym data will be used in many places to accomplish many business requirements.

In most cases, we can easily utilize the OOB concept to validate the uniqueness of the columns. But to validate across different lists we need to provide some customization. We can achieve this in various ways.

In this article we will handle this validation through the event handler. We are assuming that a project from a SharePoint template is already created.

As shown in the image below, firstly we’re going to add an event receiver, select the list to which the event handler needs to work , check the item added and item being updated (as we need to validate during edits as well).

Please repeat this for the regional list as well.

Now we will write our validation code in the event receiver. Let us write for the “Adding” scenario first. In this code we will check if the acronym exists in the other list, if yes, we will cancel the addition. The code will be as shown below. Please update the column names as appropriate.

public override void ItemAdding(SPItemEventProperties properties)
        {
            base.ItemAdding(properties);
           
           
            properties.AfterProperties["Country_Acronym"] = ((string)(properties.AfterProperties["Country_Acronym"])).Trim();
            string newAcronym = ((string)(properties.AfterProperties["Country_Acronym "])).Trim();

            if (!CheckDuplicateAcronym(properties.List, properties.ListItemUniqueId, newAcronym, "Ctry_Acronym", "Text"))
            {
                //check in Country Profiles list
                SPList LegalEntityProfilesList = properties.Web.Lists.TryGetList("Country Profiles");
                if (CheckDuplicateAcronym(LegalEntityProfilesList, new Guid(), newAcronym, "country", "Text"))
                {
                    //cancel the event with error
                    properties.ErrorMessage = "Acronym already exist";
                    properties.Status = SPEventReceiverStatus.CancelWithError;
                    return;
                }
            }
        }

The generic duplicate checking function
public static bool CheckDuplicateAcronym(SPList List, Guid ItemId, string NewAcronym, string AcronymFieldName, string AcronymFieldType)
        {
            bool bAcronymExists = false;
            try
            {
                SPQuery query = new SPQuery();
                query.Query = @"<Where>" +
                    "<Eq>" +
                    "<FieldRef Name='" + AcronymFieldName + "' />" +
                    "<Value Type='" + AcronymFieldType + "'>" + NewAcronym + "</Value>" +
                    "</Eq>" +
                    "</Where>";

                SPListItemCollection items = List.GetItems(query);
                if (items != null)
                {
                    if (items.Count > 1)
                    {
                        bAcronymExists = true;
                    }
                    else if (items.Count == 1)
                    {
                        bAcronymExists = !(items[0].UniqueId == ItemId);
                    }
                    else
                    {
                        bAcronymExists = false;
                    }
                }
            }
            catch { }
            return bAcronymExists;

        }

The code is written in such a way that you can reuse this function for any list or for any field type by passing the relevant parameters. Even this function will take care of the updating scenario duplicate validation.
Next we’ll write the updating part of the code, as shown below. In the updating scenario we will pass the unique ID of the item rather than new GUID which we passed in the above adding scenario.
public override void ItemUpdating(SPItemEventProperties properties)
        {
            base.ItemUpdating(properties);
            properties.AfterProperties["Country_Acronym"] = ((string)(properties.AfterProperties["Country_Acronym"])).Trim();

            string newAcronym = ((string)(properties.AfterProperties["Country_Acronym"])).Trim();
            if (0 != string.Compare(newAcronym, properties.ListItem["Country_Acronym"].ToString(), true))
            {

                if (!CheckDuplicateAcronym(properties.List, properties.ListItemUniqueId, newAcronym, "Ctry_Acronym", "Text"))
                {
                   
                    SPList LegalEntityProfilesList = properties.Web.Lists.TryGetList("Country Profiles");
                    if (CheckDuplicateAcronym(LegalEntityProfilesList, new Guid(), newAcronym, "country", "Text"))
                    {
                        //cancel the event with error
                        properties.ErrorMessage = "the Acronym value already exist";
                        properties.Status = SPEventReceiverStatus.CancelWithError;
                        return;

                    }
                }
                else
                {
                    //cancel the event with error
                    properties.ErrorMessage = "the Acronym value already exist";
                    properties.Status = SPEventReceiverStatus.CancelWithError;

                }
            }

        }

That’s it! This event receiver can now be reused for duplicate validation of any number of lists.

Authored by:

Alvin Ubiera is a Microsoft SharePoint Technologist at the Houston-based business software provider, Rand Group and is a regular contributor to Dynamics 101. He hails from the sunny Dominican Republic and has varied experience in architecture, web development, and SharePoint.