8

Inside my Projects list, I have a lookup column named ProgrammeRevissionId that is referenced to my Dept list. When I define the following REST call:

http://.../_api/web/lists/getbytitle('Projects')/items?$filter=SiteAutomaticallyCreated eq 'Yes'&$select=ID,Title,ProgrammeRevissionId&$orderby= Title asc

and I get the following results sample, which is correct:

enter image description here

When I try to filter based on the ProgrammeRevissionId, as follows:

http://../_api/web/lists/getbytitle('Projects')/items?$filter=SiteAutomaticallyCreated eq 'Yes',ProgrammeRevissionId eq '13'&$select=ID,Title,ProgrammeRevissionId&$orderby= Title asc

I get an error :

Http 400 bad request

Why I can select a lookup column but I can not filter it inside my REST API end point?

Improve this question
2
  • Is SiteAutomaticallyCreated a choice column or a boolean?
    – Gautam Sheth
    Commented Dec 21, 2016 at 5:32
  • @GautamSheth it is a single line text field,, but i do not think it is causing the problem,, as i can filter based on it in-case i am not referencing ProgrammeRevissionId as mentioned in my original question
    – John John
    Commented Dec 21, 2016 at 12:11

2 Answers 2

Reset to default
7

If you want to filter on lookup field, you need to expand those lookup for filtering.
Try the following query: 

/_api/web/lists/getbytitle('Projects')/items?$filter=SiteAut‌​omaticallyCreated eq 'Yes' and ProgrammeRevission/Id eq 13&$select=ID,Title,ProgrammeRevission/Id&$expand=ProgrammeRevission/Id&$orderby= Title asc

More info here

Improve this answer
3
  • unfortunately i tried your code but it returned "Http 400 bad request" ... now one point that i can not understand, is that inside my original query i was able to add the ProgrammeRevissionId inside the select without using $expand.. so why i need to do so inside the filter,, in all ways i tried removing the $expand and then adding them but i will keep receiving "Http 400 bad request".. not sure what is going on??
    – John John
    Commented Dec 21, 2016 at 1:24
  • 1
    What is the internal name of ProgrammeRevission? In REST query, you have to always use internal name of your field. @johnG
    – Atish Kumar Dipongkor
    Commented Dec 21, 2016 at 5:40
  • @johnG: It's possible to query lookup id without expand by using FieldInternalName + Id like you did in your original query. Besides for filtering by lookup column this simply approach doesn't work. In case you are still getting the error, try to escape all uri parts, so your query will be ?$filter=SiteAutomaticallyCreated%20eq%20%27Yes%27&$select=ID,Title,ProgrammeRevissionId&$orderby=%20Title%20asc
    – Sergei Sergeev
    Commented Dec 21, 2016 at 6:25
7

Your filter expression looks malformed, you join them by and, not a comma. Try the below.

You'd have a request like: /_api/web/lists/getbytitle('Projects')/items?$filter=SiteAutomaticallyCreated eq 'Yes' and ProgrammeRevissionId eq '13'&$select=ID,Title,ProgrammeRevissionId&$orderby=Title asc

These things are easy enough to test in the browser and doing a little investigation on OData protocols and syntax.

As Kai reminded me, you have to expand the projected fields. The comma point I made is still valid. For completeness it would be

/_api/web/lists/getbytitle('Projects')/items?$filter=SiteAutomaticallyCreated eq 'Yes' and ProgrammeRevissionId/Id eq '13'&$select=ID,Title,ProgrammeRevissionId/Id&$expand=ProgrammeRevission/Id&$orderby=Title asc

Improve this answer
4
  • i tried your syntax mainly to use 'and' instead of ',' but i got the same error ..
    – John John
    Commented Dec 20, 2016 at 16:51
  • here is my full api request /_api/web/lists/getbytitle('Projects')/items?$filter=SiteAutomaticallyCreated eq 'Yes' and ProgrammeRevissionId eq '13'&$select=ID,Title,ProgrammeRevissionId&$orderby= Title asc
    – John John
    Commented Dec 20, 2016 at 16:52
  • bah forgot the expand as Kai detailed, the comma point is still valid
    – Eric Alexander
    Commented Dec 20, 2016 at 17:33
  • your updated code will return "Http 400 bad request" error also ..i am really confused on what is going on? now one point that i can not understand, is that inside my original query i was able to add the ProgrammeRevissionId inside the select without using $expand.. so why i need to use $expand if i want to add the lookup field inside the filter ? thanks
    – John John
    Commented Dec 21, 2016 at 1:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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