SharePoint: List Distinct (unique) values from a specific column in a list - SharePoint Development & Administration + InfoPath

Monday, March 14, 2011

SharePoint: List Distinct (unique) values from a specific column in a list

The following describes two methods to list distinct/unique values from a specific column in a SharePoint list.  An example where this may be useful is a menu system providing filter options for items in a list, using only values that are being used at the time.  


Display Unique Values using Data View Grouping (No Code):
Obtaining a distinct/unique list of values from a specific column in a SharePoint list can be achieved easily using a Data View Web Part, and by applying sort and group criteria to group items.  When grouping (group by) criteria of a Data View web part has been provided, the result is a distinct (unique) list of values from the particular column in the list that will be displayed as group headings.  Once the Data View is listing items grouped by the specified column, you can manually modify (remove cells/rows) the XSL template used to display the table to remove the actual list items from under each group (use Design mode in SPD to easily manipulate the default table layout).

The resulting Data View will list the group names (no items listed), which will be each unique value found in the specified column across all list items in the list.  Using the grouping functionality will ensure that only unique values are returned, as long as you sort by the same field to ensure that items are grouped correctly.  Once you have the list of group headings you can then modify the values displayed to be formatted as hyperlink or another control.  I typically use hyperlinks or buttons with calls to JavaScript functions that add Query String parameters to the current url.
 

Display Unique Values using XSL:
Selecting a list of unique values in a column that contains multiple instances of the same value can also be achieved using an XSL template and the “following” operator.  The following was achieved with the help of this article: http://www.bernzilla.com/item.php?id=333 , and displays a unique list of values from the Title column of items in the list.  


   <xsl:template match="/" xmlns:SharePoint="Microsoft.SharePoint.WebControls" xmlns:__designer="http://schemas.microsoft.com/WebParts/v2/DataView/designer" xmlns:asp="http://schemas.microsoft.com/ASPNET/20">
       <xsl:variable name="unique-list" select="/dsQueryResponse/Rows/Row[not(@Title=following::Row/@Title)]" />
       <xsl:for-each select="$unique-list">
       <xsl:value-of select="@Title" />
       </xsl:for-each>        
   </xsl:template>
</xsl:stylesheet>


Implementation Examples:
There are numerous circumstances where you need a distinct list of values from a specific column in a list, across all items.  A common scenario may be a dynamic menu system, that provides filter options for content on a page based on existing values in a particular column.

Implementing a dynamic menu system using this technique would involve selecting one or more columns from the main list in SharePoint to be used when listing filter options dynamically.  The menu should list all unique values from a specific field in the list, and be formatted in a way that allows the page to respond to a selection by filtering content.  


The simplest form of this would be a dynamic filter menu displaying options from one field in a list.  When an option is selected, the content on the page (from the associated list) is filtered based on the selected option.  Creating a multi-dimension menu to filter using multiple columns  is also achievable using this technique, where a sub-menu would also exist on the page.  In this case, when an option is selected from the main menu, the sub-menu options are changed (filtered) to show options relevant to the main menu selection.  Once an option is then selected from the sub-menu, the list or library on the page is then filtered using both fields.


More SharePoint Designer Training & Tutorials at SPDTraining.com
Share this article:
Stumble This Delicious
Delicious
submit to reddit
Facebook
MySpace
MySpace

3 comments:

Benno said...

Hi there,
I'm looking to implement a similar type of solution to what you have described in your second example, however I'm struggling to see where this code should be put? Any tips?
Cheers!
Ben

DanielS said...

Hi Ben,

The code goes inside the xsl:stylesheet element. Copy the xsl:template element in the example, and replace the existing xsl:template element on the page where the value for the match attribute is "/" (root).

Doing a search for: match="/" should help you find the existing element to be replaced.

Please let me know how you go, or if you would like further explanation.

Thanks

Benno said...

Thanks Daniel.
I think I have found the right place to make the change, though struggling a little on the success. Maybe I am not updating this in the right place, where exactly in SP Designer do make the change?

Getting one of these errors..

"Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Microsoft SharePoint Foundation-compatible HTML editor such as Microsoft SharePoint Designer. If the problem persists, contact your Web server administrator.
Correlation ID:2c1faf04-8176-4ebb-a438-9626944c5cb0"

Cheers,
Ben

Post a Comment