3

As many other people on this site seem to need, I'd like to get an array (or equivalent) of the distinct values in a field for a list. The canonical way of doing this is to retrieve the entire field and then iterate. However, my list is 40000 text entries long. Just retrieving the entire field takes around 5 minutes, and of course, locks the list in the process.

However, there's DEFINITELY a faster way to do it. Sharepoint itself does this when you are viewing the list and click on the filter arrow (displays a list of unique choices) in a few seconds for the same list). Does anyone know what call is being issued when either clicking the filter arrow, or the "Show Filter Choices" button (both of which populate an exhaustive list of unique values very quickly) so I can call it independently from JS?

It looks like when using "Show filter Choices", the list page is reloaded with the &Filter=1 appended to the URL. Can anyone guide me through the process of building a minimal URL for an arbitrary list and parsing to identify any of the filter fields? With a list the size I'm working with, it'll probably be faster to make the HTTP request AJAX style and then pull out the values that have been calculated for the interface.

  • You should be able to Fiddle the network traffic that occurs when that filter is clicked and find the call yourself. telerik.com/fiddler – Derek Gusoff Apr 3 '14 at 12:51
  • Wow! I've not come across fiddler before - does EXACTLY what I need. As a general FYI, the URL that seems to return the packet I need is: /sites/SITENAMEHERE/_layouts/filter.aspx?ListId={LIST_GUID}&FieldInternalName=FIELD_INTERNAL_NAME&ViewId={VIEW_ID_FOR_WHICH_THE_USER_HAS_ACCESS}&FilterOnly=1&Filter=1. This returns a web page (full html, not XML) which includes a SELECT element, with OPTION elements for the values. Which I can iterate over. And it does it in around 2 seconds. Yay! – tobriand Apr 3 '14 at 13:55
  • Right, I'm getting back the web page it's expecting, but it doesn't have the content I'm after until after it executes some javascript (and I'm having serious issues working out both what is being executed to populate the values and how to replicate this execution having just retrieved an HTTP response). Any help appreciated! – tobriand Apr 3 '14 at 16:19
  • You are getting into some really complex stuff here - more complex than a QA site can support. You are on the right track though. That request that loads the select is in the Fiddler traffic somewhere downstream. Good luck! – Derek Gusoff Apr 3 '14 at 16:34
3

I believe the simplest and recommended way to achieve this is using either the SharePoint JavaScript Object Model or AJAX. Neither will cause the page to become unresponsive and both should be fast, even with several thousand items.

If you are performing just one query, the SharePoint JavaScript Object Model is your best option. The following MSDN article covers basic operations with the JavaScript Object Model. It targets 2013 however; it is also accurate for 2010.

http://msdn.microsoft.com/en-us/library/office/jj163201(v=office.15).aspx

Once you retrieve the list items, you can add them to an array and then get the unique values. The following article has a very fast way of getting unique items from an array:

http://www.shamasis.net/2009/09/fast-algorithm-to-find-unique-items-in-javascript-array/

If you prefer to continue down the path of using filter.aspx, the following article discusses and provides code samples for the exact same functionality.

http://modery.net/providing-filter-dropdowns-for-a-sharepoint-listlibrary-with-jquery/

For reference, here are the important bits:

The code below consists of two parts, a JavaScript section (including jQuery and SPServices usage) to retrieve the dropdown and prepare its output as well as a simple HTML section where we present the result of the JavaScript code.

 //update the following 2 lines with your own local versions if wanted
 < script type = "text/javascript"
src = "//cdnjs.cloudflare.com/ajax/libs/jquery/2.1.1/jquery.min.js" > < /script>
<script type="text/javascript
" src=" //cdnjs.cloudflare.com/ajax/libs/jquery.SPServices/2014.01/jquery.SPServices.min.js"></script>
 < script type = "text/javascript" >
    $("document").ready(function() {
        //set default vars; replace them with your ownvar
        listID = '{B858515E-A827-4444-98DA-E81B4F1AF0AD}';
        var viewID = '%7B3C11FBF2%2D9702%2D4B76%2DB280%2D02A783B8E7FA%7D';
        //load paramsvar
        allVars = $().SPServices.SPGetQueryString();
        var filterFieldsParams = "";
        $.each(allVars, function(objKey, objValue) {
            if (objKey.substr(0, 11).toLowerCase() == "filterfield" || objKey.substr(0, 11).toLowerCase() == "filtervalue") {
                filterFieldsParams += "&" + objKey + "=" + objValue;
            }
        });

        function getAjaxFilter(name, internalName) {
                $.ajax({
                    url: $().SPServices.SPGetCurrentSite() + '/_layouts/filter.aspx?ListId=' + listID + '&FieldInternalName=' + internalName + '&ViewId=' + viewID + '&FilterOnly=1&Filter=1' + filterFieldsParams,
                    success: function(data) {
                        // replace"<b>"+name+": </b>" with your own code if required
                        $('#filterField' + internalName).html("<b>" + name + ": </b>").append($("<div></div>").append(data).find("select, img"));
                        //clear current onChange event
                        $("#diidFilter" + internalName).attr("onchange", '');
                        // add change event
                        $("#diidFilter" + internalName).change(function() {
                            FilterField(viewID, internalName, encodeURIComponent(this.options[this.selectedIndex].value), this.selectedIndex);
                        });
                    }
                });
            }
            // provide selected filters
        getAjaxFilter("Single Line of Text", "SingleLineOfText");
        getAjaxFilter("Number", "Number");
        getAjaxFilter("Currency", "Currency");
    }); < /script>
<div id="allFilters">
<div id="filterFieldSingleLineOfText"></div >
 < div id = "filterFieldNumber" > < /div>
<div id="filterFieldCurrency"></div >
    < /div>

Explanation

The first two lines load the necessary jQuery and SPServices scripts. In the beginning of the JavaScript code block, we first define the ID of the list/library and the view, and then to fetch any filter parameters that may have been applied already from the URL. In such a case, we want our filters to take into account any existing filters, so that unrealistic filter options are not shown (as mentioned under Background Information).

The function getAjaxFilter is the core of this solution. It calls the above mentioned filter.aspx page and fetches its data, filters it to return only any dropdown and any images (if a filter is applied, an icon is shown), and updates the dropdowns onChange event. The reason for this is that the default behaviour in this case doesn’t work properly when you want to remove a filter (when you select (All) from the dropdown). What we need to do here is to simply call the exact same JavaScript function FilterField (defined in core.js) with the correct parameters.

After the function declaration, we have 3 function calls, which you will later need to r eplace with your own. Lastly, there are three named DIVs which are used later to “store” the results from the getAjaxFilter calls. They follow the naming convention filterFieldINTERNALFIELDNAME, so you need to update them accordingly with your own column names (see below for how to find them out).

  • It's been a while since I properly investigated this, but the AJAX approach is almost certainly the one I'll end up using. For now, I'm looping over the list. It adds around 10-15 seconds to the load time for the page, so it is noticeable, but for the time being it does the job. – tobriand Jul 30 '14 at 16:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.