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?

  • Is SiteAutomaticallyCreated a choice column or a boolean? – Gautam Sheth Dec 21 '16 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 Gu Dec 21 '16 at 12:11
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

  • 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 Gu Dec 21 '16 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 Dipongkor - MVP Dec 21 '16 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 Dec 21 '16 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

  • i tried your syntax mainly to use 'and' instead of ',' but i got the same error .. – john Gu Dec 20 '16 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 Gu Dec 20 '16 at 16:52
  • bah forgot the expand as Kai detailed, the comma point is still valid – Eric Alexander Dec 20 '16 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 Gu Dec 21 '16 at 1:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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