1

I have a simple fetch I am using to GET items from a specific SharePoint list.

When I specify what fields I want to GET with _api/lists/getbytitle('ListName')/items?$select=Name,Item2,Item3,Item4,etc. I get an error logged in my console telling me:

code: "-1, Microsoft.SharePoint.SPException"
message: {lang: "en-US",…}
lang: "en-US"
value: "The query to field 'Name' is not valid. The $select query string must specify the target fields and the $expand query string must contains Name."

The Name field that I am trying to GET is lookup column (metadata). So one thing that I tried to bypass this is _api/lists/getbytitle('ListName')/items, which pulls everything related to that list through and stores it.

When I look through my response to see what all was returned, along with a million different items and values, this actually does return the Name field (but not how I expected/needed it to be stored). It returns to values related to the one field NameID and NameStringID.

NameID: 49
NameStringID: "49"

I have done research and cannot find any other related topics, I have never worked with metadata in this sense so I am stumped on getting around this issue.

1 Answer 1

2

SharePoint lookup fields actually store the list item ID of the linked to item in the lookup list. So, for instance, you are querying the list ListName, but let's say your Name field is a lookup to a list called LookupList.

That NameID is the item ID of the item in LookupList that is linked to via the Name lookup field.

You could use that directly - you gave an example of an item having NameID: 49. You could query the lookup list directly:

/_api/web/lists/getbytitle('LookupList')/items(49)

and then get field values off of that.

Or, you can pull in those values in the same query you make to ListName. In order to get values from fields in the lookup list, you have to use a combination of $select and $expand. The format in a generalized sense is:

<query>?$select=FieldNameInThisList/FieldInLookupList,FieldNameInThisList/OtherFieldInLookupList&$expand=FieldNameInThisList

So, let's say you wanted to get the values of the Title field and OtherField from the item linked to in your Name field. You would construct your query like:

/_api/lists/getbytitle('ListName')/items?$select=Name/Title,Name/OtherField,Item2,Item3,Item4&$expand=Name

In order for this to work, though, you do need to know the field names of the fields in the lookup list that you want to retrieve.

You should be able to find plenty of information on this if you search for "how to get values from lookup field in REST API" (or something similar).


After some clarification in the comments, it seems that the field in question is a User ("Person or Group") field.

This type of field is, at its essence, a Lookup field, except that it is a lookup to a very specific hidden list in the SharePoint site that stores user information. Because you need to know the names of the fields in the lookup list that you want to "expand", and the lookup list in this case is hidden, it can be difficult to know what fields you can choose.

This other question and answer here on SP.SE provide a good list.

In the comments you also link to a user voice issue that is about Managed Metadata fields. Managed Metadata fields (a.k.a. Taxonomy fields) are a bit more complex than regular Lookup or User fields, and might take more than a simple $expand in order to get usable/readable data from them.

6
  • Thanks for your answer, I will test it out. sharepoint.uservoice.com/forums/329220-sharepoint-dev-platform/… I came across this feed, and it is people experiencing same issue I am. So I have to make another call, after I already made one?
    – BeerusDev
    Mar 9, 2021 at 20:47
  • So I followed your second solution /items?$select=Name/ID&$expand=Name and this is what it returns in the response ``` Name: {__metadata: {id: "somerandomstringofnumbers", type: "SP.Data.UserInfoItem"}, ID: 9} ID: 9 __metadata: {id: "somerandomstringofnumbers", type: "SP.Data.UserInfoItem"} id: "somerandomstringofnumbers" type: "SP.Data.UserInfoItem"```
    – BeerusDev
    Mar 9, 2021 at 21:00
  • Hmm, now I'm getting confused. In your question, you imply the your Name column is a Lookup column. The thing you link to here in your first comment is about Managed Metadata columns. And now in your second comment, it appears as though it's actually a User column. Lookup, Managed Metadata and User columns are kind of similar, but really need to be dealt with in different ways. What type of column is your Name column actually? Mar 9, 2021 at 21:06
  • Person or Group, I thought they were all considered managed metadata. My apologies, but I was able to come up with a solution based off of your answer
    – BeerusDev
    Mar 9, 2021 at 21:08
  • 1
    $select=Name/Id,Name/Title&$expand=Name
    – BeerusDev
    Mar 9, 2021 at 21:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.