SharePoint 2013 REST-API: Reliable way to retrieve all field values of a list item?

Question

I am trying to retrieve all field values of a specific list item via the REST-API of SharePoint 2013. The values will be used for further processing. The goal is to retrieve all field values dynamically, so manually specifying the required fields is out of the question. The requests are done from a Java-Applikation, so I am building the HTTP-Requests myself and not with CSOM or JSOM.

I tried the following ideas, but without satisfying results:

Simple request to /_api/web/lists(guid)/items(id)/FieldValuesAsText. The problem with this approach is, that all values are returned as Strings, so I don't get typed values from the JSON parser. Also some values like dates are formatted, e.g. "8/8/2014 3:31 PM", which is not usable, as I need dates in ISO-8601 format.

My second approach was to first do a select to retrieve all available fields with /_api/web/lists(guid)/items(id)/ParentList/Fields and then build a select on the list item, based on the EntityPropertyName of each field. This would result in something like /_api/web/lists(guid)/items(id)?$select=Id,Created,Author,.... Problem is, that for example a query with $select=Author does not work, as Author needs to be specified in the $expand-Parameter.

Error:

The query to field 'Author' is not valid. The $select query string must specify the target fields and the $expand query string must contains Author.

And I could not find a way to determine which fields have to be expanded and which not.

My third approach was similar to number two, but retrieve each field from the list item with a separate request: /_api/web/lists(guid)/items(id)/Author. With this I can also retrieve expanded field values as objects and don't have to deal with an $expand-Parameter in the select. The Problem with this method is that it is very slow as there are approximately 60 requests executed for just a single list item.

Does anyone know another method the retrieve all field values of a list item?

Answer 1

How about a simple query:

 /_api/web/lists/getbytitle('<list title>')/items(<item id>)

which will return all the field values for a list item with some limitation for User/Lookup fields:

• only lookup id values will be retrieved.

In order to request all the field values including User/Lookup field values, you could specify * to select all the fields plus to specify projected fields explicitly:

/_api/web/lists/getbytitle('<list title>')/items(<item id>)?$select=<projected field>/Id,<projected field>/Title,*&$expand=<projected field>

For exampe; in order to request all the field values including projected fields (like Author and Editor user fields) the following query could be used:

/_api/web/lists/getbytitle('<list title>')/items(<item id>)?$select=Author/Id,Author/Title,Editor/Id,Editor/Title,*&$expand=Author,Editor

Answer 2

This is a very old question but I can shed some light on one part of it. You say that

I could not find a way to determine which fields have to be expanded and which not.

In the schema returned by the fields REST endpoint, there is a value FieldTypeKind that tells you the type of each list. The value is an integer from this enum and from my own experiments I think the only two that belong in $expand are Lookup = 7 and User = 20. Basically, if you have a field definition {Title: "Author", FieldTypeKind: 20, EntityPropertyName: "Author", LookupField: "Title"}, that tells you that your REST query should be $select=Author/Title,$expand=Author.

This gets more complex with "additional" lookup columns, where you add a column (let's call it "Category") and configure it to show the "Title" field of the lookup table, but also check the box under "Add a column to show each of these additional fields", for the column "Color". Now you have a {Title: "Category", FieldTypeKind: 7, EntityPropertyName: "Category", LookupField: "Title"}, but you also have a second field definition {Title: "Category:Color", FieldTypeKind: 7, EntityPropertyName: "Category_x005c_Color", LookupField: "Color", PrimaryFieldId: "{some guid}"}. You have to use the PrimaryFieldId of the additional field to find the primary field, and use its EntityPropertyName to construct the select/expand arguments: $select=*,Category/Title,Category/Color,$expand=Category.