Home > SharePoint > Prevention of duplicate entries based on two or more columns in a SharePoint list

Prevention of duplicate entries based on two or more columns in a SharePoint list

SharePoint 2010 has a lot of improvements compared to SharePoint 2007. One of this improvements is the possibility to create unique constraints on a column in a SharePoint list.

image

This constraint is valid only for one column, but sometime you need to ensure that your unique constraint includes two or more columns.

The best way to create a multi column unique constraint is to implement an event receiver for the list. (http://msdn.microsoft.com/en-us/library/ff728093.aspx)

To be clear: An event receiver is the best way to avoid double entries in your list because it called every time your list item is changed regardless of the origin of the change.

To implement an event receiver you need to be familiar with visual studio, and be able to deploy source code solution to your site collection. An event receiver can be implemented as sandbox solution. In this case it can be deployed to Office365 also.

Solution for business users (no code)

In this post I would like to demonstrate a solution for an multi column unique constraint which can be implemented by a business user, who is not familiar with visual studio or is not allowed to deploy a sandbox solution to the site collection.

This approach will work only if the user enters data through the SharePoint UI. The approach I will demonstrate will not work for updates through source code, or through data sheet view. If you need to cover all possible input channels you need to implement an event receiver.

In our case we have a list “Cust List” with two columns “Title” and “Additional Title”. Our goal is to implement a unique constraint including this two columns to prevent a normal user to save duplicate entries in the list.

Please note that there is a column called “temp” we will need this column during this lab to raise an error if the constraint is violated.

image

 

SharePoint 2010 allows you to customize edit and display forms for list item through InfoPath.

  1. Edit form in Infopath
    Click on “Customize Form” in the Ribbon.

    image

  2. Add secondary data source to your form
    To be able to check for existing entry with same values for “Title” and “Additional Title” we need to check the list before saving a new entry to it. For this purpose we need a "secondary data source” pointing to “Cust List”.

    To add a secondary data source to your form click on “From SharePoint List” button in the “Data” tab:
    image

    Type in the URL to the SharePoint site where your list is stored and click next.
    image

    Select the list, in my case it is “Cust List”, from the listbox and click “next”
    image

    Select only “Title” and “Additional Title” from the list, so the amount of data we will get from the secondary data source is as low as possible. This will reduce the time to check for a double entry and avoid freezing of the form.
    We need to select “Title” and “Additional Title” because we will make a query on this field to retrieve duplicate entries. Click “next”
    image

    We do not need to store a copy of our data for offline scenario because our form is hosted in the browser, so leave the checkbox empty and click “next”
    image

    Give your data source a name and uncheck the checkbox for retrieving data on form load, otherwise all entries from your list will be loaded to your secondary data source while form opens. In a large list scenario it will cause a delay and bad performance of the form.
    MAKE SURE YOU UNCHECK THE CHECKBOX. Click “Finish”
    image

  3. Creating a rule to check for duplicate entry
    After we’ve created a secondary data source to our list, we need to create the rule which will query the list for duplicate entries and prevent user to save actual item if it violates the unique constraint.

    Reminder: our constraint includes “Title” and “Additional Title” columns/fields.

    Select “Title” field in your form and click “Manage Rules” in the ribbon
    image

    Click on “New” and select “Action” from the drop down:
    image

    Give your rule a name and set the condition for the rule to run only if “Title” and “Additional Title” are not blank:
    image

    Add “Set field’s value” action to your rule. Select “Title” field of your secondary data source. Default InfoPath displays fields from “main data source”. To select a field from secondary data source click on “Show advanced view”
    image

    Select your secondary data source from the drop down at the top of the dialog and select “Title” from the “queryFields” node. Click “OK”
    image

    Now you should be back to “Rule details” dialog. Click on the button near “Value:” text box.
    image

    Click on “Insert Field or Group” and select “Title” from “main data source”
    Click “OK” until you get back to “Rule details” dialog.
    Click “OK” on “Rule details” dialog.
    image

    Your rule should contain one action:
    image

    Create same “Set field’s value” action for “Additional Title” field. After you’ve added your second action your rule should look as follows
    image

    Add “Query for Data” Action to your rule and select your secondary data source.
    Click “OK”.
    image

    Your rule should look as follows:
    image

    We’ve created a rule which queries our list for duplicate entries regarding our input in fields “Title” and “Additional Title”.

    We need to raise an error in the form if a duplicate entry was found by our rule.

  4. Raise an error if a duplicate entry was found
    To raise an error in the form we will use the “temp” field/column. We will update our rule we’ve created in “3.” to populate the field with the ID of the first entry of our secondary data source after we made a query to it.

    If there are no duplicate entries (query to secondary data source returned no items), the “temp” field will remain empty, otherwise it will contain an ID of the duplicate entry. We will create a “Validation” rule on the temp field to prevent user to save item if “temp” field is not empty.

    Select “Title” in your form and add another action to the rule we’ve created in “3.” Add a “Set field’s value” action.
    Select “Temp” as field and “ID” from secondary data source as value. Make sure you select the “ID” field from “dataFields” node. 
    image

    image

    Your rule should look as follows:
    image

  5. Copy the rule to “Additional Title” Field
    We need to make sure that the check is done after the user changes “Title” or “Additional Title” field. For now, the check is only performed after the user changes the “Title” field.

    With InfoPath 2010 we can copy rules from one field to another. Thanks god, and program manager of InfoPath who made a decision to implement this feature Smile.

    Select “Title” field on your form and click on the “Copy Rule” icon at the top of the rule pane:
    image

    Select the “Additional Title” field and paste the Rule. You need to update the rule on “Additional Title” field after you’ve copied it.

    After you rule has been copied to the “Additional Title” field the first action “Set field’s value: Title = Title” has been changed. InfoPath is to smart and updates the rule automatically to the field where the rules is copied to. This happens to actions and conditions. 

    We still need to copy “Title” from our “main data source” to the “Title” – Query parameter of our secondary data source. InfoPath makes changes to the rule which will result in wrong query, and our check for duplicate entry will not work if the user changes “Additional Title” field.
    image

    Change first action, and condition so your rule looks as follows:
    image

  6. Complete error handling
    Now we are checking for duplicate entries on both fields “Title” and “Addition Title”, we copy an ID of the duplicate entry to our temp field if we find one. Now we need to raise an error, and prevent user saving the form if a duplicate entry exists.

    To achieve this goal we need to create a validation rule for “temp” field.

    Select “temp” field.
    Click “New” button in the “Rules” pane and add “validation” rule.
    Set up the condition and for your rule and type in the “Screen tip”:
    image

    Now we’ve created a validation rule and a user will not be able to save a form which will cause duplicate entries in the list. Now we need to inform the user what kind of error prevent saving the form.

    Put “calculated value” field at the top of our form where we will display an error message if necessary:
    image

    Type in the error message you would like to present to the user. Make sure you place it inside “…” otherwise InfoPath will try to analyze your text as formula and display an error:
    image

    To make sure to display the error message only if a duplicate entry was found create a Formatting rule for the “Calculated value” field:
    image

  7. Publish your form
    image
    Verify your form. Insert couple of entries to your list. This approach will work for small lists and for large lists.
    image
Advertisements
Categories: SharePoint Tags: , ,
  1. Drew
    June 21, 2012 at 9:40 pm

    Andrej
    This worked perfectly, thanks for the detailed post!

    Drew

  2. Waldemar
    October 12, 2012 at 2:57 pm

    Brilliant! Thank’s a million for that post. Worked like a charm

  3. December 3, 2012 at 7:18 pm

    very informative post…

  4. January 28, 2013 at 8:00 pm

    This is designed for 2010. Can you forward me (or assist) with a 2007 solution?

    • January 28, 2013 at 8:41 pm

      Hi Eric,
      unfortunately I don’t have an environment with office 2007 any more. Which step differs in InfoPath 2007 from my explanation?
      Bg Andrej

  5. Mira Stamboliyska
    April 11, 2013 at 5:16 pm

    Fantastic post! I am new to SP and this was incredibly helpful – thank you so much for taking the time to write this out in such an easy to follow way! Worked great for me!

  6. jeb
    April 26, 2013 at 6:19 pm

    nice! this is awesome

  7. Amit
    January 4, 2014 at 12:37 pm

    Hi, I tried applying this on a date field. As in my objective is to ensure that the same entry is not made on the same date. I did not get any (got a ..) value when I selected the Date field from the Main path. Please reply it will be very helpful.

    • January 16, 2014 at 8:46 am

      Hi Amit,

      Even if you have selected the field in SharePoint to store date only there is a time fragment in the field which is not displayed. Date is stored as “2012-07-12T00:00:00” in SharePoint. If you have a date only field in InfoPath your value of the field is “2012-07-1” only. You need to add the time part to your query string. Add a text formula in your rule where you set the query values like this: concat(MyDateField, “T00:00:00”) this will result in propery query string and you will get items which contains this date only.

      bg Andrej

  8. AK
    March 25, 2014 at 5:23 pm

    Hi, this works perfectly fine using infopath but does not restrict duplicate entries in the datasheet view. Can you assist how to achieve this? Thanks in advance

    • March 27, 2014 at 1:52 pm

      The only way I can think of is to write an EventReceiver to validate the changes made by the user.

  9. Donelle
    July 28, 2016 at 4:48 pm

    This works like a charm for exact matches, but is there a way to prevent overlapping date periods?

  10. ranjani
    December 23, 2016 at 5:47 am

    Thanks a lot for detailed post . IT really helped

  11. Ross
    January 11, 2017 at 10:10 pm

    This works great, but I am having trouble with this with my “Combo-box” setups. I have a second field that is hidden, unless “other” is chosen then a user will enter in information. I have a workflow to add this back in to the connected list, but I would like to also check for duplicates against this. So, once the new item is added to the list, if a user pulls their last record to make a new record, they will no longer need to type in this field, but the other choices they fill in need to be checked again. Ex. Field1–>”other”, Hidden Field1.5-> “New” Item, Field2=1, Field3=2, Field4=3 all submitted. User Pulls record and changes Field1->”New”, so this record is technically the same as before (Field1=New,Field2=1, Field3=2, Field4=3), but using the rules you have outlined above, an error message will not show since The “other” field changed to the new item.

    Any ideas?

    Thanks,

    Ross

  12. Lul Woreta
    January 21, 2017 at 7:45 pm

    Wow wonderful explanation appreciated !!!

  13. kami
    February 15, 2017 at 1:37 pm

    Hi!
    sounds amazing! do you have a solution for 2013?

  14. Santhosh Kesavan
    February 20, 2017 at 11:34 am

    Awesome! It worked like charm even for 2013. Thanks so much!

  1. December 3, 2012 at 5:59 pm
  2. February 11, 2013 at 9:36 am

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: