Detecting Duplicate List Items Using XSLT

A requirement came up the other day, for a quick and easy way of detecting and displaying duplicate items in a SharePoint List.


SharePoint 2010 has a ‘unique column' or ‘unique column constraints' feature which can enforce uniqueness on values stored in a list or library column, effectively creating additional keys on the List.

Although this can be a very useful feature (especially when creating/implementing new SPLists), there are still occasions when ‘unique column constraints' cannot be (or in this case, hadn't been) implemented on a given SharePoint List due to the data being stored, but you still need a way of returning a set of ‘unique values' from the given set of data.


For example:
If you needed to store the following data in a SharePoint List ... would be able to do so and implement a ‘unique column constraints' on the Title (or Name) column without a problem.


However, if you needed to store the following data in a SharePoint List ...

you wouldn't be able to implement a ‘unique column constraints' on the Title (or Name) column.


But might still want to display a unique list of Names...

Luckily Marc D Anderson posted ways of achieving this (along with subtotals) with the use of some XSLT

Showing Subtotals in a DVWP, Sorted by the Subtotals - Part 1

Showing Subtotals in a DVWP, Sorted by the Subtotals - Part 2

Showing Subtotals in a DVWP, Sorted by the Subtotals - Part 3

This is achieved by "sorting" the results by the column in question (in this case "@Title" aka 'Name') and then using the ‘ddwrt:NameChanged' function to check if/when the value changes.

Now this approached works well, assuming that you only want to display/check for duplicate values that are stored in a single column.

If we add the other columns to the output, its limitations quickly become apparent.



In addition, this particular requirement needed to check for duplicate items based on values stored across two (or more) columns ..

..and simply flag them as "duplicates" without making any other visual changes (such as ‘sorting')


Luckily this can be achieved with the use of the some additional XSLT

First declare the following variables within the "dvt_1.rowview" xsl template 

<xsl:variable name="currTitle" select="@Title" />
<xsl:variable name="currDateValue" select="@DateValue" />
<xsl:variable name="hasMatchingrows" select="$Rows[@Title=$currTitle and @DateValue=$currDateValue]"/>


The "hasMatchingrows" variable can then be used to perform the check for "duplicate items"

<xsl:if test="count($matchingrows) > 1">
(<img src="_layouts/images/warn16.gif" border="0" /> Duplicate!)




Furthermore, if we declare an additional XSLT variable, this can also be used in conjunction with the ‘ddwrt:NameChanged' function

<xsl:variable name="newCurrDateValue" select="ddwrt:NameChanged(string(@DateValue), 0)"/>
<xsl:if test="string-length($newCurrDateValue) > 0">
<xsl:value-of select="$currTitle "/> - 
<xsl:value-of select="$currDateValue"/> - Total:
<xsl:value-of select="sum($hasMatchingrows[current()]/@NumberValue.)"/>




One last thing to mention, remember that all XSLT transformations occur on the client.
It does NOT affect the SPQuery being submitted or the XML data set that is returned.

Therefore, each of the XSLT  approaches mentioned above will only process

  1. the data retuned by the underlying "List View" or SPQuery
  2. the current ‘page set' of data

So if your "paging" is set to "Display in sets of 5 or 10", you will probably need to increase it (or alternatively perform some appropriate sorting/filtering) to ensure duplicates records DO NOT cross multiple ‘paged' results - otherwise some duplicates may not get flagged accordingly.

(You may want to set paging to "Display All Items" wherever possible, as this will ensure all XML records returned from the SPQuery are made available to the XSLT)



Published by aobi


No Comments