Using SharePoint REST API $filter to specify multiple columns

Posted on 8/18/2011 @ 8:49 AM in #SharePoint by | Feedback | 44374 views

Okay I’m sure you’ve seen a bajillian copy pasted examples on the interwebs demonstrating you how to get filtered columns on a single column using the REST based API and $filter operator.
Hell it’s a shame that even MSDN has only the simplistic examples. 

For instance, if your data looks like this –>

COLUMN1 COLUMN2 COLUMN3 COLUMN4
VALUE11 VALUE12 VALUE13 VALUE14
VALUE21 VALUE22 VALUE23 VALUE24
VALUE31 VALUE32 VALUE33 VALUE34

 

you can easily do this ..

/_vti_bin/ListData.svc/ListName?$filter=COLUMN1 eq 'VALUE11’

And yes that’ll work . .FOR one column. But what if I wanted to include more than one column in such a filtering condition? Do you do?

/_vti_bin/ListData.svc/ListName?$filter=COLUMN1 eq 'VALUE11’&$filter=COLUMN1 eq 'VALUE21’

No, that won’t work!! You can only specify one operator at one time. In order to achieve such multi column filtering you would use a URL like as shown below -

/_vti_bin/ListData.svc/ListName()?$filter=(((COLUMN1%20eq%20'COLUMN1VALUE')%20and%20(COLUMN2%20eq%20'COLUMN2VALUE'))%20and%20(COLUMN3%20eq%20'COLUMN3VALUE'))%20and%20(COLUMN4%20eq%20'COLUMN4VALUE')

Hope someone found this useful!

Sound off but keep it civil:

Older comments..


On 8/18/2011 11:39:03 AM Fred Morrison said ..
The sheer complexity of the example multi-column filter you provided begs for a tool to aid in generating those filters. This reminds me of the god-awful CAML syntax. And yes, boys and girls, the old saying still holds: "If it has 'syntax', it ain't user-friendly" (and might add, in this case, "is aint' developer-friendly" either).


On 8/18/2011 6:38:36 PM Sahil Malik said ..
It's very easy to write such a tool IMO


On 8/18/2011 11:38:08 PM Mor Shemesh said ..
Hi,


Have you tries taking it to the limit, and checking how many ands\ors can you add. and I don't suppose a url size would make an issue since its a web service...

Thanks :)


On 10/26/2011 5:44:09 AM Vie said ..
Thanks a lot Sahil, it really helped (as always) !


On 5/17/2012 3:58:15 AM Mike said ..
Hi Sahil,

How would you $filter on a lookup column that allows multiple values. For instance, let's say I have list for BlogPosts. Each post can be associated with zero or more Categories (lookup list). How can I create a filter to to pull BlogPosts that have been tagged with "ASP.NET"?

Thanks,


Mike


On 5/17/2012 3:08:32 PM Sahil Malik said ..
Mike,

You can find the answer using this technique -


http://blah.winsmarts.com/2011-10-Figuring_our_complex_REST_queries_for_SharePoint.aspx

S


On 9/14/2012 9:08:25 AM Alex said ..
Very useful thanks


On 4/11/2013 12:22:11 AM Daniel said ..
This was really helpful.. Thanks a lot