8

I am using SharePoint 2013 REST. I have list which contains many look up columns, I can get list items through list if they are not look up columns but I can't select and also filter based on look up columns.

for example my list have 4 columns 2 are look up column:

Title, Company, Province

Where province is a look up field how can I select filter my data based on province field ?

which is a look up field it gives error.

My REST Code:

    $.ajax({
       url: "https://Myserver.com/sites/ibs/omt/_api/web/lists/getByTitle('Test')/items?$select=Title,Province/ID&$expand=Province/ID&$filter=Province eq'"+province +"'",
       type: "GET",
       headers: {
          "accept": "application/json;odata=verbose",
       },
       success: function(data){
          $.each(data.d.results, function(index, item){
            //$('#first_data').html(item.Title);
            $('.int_extras').append("<a style='font-weight:bold; color:#FFFFFF;margin:2px;' href='"+links+item.ID+"'>"+item.Title+"</a><br />");
         });
      },
      error: function(error){
        alert(JSON.stringify(error));
      }
   )};

I am new to REST queries please give your examples in details

1
  • 1
    The end of your call is ')};' where it should be : '});'
    – Hybris95
    Jul 20, 2016 at 13:34

5 Answers 5

29

How to filter by lookup field value using SharePoint REST

Assume a Contacts list that contains a lookup field named Province

Option 1

Once a lookup column is getting added into list, its ID become accessible automatically via ListItem resource. For example, when the field named {field-name} is added into List, it's value could be set or get via {field-name}Id 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('Contacts')/items?$filter=ProvinceId 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

12
  • My new query: "Myserver.com/sites/ias/omt/_api/web/lists/… eq'"+province+"'", when I write it in browser it gives this error: The field or property 'Province' does not exist
    – MJ X
    Oct 21, 2014 at 3:52
  • 1
    This error occurs since there is no field named Province in Province list (projected list). To filter by Province Title use the following query: /_api/web/lists/GetByTitle('Contacts')/items?$select=Province/Title&$expand=Province&$filter=Province/Title eq 'A' Oct 21, 2014 at 6:15
  • Pay attention to expression Province/Title that means get Title field from Province list, in your case it is Province/Province what means get Province field from Province list Oct 21, 2014 at 6:16
  • even I can't select my code is blow: MyServer/sites/ias/omt//_api/web/lists/… gives error: The field or property 'Province' does not exist.
    – MJ X
    Oct 21, 2014 at 6:27
  • 1
    Let us continue this discussion in chat.
    – MJ X
    Oct 21, 2014 at 6:34
4

your REST Service Call should be like

 url: "https://Myserver.com/sites/ibs/omt/_api/web/lists/getByTitle('Test')/items?$select=Title,Province/ID&$expand=Province&$filter=Province/ID eq '"+province +"'"

It will work for you as while filtering you need to specify field followed by your look up column name

2

I have recently posted an article on Cascading drop down in SharePoint using REST API.

_spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('" + drinkTypeListName + "')/items?$select=Title,Drink/Title,Drink/Id&$expand=Drink&$filter=Drink/Title eq '" + selectedDrink + "'";

Hope this helps you!

1
  • worked perfectly :)
    – Learner
    Jul 17, 2018 at 13:12
1

Here is my RestUrl-

/_api/web/lists/getByTitle('Questions')/items?&$filter=Language eq '1'

Details- In this it is fetching all the items in questions List which is having specific languageId. LanguageId is defined from language list having lookup in Question list. Question List contains a lookup column ie "language" for language List.

0

Andrew Connell covers the solution to this very thoroughly in his blog here: http://www.andrewconnell.com/blog/Applying-Filters-to-Lookup-Fields-with-the-SP2013-REST-API

2
  • 2
    Please don’t add just a link as answer to a question. If the link breaks, the answer you provided will not be useful for future visitors. Provide an answer of your own and use the link as reference. If needed, you can block quote the content from your link. More information is found in our help center, especially How do I write a good answer
    – Benny Skogberg
    May 9, 2017 at 6:17
  • 1
    Whilst this may theoretically answer the question, it would be preferable to include the essential parts of the answer here, and provide the link for reference.
    – user18571
    Sep 20, 2017 at 18:59

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.