none
Checking for duplicates in a SharePoint list RRS feed

  • Question

  • After advice here, I have adopted a hybrid approach to the database we use to track children's progress (using annotated photographs) in our school. The "working" database is actually pretty simple only requiring one table to be updated each week. That is the table that I have converted into a SharePoint list and linked to in the front end. However, exporting it to SharePoint removes the primary key (consisting of two fields) and inserts an autonumber key. As a result, I have to check for duplicates as each record is saved. Not being all that clever at Access stuff, I do this via a duplicates query. I allow the record to be saved, run the query, count the number of records returned, trap error 3021, and, if a duplicate appears, delete it.

    Is there anything desperately wrong with this approach?

    Best - Anthony

    Tuesday, September 8, 2015 9:54 AM

Answers

  • Okay, if you're adding the new record via Access, you can check for duplicate values using the DCount() function. For example:

    If DCount("*", "ListName", "photoID=" & Me.photoID & " And ChildALDTermID= " & Me.ChildALDTermID) > 0 Then
    'duplicate
    Else
    'not duplicate
    End If

    Hope that makes sense...

    • Marked as answer by AnthonyMA Sunday, September 13, 2015 2:27 PM
    Tuesday, September 8, 2015 5:25 PM

All replies

  • Hi Anthony. I suppose it could be streamlined to avoid doing double work of saving the record first, then checking it was supposed to be saved in the first place or not, and then deleting it when it wasn't supposed to be saved at all. What makes the record a "duplicate?"
    Tuesday, September 8, 2015 3:24 PM
  • The combination of photoID and ChildALDTermID must be unique in the table. Originally these two fields made up the primary key. The substitution of that primary key by a random autonumber field means that duplicates can now be created and that is what I have to avoid.
    Tuesday, September 8, 2015 4:49 PM
  • Okay, if you're adding the new record via Access, you can check for duplicate values using the DCount() function. For example:

    If DCount("*", "ListName", "photoID=" & Me.photoID & " And ChildALDTermID= " & Me.ChildALDTermID) > 0 Then
    'duplicate
    Else
    'not duplicate
    End If

    Hope that makes sense...

    • Marked as answer by AnthonyMA Sunday, September 13, 2015 2:27 PM
    Tuesday, September 8, 2015 5:25 PM
  • Thanks so much. Forgive my tardiness in replying - new academic year causing work overload :-)
    Thursday, September 10, 2015 11:07 AM
  • Hi Anthony. Not a problem. Let us know how it goes... Cheers!
    Thursday, September 10, 2015 3:07 PM
  • Yes, that's much more elegant. Thank you very much.
    Sunday, September 13, 2015 2:27 PM
  • Hi Anthony. You're welcome. Glad to hear it worked out for you. Good luck with your project.
    Sunday, September 13, 2015 3:22 PM
  • FYI, one approach in database design in cases like this is to create a compound index in the table definition on the two columns and to set a unique constraint on the index. That way, insertions are rejected if the incoming data is non-unique.

    You might still want to do the uniqueness check in code as the DB guy suggests to create a better user experience, but having a constraint at the table level ensures that the data is always correct even if it is added outside of your program logic.

    I believe Sharepoint 2010 and newer supports compound indexes with up to two columns. What I don't know is if it also supports making the compound index unique. But you could investigate this approach if it makes sense for you.


    • Edited by Gary Voth Monday, September 14, 2015 2:48 PM
    Monday, September 14, 2015 2:47 PM
  • Thank you Gary. I haven't investigated this in depth; I assumed that when the Access routine that exports the table to SharePoint resulted in the demolition of my primary key, that meant no cigar. SP also writes to an "issues" table in which it says "SharePoint does not support unique indexes on any column other than ID"
    Monday, September 14, 2015 2:58 PM
  • "SharePoint does not support unique indexes on any column other than ID"

    Unfortunately, that is not entirely true. SharePoint allows any column, with certain requirements, to have a unique index. However, I am not sure that it allows multi-column indexing like Access does. Just my 2 cents...
    Monday, September 14, 2015 3:02 PM
  • Yeah, I've seen the same error at times. I wonder though if that could be a limitation of the upsizing wizard left over from earlier versions? I guess it would be worth looking at the list directly or with Sharepoint Designer.  (I've experimented with this via Office 365, but I'm not really a Sharepoint guy...)

    Edit: According to this article, SharePoint does support compound indexes, but possibly only for internal use. Here is the relevant quote (note the part where they can't be utilized by views). I'm not clear how "metadata navigation" relates to how Access uses SharePoint:

    • "Indexes are important for large lists. In SharePoint Server 2010, you can now create compound indexes. Compound indexes are useful when queries are commonly performed on two columns because a query on just one column might not be selective enough. Compound indexes are not utilized by views. However, they are utilized by metadata navigation. When a throttling condition occurs, the metadata navigation logic can try again and use applicable compound indexes and single indexes for the chosen filter conditions to find complete or partial results that satisfy the query."


    • Edited by Gary Voth Monday, September 14, 2015 6:30 PM
    Monday, September 14, 2015 3:46 PM