2

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 $select and $expand options on the FieldValuesAsText property.

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 LookupFieldName + Id, so: MyLookupFieldId.

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?

  • Is it SharePoint online or SharePoint OnPremise? – Samir Khimani Feb 3 '17 at 16:02
  • It is SharePoint 2013 on-prem. – Dylan Cristy Feb 3 '17 at 16:04
  • Can you please try "MyLookupField" or "MyLookupField/ID" instead of MyLookupFieldId and try once. – Samir Khimani Feb 3 '17 at 16:06
  • @Sam I had tried both of those previously and they didn't work. I did figure it out though, see below. – Dylan Cristy Feb 3 '17 at 16:09
  • Please check my edited comment – Samir Khimani Feb 3 '17 at 16:10
1

Ugh, yet again, another one of those times when you discover the answer yourself a mere five minutes after posting the question.

It works if you explicitly expand the lookup field you are trying to filter on, like so:

"/_api/web/lists/getbytitle('Doc Lib Name')/items?$select=ID,MyLookupField/Id,FieldValuesAsText/FileLeafRef&$expand=FieldValuesAsText/FileLeafRef,MyLookupField&filter=MyLookupField/Id eq " + lookupId;
0

When filtering, you take the Id off of the lookup (ONLY in the filter). I use the same syntax as your original example in your question, but filter should look like: ..."&$filter=MyLookupField eq " + lookupId;. I'd suggest using that rather than expanding it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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