SharePoint filtering list with multiple values of the same field

Question

"Display the items in the list that either belongs to the category Health or Entertainment."

I have a sample list: http://www.example.com/Lists/MyList/AllItems.aspx

And if I am to filter the list items to look for Categories with the value "Entertainment", the URL will look like this:

http://www.example.com/Lists/MyList/AllItems.aspx?View={ViewId}&FilterField=Categories&FilterValue=Entertainment

Suppose I'd like to filter by the list items with the Categories "Entertainment" OR "Health". Unfortunately, this URL would not work as I can only filter with 1 value for the same field at a time:

http://www.example.com/Lists/MyList/AllItems.aspx?View={ViewId}&FilterField1=Categories&FilterValue1=Entertainment&FilterField2=Categories&FilterValue2=Health

What is the URL for filtering with multiple values of the same field ("OR" condition) in a SharePoint list?

Answer 1

You'll want to use the name/value pair of FilterName and FilterMultiValue in your query string to achieve an OR filter.

For example, to filter a standard Tasks list view in a SharePoint Team Site to return all items that are "Completed" or "Deferred", your URL would look like:

http://www.example.com/Lists/MyList/AllItems.aspx?View={ViewId}&
FilterName=Status&FilterMultiValue=Completed;Deferred

In your use case, the new URL would be:

http://www.example.com/Lists/MyList/AllItems.aspx?View={ViewId}&
FilterName=Categories&FilterMultiValue=Entertainment;Health

UPDATE: Unfortunately, Microsoft does not do a great job of documenting things like query string params that work in SharePoint. I don't even remember where I first picked it up (sometimes you see a native web part doing something & try it yourself) but I've been using successfully since SP2007. Here though is a blog post from 2012 that explains the same thing & offers a few more examples: http://techtrainingnotes.blogspot.com/2012/03/sharepoint-search-filter-or-sort-lists.html

Answer 2

On SPO, I tried FilterMultiValue and it did not work for me. But I've been successful with a simple operator %3b%23 between values.

In this example URL, I am using view1 (a public view that already has some filtering in place), then filtering that view URL on the Product field for "value1" and "value2".

https://company.sharepoint.com/sites/sitename/library/Forms/view1.aspx?FilterFields1=Product&FilterValues1=value1%3b%value2&FilterTypes1=TaxonomyFieldType&viewid=2d0d02c1-4f0c-4eb8-9bea-4d7494bf7014

My use case: We have a product page with links to product documentation. Of course one product was easy. But I needed a lot of different links with different product combinations, and I didn't want to create or generate 1000 views.

To figure it out, I used the base URL for view1. Then I did onscreen filtering for the two values I wanted to see, which appends the URL. I used the appended URL to see that the "OR" operator is %3b%23. At that point, I used a spreadsheet with all the product values I needed for view URLs, and concatenated in the operator, creating a complete URL. Then we used those URLs in the web part for Product links. It's kind of a hack, but it is faster and easier to maintain than individual views or site pages.

Answer 3

Had this same issue in Sharepoint 2013 (Office 365). I created the exact filter I wanted within the List UI (multiple filters with one containing an AND clause for a single field), and then clicked "Save This View". Worked just fine.

Answer 4

Solution for filtering multi value lookup column in SharePoint. Link for detailed instructions: Multi value Lookup Columns in SharePoint Code for reference - Hope this helps some readers.

function multiValueLookup() {
    var items = "";
    var citiesListName = "Cities";
    $("select[title='City possible values'] option").remove();
    $("select[title='State selected values'] option").each(function (i) {
        var clientContext = new SP.ClientContext.get_current();
        var oList = clientContext.get_web().get_lists().getByTitle(citiesListName);
        var camlQuery = new SP.CamlQuery();
        camlQuery.set_viewXml("<View><Query><OrderBy><FieldRef Name='Title' /></OrderBy><Where><Eq><FieldRef Name='State' LookupId='TRUE'/><Value Type='Lookup'>" + $(this).val() + "</Value></Eq></Where></Query></View>");
        var items = oList.getItems(camlQuery);
        clientContext.load(items);
        clientContext.executeQueryAsync(success, failure);
        function success() {
            var pn2 = "";
            var pn1 = "";
            var ListEnumerator = items.getEnumerator();
            while (ListEnumerator.moveNext()) {
                var currentItem = ListEnumerator.get_current();
                if (currentItem.get_item('Title') != null) {
                    var pn1 = currentItem.get_item('Title');
                    if (pn2 != pn1) {
                        items = "<option value='" + currentItem.get_item('ID') + "' title='" + pn1 + "'>" + pn1 + "</option>";
                        $("select[title='City possible values']").append(items);
                        pn2 = pn1;
                    }
                }
            }
        }
        function failure(sender, args) {
            // alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
        }
    });
}

Answer 5

Combining single-value and multi-value filters example which works for me:

&FilterFields1=Entity&FilterValues1=[value1]%3B%23[value2]&FilterField2=CustomContentType&FilterValue2=[value]   

Single value fields &FilterField1=...&FilterValue1=...

Multi value fields (OR logic for the values) &FilterFields1=...&FilterValues1=...;#...

As per the example, the filter counter should always be incremented. encodeURIComponent(';#') = %3B%23 - this is the multi-value separator.

Done in SP2016; my Entity column is a choice one (multi-value).