27

Using the SharePoint 2013 REST API,

Can I get more than one column from the lookup column by using the $select and $expand endpoints?

Improve this question

5 Answers 5

Reset to default
18

Yes, you can expand multiple columns:

/_api/web/lists(guid'<guid>')/items?$select=Title,LookupField1/FieldToBeExpanded1,LookupField2/FieldToBeExpanded2&$expand=LookupField1,LookupField2
Improve this answer
1
  • Too simplistic. Doesn't define FieldToBeExpanded1 and FieldToBeExpanded2, and seems to be talking about having multiple lookups, not multiple fields from a single lookup's backend list. Commented Nov 1, 2021 at 22:25
7

Below query finally worked for me:

  • Cats is a list of cats, with a Title field and Merged as a calculated field

  • In the MainFlat list, Cat is a lookup field to the Merged field on the Cats list

    "/_api/web/lists/getbytitle('MainFlat')/items?$select=Title,number,Cat/Merged&$expand=Cat&$filter=Cat/Merged eq '"+Merged+"'"

If you bring in multiple fields using the settings of that Cat lookup column in the MainFlat list, you can bring them in on the REST call. For example, if you want the cat's name from the Title field, too, and you specify that you also want to show the Title field in the Cat field's settings, you can then add Cat/Title:

$select=Title,number,Cat/Merged,Cat/Title&$expand=Cat

It will not work until you add the Title field in the settings for the Cat lookup field, though.

Improve this answer
2
  • 1
    i have problems @imanabidi that solved by your answers thanks ;) Commented Jan 1, 2017 at 6:34
  • 1
    i have this problem after 3 years and your answer helped me again. ;) Commented May 9, 2021 at 13:21
3

Consider your lookup column name as "Field1LUP" of "List1" and "Title" field of "List2" is source to lookup field.

/_api/web/lists(guid'<guid>')/items?$select=Field1LUP/Title&$expand=Field1LUP
Improve this answer
0

The REST API and OData request parameters can be used to request additional information from a lookup field's list. As an example, let's say we have an Employees list with a Department lookup field that has Name and Branch fields on that Department list. If we need to get the Employee's Name and Id, but also get the department's name and branch, we can use this GET request:

/_api/web/lists/getbytitle('Employees')/items?$select=Name,Id,Department/Name,Department/Branch&$expand=Department

This gets the employee's name, ID, and then we expand the Department lookup field and read the department name (Department/Name) and branch (Department/Branch). The requirement is to expand the lookup list and prefix every field you want off of its list with the list name. One important note is that this will not work until you select in the settings of the Department lookup field, in the Employees list, that you want the additional Branch column to appear on the Employees list.

More information can be found at:

http://www.ozkary.com/2015/10/sharepoint-2013-api-odata-for-lookup-fields.html

Hope this helps.

Improve this answer
1
  • I think this got downvoted originally because it didn't answer how you would get multiple columns. Provided an edit. Commented Nov 1, 2021 at 22:22
0

When referring to a lookuplist/value can lookup list be equal to a guid? My lookup lists contain / and it's causing problems within the query

Improve this answer
2
  • As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. Commented Jan 21, 2024 at 9:24
  • If you have a new question, please ask it by clicking the Ask Question button. Include a link to this question if it helps provide context. - From Review Commented Jan 21, 2024 at 10:26

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.