Tuesday, May 24, 2016

Workaround to filter on taxonomy columns using OData filters instead of CAML using REST in SharePoint

Wow, that was a long post title indeed. The thing is, you cannot use OData filters on taxonomy columns in SharePoint, hence you see posts on the interwebs about using CAML instead to filter on taxonomy fields by using this construct:


And this works just perfect. But if you try to add a field expansion at the same time on a lookup field, then it blows up. So you can either do an $expand=MyLookupFiled on the /Items endpoint or filter using CAML on a taxonomy field using the /GetItems endpoint.

What if I told you that you can accomplish both using a workaround!! You may either contact me and pay up, or continue reading for free :)
The workaround involves using the hidden list field TaxCatchAll. This field exists for all rows having taxonomy data, and includes all the terms used. The good thing is that this is a lookup field pointing to the hidden taxonomy list located at [site url]/Lists/TaxonomyHiddenList/AllItems.aspx.

If you want to filter against a term named Puzzlepart you could craft the following REST URL:

$filter=TaxCatchAll/Term eq 'Puzzlepart'&

If you want to use the ID for a taxonomy term, use TaxCatchAll/IdForTerm instead. And back to why I wrote this post. I needed to expand on a lookup column in addition to the taxonomy filter. Add another expand and select and you’re all set.

/_api/web/Lists/GetByTitle('<list>')/Items/?$select=*,TaxCatchAll/Term,MyLookup/Title&$filter=TaxCatchAll/Term eq 'Puzzlepart'&$expand=TaxCatchAll,MyLookup

If you have multiple taxonomy fields it will still work, but you need to know which column is using which term set in order to control it fully, but should work out just fine for most cases.

Happy filtering and expanding of fields!!