SharePoint 2013 REST How to select a look up field

Question

I can't select look up field in my SharePoint 2013 List. also I can't filter base on a Look up field.

for example I have List with Name Test and this list has fields: Title, Company, Province the Company and Province is look up fields I want to filter based on Province which is a look up field using REST query it gives error:

my query:

https://TestServer/sites/AIB/OBC/_api/web/lists/getByTitle('Test')/items?$select=Province/Title&$expand=Province&$filter=Province/Title eq 'ABC'

it gives error when I put the URL in My browser for testing it gives the blow error:

<m:message xml:lang="en-US">The field or property 'Province' does not exist.</m:message>

How to filter based on a look up field in SharePoint 2013 REST ?

Answer 1

Use $expand like blow code:

/_api/web/lists/GetByTitle('Test')/items?$select=Province/Title&$expand=Province&$filter=Province/Title eq 'XYZ'

Answer 2

Mehdi jalal, I found why it was throwing that error. You need to close your ProvinceTitle with Single Quotes and there you have it. like this

Query Syntax: /_api/web/lists/GetByTitle('Contacts')/items?$select=Province/Title,Province/ID&$expand=Province&$filter=Province/Title eq '<ProvinceTitle>'

Now this is Example Query: /_api/web/lists/GetByTitle('Contacts')/items?$select=Province/Title,Province/ID&$expand=Province&$filter=Province/Title eq 'Detroit Province'

Answer 3

How to filter by lookup field value using SharePoint REST

Assume a Contacts list that contains a lookup field named Province

Option 1

When a lookup column is being added into list, its ID become accessible automatically via REST. For example, when the field named Province is added into List, Province Id could be set or get via ProvinceId property of List Item.

The following query demonstrate how to filter list items by lookup field Id (Province Id in our case):

/_api/web/lists/GetByTitle('<list title>')/items?$filter=LookupField eq <ProvinceId>

where <ProvinceId> is a province id

Option 2

In order to filter by lookup value, the query should contain $expand query option to retrieve projected fields (like Province Title). The following example demonstrates how to filter by lookup field value (by Province Title in our case):

/_api/web/lists/GetByTitle('Contacts')/items?$select=Province/Title&$expand=Province&$filter=Province/Title eq <ProvinceTitle>

where <ProvinceTitle> is a Title of Province