REST API Query Syntax

Question

I'm trying to pull back all Projects that are Active and have a ProjectName="X". ProjectName is a lookup and Active is Yes/No. Here's what I have built:

http://<siteurl>/_api/lists/getbytitle('Project%20Locations')/items?$select=Title,ProjectName

I'm getting the error:

The query to field ProjectName is not valid. The $select query string must specify the target fields and the $expand query string must contains ProjectName

What's wrong with my syntax and how can I build a clause in for ProjectName="X" and Active="Yes" ?

Answer 1

Try following. I am assuming that you have included Title column in your lookup column.

Building a clause for ProjectName="X" and Active="Yes", you have to use $filter operator.

http://<siteurl>/_api/lists/getbytitle('Project%20Locations')/items?$select=Title,ProjectName/Title&$expand=ProjectName&$filter=Active eq 1 and ProjectName/Title eq 'X'

Meaning of the error.

You have to $expand your lookup columns. Target column must be specified in the $select

Regarding $filter by lookup column

1. You need to $expand that column
2. In $select, you have to specify which columns you need to retrieve from lookup column. For example, your lookup list has several columns like Title, Name, EstimatedDate, CloseDate and others. Now you need Title and Name. So your query string is $select=ProjectName/Title,ProjectName/Name&$expand=ProjectName
3. Now you can $filter where ProjectName/Title="X" or ProjectName/Name="Y". Remember you can not $filter something directly like ProjectName="X". So your $filter should look like $filter=Active eq true and ProjectName/Title eq 'X'

Let me know for more explanation OR have a look on my article

CRUD Operation to List Using SharePoint 2013 Rest API