Expanding columns in lookup fields

Question

Using the SharePoint 2013 REST API,

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

Answer 1

Yes, you can expand multiple columns:

/_api/web/lists(guid'<guid>')/items?$select=Title,LookupField1/FieldToBeExpanded1,LookupField2/FieldToBeExpanded2&$expand=LookupField1,LookupField2

Answer 2

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.

Answer 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

Answer 4

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.

Answer 5

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