This tripped me up a few months back. After figuring it out I added a reminder and jotted down a few notes with the intention of blogging it later. I never got around to it but recently noticed a few people asking this question on a discussion list I’m on, so it bubbled to the top of the priority list.
Here’s the problem: When you add a lookup field to a list in SharePoint, you can’t filter by the name of the item in the lookup column when using the REST API. The reason is simple: the value isn’t exposed the way you might expect it.
Consider the following scenario: You have two lists (1) Companies and (2) Employees. The Employees list has a column Employer that is a lookup to the Companies list. Here’s what you get back for the list item in a REST query… notice there is an EmployerId element but not a Employer element:
REST query response
This contains the ID of the item in the parent list. In order to filter on this field in your query you need to use a OData operator $expand that tells it to walk the association. The value you put in here is the name of the column in the list (not what you see in the REST response) and the child Id field. You will also need to select the specified column and then you can apply your filter.
So, using the lists & columns from the above scenario it would look something like this if you wanted to get all employees that worked for company ID=1 (I added some line breaks to make it more readable):
http://[...]/_api/web/lists/getbytitle('Employees')/Items/
?$select=Title,Employer/Id
&$expand=Employer/Id
&$filter=Employer/Id eq 1
Special Scenario: People Fields
People fields are very similar because they are also lookup fields under the covers. The lookup column points back to the User Information List so you’ll want to use that list to find the person by name or sign in name to get their ID and then use that in your filter within the content list using the above technique.
Special Scenario: Managed Metadata Fields
These fields are stored in a different way, as you can see from the Relevant Offices column in my list:
REST query response for Managed Metadata fields
As you can see, the data is stored a little differently than what you might expect. You can see the label that was used for the term, but you can also get the GUID as well as the WssId. Managed metadata columns are really lookup fields too! They point back to a special hidden list called the TaxonomyHiddenList (I explained how it works in
part 6 of my Managed Metadata Series).
This special list at the root of a site collection contains a copy of all terms used throughout the entire site collection. So if you want more information about the term, you can go there using the technique above and the ID of the list item in that list (aka: WssId) or you can use the Taxonomy server-side or CSOM to find the specific term by it’s GUID.
Microsoft MVP, Full-Stack Developer & Chief Course Artisan - Voitanos LLC.
Andrew Connell is a full stack developer who focuses on Microsoft Azure & Microsoft 365. He’s a 20+ year recipient of Microsoft’s MVP award and has helped thousands of developers through the various courses he’s authored & taught. Whether it’s an introduction to the entire ecosystem, or a deep dive into a specific software, his resources, tools, and support help web developers become experts in the Microsoft 365 ecosystem, so they can become irreplaceable in their organization.
