2 ways for Multiple filters on a (external) list without typing the whole thing – SharePoint 2007 / 2010

I am building a solution for a client where they can pick an item in an external list and they get to see a page with the rest of the data.

The selection table, where you can pick the item, has 5 fields. All fields have to be able to be filtered in order to find the right one.

So, I quickly added a text filter and tried a selection code to search the right item and that worked. I added a second text filter to see if I could search on the full name of the item, and that didn’t work.

Apparantly, the text filter can only filter out the exact text in a field. Texts have to be equal to the text you are filtering. Which makes it absolutely unusable in this situation: The client wants to filter out the correct field by typing a few letters, so maximum speed can be reached.

Thankfully, a short search led me to this post by Laura Rogers (AKA @WonderLaura)

She explains very extensively on how to create a text filter that will search on portions of your field, just like a “contains” function.

In short:

– Add a text filter on the page (in my case 5 text filters, for every column you want to search). Give it a usable name, like Fullname or ApplicationACode, not just TextFilter1, TextFilter2

– Add a dataview version of the list you want to search on via SharePoint Designer (in my case an external list, which worked perfectly)

– Create a parameter on the dataview in SPD for every field you want to search. Use a querystring type and use the exact name of the column

– Add a filter for every field, where you add a “Contains” between the parameter and the actual value

– Connect your textfilter(s) with the dataview list via “Get Parameters From”

All done!

Now, if you type in the text filter “An” it will filter out all fields containing “Antwerp”.

Another way to filter data is Jquery Instant filtering by Jaap Vossers.

This method uses input boxes in the list, between the titles and the first item. If you type in one of them, the list will filter itself.

Keep in mind: This filter only filters what is on the screen!! If you have a lot of items and you use paging, know that it will only filter out the page you are on right now!!

On the other hand, this is a very easy to use (and easy to install) solution. Just add the code down here (or go to codeplex to get the latest release!) to a Content Editor webpart. Don’t forget to reference the jquery file in the first line of the code

The Code

<script src="ReftoJQUERY" type="text/javascript"></script>
<script type="text/javascript">
$(document).ready(function()
{
jQuery.extend(jQuery.expr[':'], {
   containsIgnoreCase: function(a,i,m) {return (a.textContent||a.innerText||jQuery(a).text()||'').toLowerCase().indexOf((m[3]||'').toLowerCase())>=0}
 });
        $("table tr.ms-viewheadertr").each(function()
        {
                if($("td.ms-vh-group", this).size() > 0)
                {
                        return;
                }
                var tdset = "";
                var colIndex = 0;
                $(this).children("th,td").each(function()
                {
                        if($(this).hasClass("ms-vh-icon"))
                        {
                                // attachment
                                tdset += "&lt;td>&lt;/td>";
                        }
                        else
                        {
                                // filterable
                                tdset += "&lt;td>&lt;input type='text' class='vossers-filterfield' filtercolindex='" + colIndex + "' />&lt;/td>";
                        }
                        colIndex++;
                });
                var tr = "&lt;tr class='vossers-filterrow'>" + tdset + "&lt;/tr>";
                $(tr).insertAfter(this);
        });

        $("input.vossers-filterfield")
                .css("border", "1px solid #7f9db9")
                .css("width", "100%")
                .css("margin", "2px")
                .css("padding", "2px")
                .keyup(function()
                {
                        var inputClosure = this;
                        if(window.VossersFilterTimeoutHandle)
                        {
                                clearTimeout(window.VossersFilterTimeoutHandle);
                        }
                        window.VossersFilterTimeoutHandle = setTimeout(function()
                        {
                                var filterValues = new Array();
                                $("input.vossers-filterfield", $(inputClosure).parents("tr:first")).each(function()
                                {
                                        if($(this).val() != "")
                                        {
                                                filterValues[$(this).attr("filtercolindex")] = $(this).val();
                                        }
                                });

                                $(inputClosure).parents("tr.vossers-filterrow").nextAll("tr").each(function()
                                {
                                        var mismatch = false;
                                        $(this).children("td").each(function(colIndex)
                                        {
                                                if(mismatch) return;
                                                if(filterValues[colIndex])
                                                {
                                                        var val = filterValues[colIndex];
                                                        // replace double quote character with 2 instances of itself
                                                        val = val.replace(/"/g, String.fromCharCode(34) + String.fromCharCode(34));
                                                        if($(this).is(":not(:containsIgnoreCase('" + val + "'))"))
                                                        {
                                                                mismatch = true;
                                                        }
                                                }
                                        });

                                        if(mismatch)
                                        {
                                                $(this).hide();
                                        }
                                        else
                                        {
                                                $(this).show();
                                        }
                                });
                        }, 250);
                });
});
</script>