7

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

Improve this question
1
  • 1
    The end of your call is ')};' where it should be : '});' – Hybris95 Jul 20 '16 at 13:34
28

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

Improve this answer
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 '14 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' – Vadim Gremyachev Oct 21 '14 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 – Vadim Gremyachev Oct 21 '14 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 '14 at 6:27
  • 1
    Let us continue this discussion in chat. – MJ X Oct 21 '14 at 6:34
3

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

Improve this answer
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!

Improve this answer
1
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

Improve this answer
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 '17 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 '17 at 18:59
0

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.

Improve this answer

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.