Monday, September 28, 2015

SharePoint 2013 Get distinct values of a column in a list or library


I am sure you would have come across the requirement
"Get distinct values of a column in a list or library"

That's why you are here.

What a typical developer usually does for the same requirement;
In jQuery / C#:
- Retrieve the list items
- Create an array object
- Loop all list items
- Compare column value with array
- Store the value in array if there is no match

* I think it is very easy to get distinct values if we use LINQ. I have not tried it.

As always, I came across one application page "filter.aspx" when I was investigating the SharePoint OOTB sorting behavior. :)

What it does? 
- It uses the query string parameters ListIdFieldInternalName, ViewId, FilterOnly and Filter
- Processes and displays a drop down with only distinct values of that column

It works for all columns. I tried with even People/groups column. It's working !!!!!

How to consume those values? [My thought process]
- Provide necessary values for below url

http://<site collection url>/_layouts/15/filter.aspx?ListId={<GUID>}&FieldInternalName=<Column's Internal Name>&ViewId={<GUID>}&FilterOnly=1&Filter=1

Example: https://sample.sharepoint.com/sites/dev/_layouts/15/filter.aspx?ListId={0717D149-0B6A-4657-8DFE-A7A06251F086}&FieldInternalName=Class&ViewId={E1A01D7E-32BB-4AA2-88FC-EE630003AF94}&FilterOnly=1&Filter=1

- Make an ajax call
- Parse and Read the HTML response

* Using jQuery you can easily read the distinct column values

If you want to see the HTML structure of the page, copy and paste the url with all values needed in browser. You shall view the response using browser's developer tool.

Update: Nov 22, 2016
----------------------------
This is my function to parse the ajax response and retrieve the distinct values.
Make sure you have a div element on your page with style="display:none" and ID="tempdiv"
Most importantly do not forget to change the querystring values on url

function getUniqueNames(){
var uniqueNames = [];
$.ajax({
 url:_spPageContextInfo.webAbsoluteUrl+"/_layouts/15/filter.aspx?ListId={0717D149-0B6A-4657-8DFE-A7A06251F086}&FieldInternalName=Class&ViewId={E1A01D7E-32BB-4AA2-88FC-EE630003AF94}&FilterOnly=1&Filter=1",
 method: "GET",
 headers: { "Accept": "application/json; odata=verbose" }
}).then(function(response) {
  $('#tempdiv').html(response);
  $('#tempdiv').find('SELECT option').each(function(i,v){
   if($(v).attr('value')!='')
    uniqueNames.push($(v).attr('value'));
  });
  $('#tempdiv').html('');
});


0 comments:

Post a Comment