How to filter on a Managed Metadata column via REST in SharePoint 2013

Send to Kindle

Pardon the pun, but I just had a ‘clever workaround’ moment with SharePoint’s oData/REST implementation when it comes to filtering list items based on taxonomy (managed metadata) columns. Now I do not consider myself a developer, so this article is probably a little verbose for some readers, but should be helpful to power users or IT pros.

Here is an example term set called FilterDemo. You can see two levels of hierarchy.

image

Take the scenario of a custom list (called TestFilter) with a managed metadata column (called FilterDemo) that links to the above term set. Let’s also assume there are 3 entries in it as follows:

Title FilterDemo
A A1
B B3
C Category A

Using the wonders of the REST API, I am able to get access to all items in the list via the following URL:

http://site/_api/web/lists/getbytitle(‘TestFIlter’)/Items

If you execute that, and IE is has “feed reading view” turned off, you will get back lots of scary looking XML. If you collapse it though, you will see three entry tags in it. One for each item in the TestFilter list.

 <?xml version=”1.0″ encoding=”utf-8″ ?>
<feed xml:base=http://site/_api/ xmlns=”http://www.w3.org/2005/Atom xmlns:d=”http://schemas.microsoft.com/ado/2007/08/dataservices xmlns:m=”http://schemas.microsoft.com/ado/2007/08/dataservices/metadata xmlns:georss=”http://www.georss.org/georss xmlns:gml=”http://www.opengis.net/gml>
  <id>a0dd3649-27b9-4d8d-90f8-243e9622b158</id>
  <title />
  <updated>2013-09-23T01:44:35Z</updated>
+ <entry m:etag=”“2”>
+ <entry m:etag=”“3”>
+ <entry m:etag=”“1”“>
</feed>

Using more wonders of REST (and oData), I can change the URL to filter my results so that I only get matching items back. For example: here I am filtering on Items where the Title field has “A” in it.

http://site/_api/web/lists/getbytitle(‘Testfilter’)/Items/?$filter=Title eq ‘A’

Now we get back just the one entry matching that criteria…

 <?xml version=”1.0″ encoding=”utf-8″ ?>
<feed xml:base=http://site/_api/ xmlns=”http://www.w3.org/2005/Atom xmlns:d=”http://schemas.microsoft.com/ado/2007/08/dataservices xmlns:m=”http://schemas.microsoft.com/ado/2007/08/dataservices/metadata xmlns:georss=”http://www.georss.org/georss xmlns:gml=”http://www.opengis.net/gml>
  <id>9dad763d-743f-4ffb-b26b-e53c0f6e1f7e</id>
  <title />
  <updated>2013-09-23T02:19:02Z</updated>
+ <entry m:etag=”“2”>

</feed>

Okay, so there is nothing earth shattering about what I just did above and its well documented in various places. But look what happens when I try and filter items in the list based on the FilterDemo column which is Managed metadata based…

http://site/_api/web/lists/getbytitle(‘Testfilter’)/Items?$filter=FilterDemo eq ‘A1’

Boom! Browser returns an error. If I do the same thing using Fiddler to look at the trace, it reports a HTTP/1.1 400 Bad Request error.

So I start digging and come across articles from Phil Harding and Serge Luca informing me that Taxonomy columns are unsupported via REST. I got my hopes up when I came across an Andrew Connell article on filtering lookup fields, since behind the scenes the taxonomy field is actually a lookup field, but in the comments section, it seemed to confirm that this wasn’t doable. All seemed lost…

But in reading MSDN’s REST articles, I had a vague recollection that CAML could be done via REST queries. I knew that using CAML, it was indeed possible to filter taxonomy columns. I proved it using CAML Designer 2013, connecting to the TestFilter list and filtering it successfully using the following XML…

<Where>
   <Eq>
      <FieldRef Name='FilterDemo' />
      <Value Type='TaxonomyFieldType'>A1</Value>
   </Eq>
</Where>

So, armed with this knowledge, I came across an MSDN forum thread where a tantalising clue was offered. Christophe Humbert asked whether CAML queries could be done via the REST API and Erik C. Jordan provided this nugget of wisdom:

I was able to get the following to work:

POST https://<site>/_api/web/Lists/GetByTitle(‘[list name]’)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query>[other CAML query elements]</Query></View>”}

Editors node: I will be a little verbose at this point in case you are not a developer or overly familiar with REST.

This approach looked exactly what I needed and I thought this was worth a shot, but since the remedy is a HTTP POST rather than a GET, I couldn’t do it with Internet Explorer, so I loaded up fiddler, and used the Composer function. I crafted the following POST with an empty CAML query as a test…

http://site/_api/Web/lists/getByTitle(‘TestFilter’)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query></Query></View>”}

 

image

And this is the response I got…

HTTP/1.1 411 Length Required

A quick bit of googling, and I realise that some HTTP queries require the use of a ‘Content-Length‘ field in the HTTP header. The standard states that: “Any Content-Length greater than or equal to zero is a valid value”, so I tried this figure as shown below:

image

And this time I get the response:

HTTP/1.1 403 FORBIDDEN (The security validation for this page is invalid and might be corrupted. Please use your web browser’s Back button to try your operation again).

Another quick bit of googling I discover that I am missing another required HTTP header in my POST request. This is called the X-RequestDigest and it holds something called the form digest. The form digest improves SharePoint security because it is specific to a specific user, site and limited to a certain time frame. You need to request a form digest and then pass it back to SharePoint for subsequent calls. To get hold of the form digest, you have to make another REST call which generates one. This is done by making a POST request with an empty body to http://site/_api/contextinfo and extracting the value of the “d:FormDigestValue” node in the information returned. In fiddler it looks like the following…

image

image

If you look at the returned content from calling the _api/contextinfo method above, I have highlighted FormDigestValue. In Fiddler, copy this value into the Request headers section of the composer and retry the CAML request:

image

Now if you execute the request, we get data!

HTTP/1.1 200 OK

If you look a the raw results in fiddler, you will see a whole bunch of scary XML. If you examine the results using the XML parser built into Fiddler as shown in the image below, you will see very similar output to my original REST request that I started this article with – 3 entries in this list. It worked!

image

So now let’s add our CAML query into the XML and see if we can make it work. Recall that I successfully tested this query via the following CAML…

<Where> <Eq> <FieldRef Name=’FilterDemo’ /> <Value Type=’TaxonomyFieldType’>A1</Value> </Eq> </Where>

So I construct the following URL and paste into the Fiddler constructor:

http://site/_api/Web/lists/getByTitle(‘TestFilter’)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query><Where> <Eq> <FieldRef Name=’FilterDemo’ /> <Value Type=’TaxonomyFieldType’>A1</Value> </Eq> </Where> </Query></View>”}

 

With great excitement, I clicked “Execute” and received….

HTTP/1.1 400 Bad Request

Ah crap! Unfortunately I could not find a single example of this form of REST query to SharePoint, but I got a hint to the problem from Fiddler itself. It wasn’t happy with my request at all, showing the request as red.

image

Clearly I was doing something wrong, and being a non-developer I figured I wasn’t encoding things properly. So after some trial and error, I worked out that spaces were the issue. So where I was able to remove them I did, and those that I couldn’t, I encoded like so:

http://site/_api/Web/lists/getByTitle(‘TestFilter’)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query><Where><Eq><FieldRef%20Name=’FilterDemo’/><Value%20Type=’TaxonomyFieldType’>A1</Value></Eq></Where></Query></View>”}

At this point fiddler stopped showing me an angry red colour and I clicked the Execute button. Wohoo! It works! Below you can see a single matching entry, just like my example when I filtered on Title column using the $filter parameter.

image

Expanding the XML indeed confirms it has matched term A1. Smile

image

Conclusion

While I was happy that I found a way to use REST to filter a list based on a Taxonomy column, I’m sure this method offers some interesting opportunities in various other scenarios.

In my company Seven Sigma, we have a worn-out post-it note that has the words “Alpha SharePoint Developer” written on it. This gets stuck to the office of whoever does the coolest coding trick and I’m happy to report that this little effort netted me the Alpha developer prize for the first time ever, principally because I then used this approach with SharePoint Designer 2013 workflows and it worked really well. In fact it worked so well that I have decided that using this with the new capabilities of SPD workflows warrants a blog series of its own.

Until then, I hope that this approach works for you and happy REST’ing!

 

Thanks for reading

Paul Culmsee

www.hereticsguidebooks.com

www.sevensigma.com.au

 Digg  Facebook  StumbleUpon  Technorati  Deli.cio.us  Slashdot  Twitter  Sphinn  Mixx  Google  DZone 

No Tags

Send to Kindle
Bookmark the permalink.

27 Responses to How to filter on a Managed Metadata column via REST in SharePoint 2013

  1. Kunal says:

    Excellent description of your analysis to come out with a solution. And yes a great outcome. Will be helpful and surely useful. I tag you as #AlphaDeveloper too. Gud on ya mate!

    Kunal

  2. admin says:

    Thanks Kunal – appreciate the feedback 🙂

  3. Paul,
    This is interesting and will check how this approach works out for KoSp Js – Knockout Js for SharePoint ( http://kosp.codeplex.com/ )

    Thanks and Regards
    Ashok Raja .T

  4. admin says:

    Thanks Ashok. Your KoSp project looks great too.

  5. Great post. Now i know two more things 1. You can use CAML query in REST calls 2. Taxonomy fields can be now called via REST API
    Cool stuff mate.

  6. SC Vinod says:

    Excellent man…really helpful!!!

  7. Abul says:

    Hi,

    Thanks for the wonderful post.

    I am using the same formula to fetch record from document library, i can get all Taxonomy field but i struggling to get Name (linked to document with edit menu). I need this field with link to the particular document or the name. Please help me to get this field.

    Many thanks in advance for your help.

    Regards,
    H

  8. fazil says:

    Hi,

    Thanks for the nice post. I am trying to use the same with Office 365 with https. When I am using GetItems method its throwing the following error

    –1, Microsoft.SharePoint.Client.ClientServiceExceptionThe HTTP method ‘GET’ cannot be used to access the resource ‘GetItems’. The operation type of the resource is specified as ‘Default’. Please use correct HTTP method to invoke the resource.

  9. admin says:

    It appears that you are using a HTTP GET method when you are supposed to be using a POST method

    regards

    Paul

  10. Kenny Bright says:

    hi,
    when i use the following in fiddler with response i get back from “ContextInfo” i get:

    Access denied. You do not have permission to perform this action or access this resource.

    Here is my steps:
    1. Request headers for “contextInfo”
    User-Agent: Fiddler
    Host: ogere.sharepoint.com
    Content-length: 0
    Cookie: FedAuth=77u/PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0idXRmLTgiPz48U1A+RmFsc2UsMGguZnxtZW1iZXJzaGlwfDEwMDMwMDAwODY4ZTZmZTBAbGl2ZS5jb20sMCMuZnxtZW1iZXJzaGlwfGtlbm55YnJpZ2h0QG9nZXJlLm9ubWljcm9zb2Z0LmNvbSwxMzAzMDMyNzEzMjE4MzM1NjIsVHJ1ZSxKSFY1dklqVjdEd1J1MVBvU1RoK0F3dmRWd2dmVVprWHRWdEloRHFYRC9iaE8zbmcxcmlZbEdGdDVOb054ZDViR2ltTEZXbG5xSTNTUCs4ZGwvM2VMUE4zMVFQVEplcXR3d1Z0MjhWSGcyYm1GTDh4elNiWnhLMHIrQzJNTXgra3FFS1FSeXNWK2hOd1dWTWZ2R2tWcTNLby9LRnJBZkpjcVdkQ1pTT2J0TjF2aHM3cFZPcjlnZGlqVFlWcW1WNWdiYlpjcnZPbVNqTTBKV0NzQVNCaHF3bFJrV3NNR1pQV3NnYXRkMFh5ZVUySmZMdEdBYUMveGxQeEoxZjJUMnNnWUU0YVk2SUlXeU5TU2g1QlJYVWZFc3Z0UjVJU0QxaDhoaXBsa3N4bVoxeWVacDRPWTdadUo0RitkemZvUjZKZEkrUGNhTGxGK1pkOWNGTTdEcmd2SGc9PSxodHRwczovL29nZXJlLWNhYzVjMzAxNjA3ODA4LnNoYXJlcG9pbnQuY29tL19sYXlvdXRzLzE1L2luaXQuanM/cmV2PWxHakVnY3ZiVjN3ajZ1YWdFTTQxU3c9PTwvU1A+; rtFa=RUQ5X+JcJxAYRY9liWzqhKRZ1sGj4yl6nR6ubiNHpgFVByl8ZCQ/1Jcw51nlr5yax8M9MXe55bunlcNTzRzSZgn34WGNa6q2tRz2HIY1I3I/JP2IbGkShIurdIz4/WBMxL73JBf+5AGc0n5LlWI1I4FJgWrm+gFMwmg9N5SfAWZxMQ1z36Y3OWEXuF4luhO6vkR/82IklqD1gu0hFZMO40UHQmsstXPbe7BjhqJQLriZ5qmdjD7ll76js9dCPLOElYn4cZqJKXen+2v1SiVmh0bB8U5AkzFe2URi1QJ812uwI+DO82vZgsLcmjAcfIFRDmOb/BQUabSV/E0KgyE8b75Vcg7gapu8xEP7l/P8nQTYXiIv/Lp+ryHtNIJq9MnDIAAAAA==

    3. Get FormDigest Value from the response from above
    4. Use FormDigestValue in X-RequestDigest
    this where i get the error above

  11. Samir says:

    Samir,

    I am using the following code to filter metadata, its throwing error. Please help me to fix this.

    $.ajax({
    type: “GET”,
    url: “https://sites/site2/transmittals/_api/web/lists/getbytitle(‘On%20Transmittals’)/Items(query=@v1)?@v1={“ViewXml”:”S – Specification, Procedure, Data Sheet, Study Report, List, Schedule”}”,
    headers: {
    “accept”: “application/json;odata=verbose”,
    “content-length” : 0,
    “X-RequestDigest”: $(“#__REQUESTDIGEST”).val()
    },
    success: function (data) {
    if (data.d.results) {
    // TODO: handle the data
    alert(‘handle the data’);
    }
    },
    error: function (xhr) {
    alert(xhr.status + ‘: ‘ + xhr.statusText);
    }
    });

    Thanks,
    Samir

  12. Kashif says:

    Great article, but one question though. I would want to use this in my SharePoint Hosted App so how to do this with cross domain sites? Looking forward for your reply.

  13. admin says:

    Hi Kashif

    I think in a SharePoint hosted app you may have a problem because of cross site scripting. But best you check with a real developer instead of a fake one like me 🙂

  14. kashif says:

    Thanks for your reply. I switched gears and had to use javascript object model for my solution.

  15. Sandeep says:

    Will it be possible in SharePoint2010 also?

  16. admin says:

    I doubt it, but I confess that I have not checked…

  17. Kangkan says:

    I tried this and tried some changes to it as well on SharePoint 2010. But yet to hit success.

    Please let me know if someone hits a success with SharePoint 2010.

    Regards,
    Kangkan
    http://www.geekays.net/

  18. PCM says:

    Seems this is POST method..When tried to browse this in the URL it is throwing a message ‘The HTTP method ‘GET’ cannot be used to access the resource ‘GetItems’. The operation type of the resource is specified as ‘Default’. Please use correct HTTP method to invoke the resource’..

    I want to use this in the Designer Workflow. Any inputs.?

  19. admin says:

    Hi PCM

    Read this series of articles for how to do POST methods using SPD in SharePoint 2013…

    http://www.cleverworkarounds.com/2013/12/21/trials-or-tribulation-inside-sharepoint-2013-workflowspart-1/

    Paul

  20. GGM says:

    Thank you so much for this post. It was exactly what i needed!

  21. Matt says:

    How can you encode the URLfor use within a JSOM REST call?
    I’ve tried encoding from Chrome advanced REST client, escaping the double quotes with \ but I get the same error regarding not well formatted JSON.
    The query I have works fine in a rest client just not from code within an app.

    Thanks

  22. Selrahc22 says:

    Great Post and greater details. This will help me out a lot. Thanks.

  23. Chuck says:

    Thank you for this. After finding this blog, I found some other references to using CAML with GetItems and using the CAML as the request body (no more encoding spaces!). Anatoly Mironov described its use here http://chuvash.eu/2014/03/25/using-caml-with-sharepoint-rest-api/ and referenced the MSDN documentation here http://msdn.microsoft.com/en-us/library/office/dn531433%28v=office.15%29.aspx#bk_ListGetItems.

  24. Someguy says:

    This helped me out enormously, thanks man!

    I extended upon this idea, because after being able to filter on term label with this solution, I wanted to filter on term GUID. This caused me some time to figure out how to do it, so in case it helps someone here is the solution to filter on term ID.

    idOfTheTermHere

    Couple of things to note:

    1) It can’t be a query anymore, has to be
    2) FieldRef Name has to point to the internal name of the term. Not the display/title name (which should end with a “_0”.
    3) Pass the GUID in the value, which is a string. That’s why the Value Type is ‘Text’

  25. Tapas Paul says:

    Thank you Paul Culmsee, It is a great post and help me alot but I have an exception when I increase the length of the CAML query that we pass in the JSON for filter, the response I get is “404” “Unauthorized”. Is there any workaround for that . If it is than please let me know it will be great help for me.

    FYI : In the list I have 6 managed metadata and each contains more than 100 values and I want to filter items based on the values.

    Thanks in advance.

    Regards,
    Tapas Paul,
    SharePoint Developer

  26. KK Paliwal says:

    Thanks Paul for very helpful work and article.

    I tried your code and approach but facing one issue. It would be great if you can look into this matter:

    Purpose: User created alert preferences using metadata to receive email whenever a new document is uploaded into one document library having similar associated metadata.

    Approach: To filter managed metadata, on upload document, I am collecting all the metadata associated with the document and building the CAML query. I have another list where user has created some alert preferences which are stored in a custom list. Using this CAML query I want to filter this custom list and want to send an email to all the respective users.

    But when passing the CAML query using POST method it is giving me following error:

    “Cannot complete this action.\u000a\u000aPlease try again.”

    And when I reduce the filters in query i.e. deselecting some managed metadata…… then it works fine.

    So now please help me to resolve this issue

  27. Pramodh says:

    Thanks for sharing information on d:FormDigestValue

    Appreciate your help!

Leave a Reply

Your email address will not be published. Required fields are marked *