Preventing Duplicate SharePoint List Items

How do you prohibit SharePoint users from adding an item to a list that has the same Title (or some other column) value as an item already in the list? If you are working with SharePoint 2010, and your users are not using the Datasheet View to add items, then there is a four-step (no code) process for preventing duplicate list items:

  1. Customize the List Form – You can use Microsoft InfoPath to customize the forms that are associated with lists. If you have the right permission level, click the List tab in the ribbon and then, in the Customize List group, click Customize Form to start InfoPath Designer 2010.
  2. Customizing a List Form

  3. Add a Data Connection – In the InfoPath Designer, click the Data tab and add a data connection that retrieves items from your list. In the Data Connection Wizard, make sure you select the Title column (or whichever column for which you want to prevent duplicate entries). The ID column will already be selected.
  4. Adding a Data Connection

  5. Add a Data Validation Rule – In the InfoPath Designer, add a data validation rule for the text box that is bound to the Title column. The rule should contain an expression that checks the current Title value against values received from the data connection created in the previous step. In the expression, you should also confirm that the current ID is not already in the list, which will happen after the list item is saved.
  6. Adding a Validation Rule

  7. Publish the Form – After you have added the rule, use the Quick Publish icon at the top of the InfoPath Designer to publish the form back to the list.
  8. Publishing the Form

24 thoughts on “Preventing Duplicate SharePoint List Items

  1. hi
    this is a great article. but how do u exactly add that expression in infopath. currently, it is giving me an error. please assist
    thank you

    1. In the InfoPath Designer, click the text box that is bound to the Title column. In the ribbon, in the Home tab, click Manage Rules. In the Rules pane, click New and then click Formatting. Below the Condition header, click None. Add a condition where Title is not blank. Add a second condition and, in the first drop-down list, select The expression. In the text box to the right of the drop-down list, type the “count” expression that I included in the post. You will need to change “Tasks” to whatever data connection you created in the second step.

      Hope that helps…

  2. How about if you want to enforce validation on multiple fields. For example, in Employee Leave Table (or SharePoint List) I want SharePoint to validate on ‘Employee Number’, ‘Start Date’, ‘End Date’, and ‘Type of Leave’ have unique values to avoid duplicate records.

    To do this in RDBMS we issue an SQL command like this:

    CONSTRAINT leave_unique UNIQUE (‘Employee Number’, ‘Start Date’, ‘End Date’, ‘Type of Leave’)

    1. Hi, Jim.

      The same type of expression I used above for the Title field would also work for other fields in the form. With declarative logic in the InfoPath Designer, you could add a data validation rule for any or all of those fields, ensuring that they had unique values to avoid duplicate records.

      Hope that helps…

      Regards,
      David

  3. Hi David,
    I have bit different scenario. I have a custom list form which I have customized in InfoPath 2013. I have a drop-down list called “Order Status” which has different status such as Processing, Shipped, Cancelled and Hold. This is a browser based list form. There is a Submit button which simply submits the data to the list. I have this form added to a Page on my SharePoint 2013 site as a web part. The issue is when users submits this form via browser it duplicates the records. For instance, if they pick “Processing” as one of the values from the Order Status drop down list it duplicates this record. Not sure what is causing this to duplicate.
    Question for you is how to use your approach to my scenario

    1. Hi, Snehal.

      Without having access to your form template, it would be a bit of a guess to determine the exact cause of your duplicate items. However, if you are trying to apply the logic from my post, note that the “Order Status” column in your example is not a unique field. Instead, find the column (other than “ID”) that is used to distinguish one item from another, normally a name field of some type (e.g., “Title”). Then, add a data validation rule for that column similar to what is shown in Step 3. You will need to update the list name in that rule (change “Tasks” to the name of your list) and replace “d:Title” with whatever unique column you are using.

      I hope this helps.

      Regards,
      David

      1. I did everything but it doesn’t do anything. my Field name is INC. I replaced “Title” with INC as well..still it doesn’t do anything.

        count(xdXDocument:GetDOM(“Chat_Review”)/dfs:myFields/dfs:datafields/d:SharePointListItem_RW[d:Title = current() and d:ID != xdXDocument:get-DOM()//my:ID]) > 0

        1. Amit, the XPath statement in the validation condition is case-sensitive. So, “dfs:datafields” should be “dfs:dataFields”. Other than that, I would need to see your form template (and your corresponding list structure) to understand why the validation rule is not firing.

  4. I want to use the expression you created in a list where I want to allow no more than 2 of a specific timeslot on a given day. How could that be done?

    1. Hi, Alex.

      You could use a similar data validation rule as the one shown above, but bound to the StartTime column, or whatever column you are using to capture time slots in your list. Something like this would probably work (note that you will need to update the name of your data connection accordingly)…

      count(xdXDocument:GetDOM(“Calendar”)/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW[d:StartTime = current() and d:ID != xdXDocument:get-DOM()//my:ID]) > 2

      I hope this helps.

      Regards,
      David

      1. I’m still having some trouble with this. I went a different route so I could use “> 0” instead of “> 2.”

        I have a column called ValidateDT which is a concat of the columns Appointment Date, Appointment Time, and Resource. In my data connection called Appointments, I have ID and ValidateDT pulled.

        My expression is: count(xdXDocument:GetDOM(“Appointments”)/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW[d:ValidateDT = current() and d:ID != xdXDocument:get-DOM()//my:ID]) > 0

        But it’s not giving an error when I test adding two appointments at the same time on the same day with the same resource. Thoughts?

      2. David – I’m having a bit of trouble with this validation rule.

        I have a column called ValidateDT which is a concat of columns Appointment Date, Appointment Time, and Resource. My data connections is called Appointments. My rule shows:

        count(xdXDocument:GetDOM(“Appointments”)/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW[d:ValidateDT = current() and d:ID != xdXDocument:get-DOM()//my:ID]) > 0

        But it isn’t returning an error when I test booking more than one instance of the same appointment time for the same appointment date with the same resource. Please advise.

  5. hi David,
    I have a similar situation here. Could you please advise what goes in “Calendar” in the following expression? Is it the list name?

    count(xdXDocument:GetDOM(“Calendar”)/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW[d:StartTime = current() and d:ID != xdXDocument:get-DOM()//my:ID]) > 2

    Regards,
    Luke

    1. Hi, Luke.

      That would be the name of the data connection that InfoPath uses for that list. You can check the name by clicking the Data tab in the ribbon and then clicking Data Connections to see the full list of connections used by the form.

      Regards,
      David

  6. Hi Dave,

    Tried to insert your formula but i get the following back:

    the following expression is not valid:
    expected value: ]
    actual value: count (xdxDocument:GetDOM (“Reg Number”)/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW[d:Title = curent ( ) and d:ID != xdxDocument–>: 0

    What do you make of it?

    Regards
    John

  7. Hi, John.

    It looks like you have a few missing and/or stray characters in your expression (and the function “current()” was misspelled). See if the following works:

    count(xdXDocument:GetDOM(“Reg Number”)/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW[d:Title = current( ) and d:ID != xdXDocument:get-DOM()//my:ID]) > 0

    XSLT is case-sensitive, so syntax in the expression needs to be precise. I hope this helps.

    Regards,
    David

  8. Hi David –

    Thanks so much for posting this. I’m attempting to use the expression you spelled out in the instructions to validate against a field named “officeAndDate” which is a concatenation of two other different fields so that I only have to run the validation once. I am tied to using InfoPath 2010 and SharePoint 2013. I’ve written the rule expression as:

    count(xdXDocument:GetDOM(“Tasks”)/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW[d:officeAndDate = current() = xdXDocument:get-DOM()//my:officeAndDate]) > 0

    When trying to publish the form after having created the validation rule, I get an error in InfoPath stating that “The data source ‘Tasks’ referenced in the form template is not valid or cannot be found”. Any insight or advice?

    Thanks,
    Jess

  9. Hi, Jesse.

    Two things you might want to check out:

    1. Confirm that your secondary data connection is indeed named “Tasks”. If you updated that connection, the name may have changed.
    2. Your expression shows “d:officeAndDate = current() = xdXDocument:get-DOM()//my:officeAndDate”. Did you mean to have an “and” in that expression?

    Hope that helps…

    Regards,
    David

  10. Hi David,
    Thank you so much for your post, I had been scouring the internet to find this solution. However, when I follow your steps, nothing is happening. In the field I want to check against the existing SharePoint list (in my case “SMNumber”), the validation rule is not firing when I test. To test, I submit two forms with identical SMNumber values, and nothing happens to stop the submission (it allows duplicate creation).

    Am I missing something?

    count(xdXDocument:GetDOM(“SIEM Rule Creation Portal”)/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW[d:SMNumber = current() and d:ID != xdXDocument:get-DOM()//my:ID]) > 0

  11. Sorry for commenting in old post. I tried to set my field value to this formula but its always returning false. even if the value exists or not. Pls help
    count(xdXDocument:GetDOM(“Supplier”)/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/[d:Supplier_x0020_Name =current() and d:ID != xdXDocument:get-DOM()//my:ID]) > 0

  12. Hi Mr. Gerhardt,

    Your post is a post that keeps on giving! It took me a bit to figure it all out but based on your post and reading the comments I have it working like a dream now!

    For future use I created a quick reference guide so that I won’t have to reinvent the wheel on how it works. I do this since as I am not sophisticated like you on this ‘stuff’ (but I am a sophisticated quilter ;p) I still like to deliver elegant solutions to the best of my ability!!

    Thank you again!!!

  13. Is there a way to release the Prevention of the duplicate item lets say after 7 days have past from the prior item being submitted? and then the person could submit the item? or if it is possible just allow the duplicate but if it is a duplicate have an email sent? and then someone can verify the duplicate item

Comments are closed.