I am trying to query a document library via REST and get the item ID and the document name. The query needs to filter on a (single value) lookup field.
Originally I was using a CAML query to do the filtering on the lookup field, which worked, but that only returned
SPListItem objects, which have the ID, but that means I would need to do another REST call to retrieve the
SPFile objects to get the document name.
I came across this answer which solved the problem of getting the ID and document name with one call by using OData
$expand options on the
I also came across this other answer about how to filter on a Lookup field if you know the lookup ID (which I do). It basically says that when a Lookup field is added to a list, the lookup IDs are accessible via a property named
My REST query looks like this:
"/_api/web/lists/getbytitle('Doc Lib Name')/items?$select=ID,FieldValuesAsText/FileLeafRef&$expand=FieldValuesAsText/FileLeafRef&$filter=MyLookupFieldId eq " + lookupId;
However, the filter is not working. I get an error:
Column 'MyLookupFieldId' does not exist. It may have been deleted by another user.
Oddly, if I do
$select=ID,MyLookupFieldId,FieldValuesAsText/FileLeafRef with no filter, the response does contain a property named
MyLookupFieldId with the correct lookup ID.
So what's going wrong here?
I found yet another answer which has a comment that seems to imply that filtering on a lookup field by ID is not supported on-prem, but that question is also about multi-lookup fields, and mine is a single value lookup field.
So is that the case? Is OData filtering on a Lookup field not supported on-prem regardless of single or multi lookup?
If so, how can I get what I want? Is it possible to combine a CAML query and OData query so I can use CAML to filter by the lookup field, but OData to expand the
FieldValuesAsText/FileLeafRef to get the document name?
How can I get the
SPListItem ID and the
SPFile document name, while filtering by a Lookup column, all in one single REST query?