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!
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 ..
|
