Trials or tribulation? Inside SharePoint 2013 workflows–Part 11

This entry is part 11 of 13 in the series Workflow
Send to Kindle

Hi all, and welcome to the penultimate article in what has tuned into a fairly epic series about SharePoint 2013 Workflows. From part 6 to part 8 of this series, we implemented a workflow that made use of the web service calls as well as the new looping capabilities of SharePoint Designer 2013. We used the web service call to get all of the items in the Process Owners list, and then looped through them to find the process owner we needed based on organisation. While that method worked, the concern was that it was potentially inefficient because if there was a large list of process owners, it might consume excessive resources. This is why I referred to the approach in part 6 as the “easy but flawed” way.

Now we are going to use the “better but harder way”. To that end, the part 9 and part 10 have set the scene for this one, where we are going to implement pretty much all of the theory we covered in them. Now I will not rehash any of the theory of the journey we took to get here, but I cannot stress enough that you really should have read them before going through this article.

With that said, we are going make a bunch of changes to the current workflow by doing the following:

  • 1) Change the existing workflow to grab the Organisation name as opposed to the GUID
  • 2) Create a new workflow stage that gets us the X-RequestHeader (explained in part 9).
  • 3) Build the URL that we will use to implement the “CAML in REST” approach (explained in part 9 and part 10)
  • 4) Call the aforementioned webservice
  • 5) Extract the AssignedToId of the process owner for a given organisation
  • 6) Call the GetUserByID webservice to grab the actual userID of the process owner and assign them an approval task

In this post, we will cover the first four of the above steps…

Get the Name not the GUID…

Here is the first stage of the workflow as it is now, assuming you followed parts 6 to 8.

image

First let’s make a few changes so that we get the Name of the Organisation stored with the current item, rather than the GUID as we are doing now. If you recall from part 4, the column Organisation_0 is a hidden column that got created because Organisation is a managed metadata column. This column stores the names and Id’s of managed metadata term(s) that have been assigned in the format of <term name>|<term GUID>. For example “Metacorp+Burgers|e2f8e2e0-9521-4c7c-95a2-f195ccad160f”.

To get the GUID, we grabbed everything to the right of the pipe symbol (“|”). Now to get the name, we need everything to the left of it.

Step 1:

Rename the stage from “Obtain Term GUID” to “Get Organisation Name” (I trust that by part 11 a screenshot is not required for this)

Step 2:

Delete the second workflow action called Calculate Variable: index plus 1 (Output to Variable:calc) as we don’t need the variable calc anymore. In addition, delete the workflow action “Copy from Current Item: Organisation_0”. You should be left with two actions and the transition to stage logic as shown below.

image

Step 3:

Add an Extract Substring from Start of String workflow action in between the two remaining actions. Click the “0” hyperlink and click the fx button. In the Lookup for Integer dialog, set it to the existing variable Index. Click on the “string” hyperlink and set it to the Organisation_0 column from the Current Item. Finally, click the (Output to…) hyperlink and create a new string variable called Organisation.

image

Now, at this point we need to pause and think about what we are doing. If you recall part 10, I had trouble getting the format right for the URL that uses CAML inside REST web service call. The culprit was that I had to encode any occurrence of a space in the URL with the HTML encoded space (a %20). Take a look at the URL that  was tested in Fiddler below to see this in action…

http://megacorp/iso9001/_api/web/Lists/GetByTitle(‘Process%20Owners’)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query><ViewFields><FieldRef%20Name=’Organisation’/><FieldRef%20Name=’AssignedTo’/></ViewFields><Where><Eq><FieldRef%20Name=’Organisation’/><Value%20Type=’TaxonomyFieldType’>Megacorp%20Burgers</Value></Eq></Where></Query></View>”}

Look toward the end of the URL where the organisation is specified (marked in bold). What do you notice?

Yep – the space between Megacorp and Burgers is also encoded. But this causes a problem since the current value of the Organisation variable contains the space. So let’s deal with this now by encoding spaces.

Step 4:

Add a Replace Substring in String workflow action. Click the first string hyperlink and type in a single space. In the second string hyperlink, type in %20. In the third string hyperlink, click the fx button and add the Organisation variable. In the final hyperlink (Output to Variable:Output), choose the variable Organisation.

image

After all this manipulation of the Organisation variable, it is probably worthwhile logging it to the workflow history list so we can see if the above steps work as expected.

Step 5:

Click the Log Variable:TermGUID to the workflow history list action and change the variable from TermGUID to Organisation. The action will now be called Log Variable:Organisation to the workflow history list

image

Step 6:

In the Transition to stage section, find the “If Variable: TermGUID is not empty” condition and change the variable from TermGUID to Organisation

image

Step 7:

Create a new workflow stage and call it “Get X-RequestDigest”. Then in the Transition to stage section of the Get Organisation Name stage, find the “Go to Get Process Owners” and change the stage from Get Process Owners to Get X-RequestDigest.

The adjusted workflow should now look like the image below…

image

Getting the X-RequestDigest…

If you recall in part 9, we need to call the contextinfo web service so we can extract the FormDigestValue to use in our CAML embedded web service call to the Process Owners list. If that statement makes no sense then go back and read part 9, otherwise, you should already know what to do!.. Bring on the dictionary variables and the Call to HTTP Web service action!

Step 1:

Go to the Get Process Owners stage further down and find the very first action – a Build Dictionary action that creates a variable called RequestHeader. Right click on it and choose Move Action Up. This will move the action into the Get X-RequestDigest stage as shown below.

image  image

What are we doing here? This action was the one we created in part 9 that asks SharePoint to bring back data in JSON format. We first learnt all about this in part 4 when I explained JSON and part 5 when I explained how dictionary variables work.

Step 2:

Add a Call HTTP Web Service action after the build dictionary action. For the URL, use the string builder and add a lookup to the Current Site URL (found in Workflow Context in the data source dropdown). Then add the string “_api/contextinfo” to it to complete the URL of the web service. Also, make sure the method chosen is a HTTP POST and not a GET.

image  image

image

This will construct the URL based on which SharePoint site the workflow is run from (eg http://megacorp/iso9001/_api/contextinfo. ) but without hard-coding the URL.

Step 3:

Make sure the workflow action from step 2 is selected and in the ribbon, choose the Advanced Properties icon. In the Call HTTP Web Service Parameters dialog, click the RequestHeaders dropdown and choose the RequestHeader variable and click OK. (Now you know why we moved the build dictionary action in step 1)

image

Step 4:

Click the response hyperlink in the Call HTTP Web Service action and choose to create a new variable. Call it ContextInfo. Also check the name of the variable for the response code and make sure it is set to the responseCode and not something like responseCode2.

image  image

Step 5:

Add an If any value equals value condition below the web service call. For the first value hyperlink, choose the variable responseCode as per step 4. Click the second value hyperlink, type in “OK” as shown below:

image

This action ensures that the response to the web service call was valued (OK is the same as a HTTP 200 code). If we get anything other than an OK, there is no point continuing with the workflow.

Step 6:

Inside the condition we created in step 5, add a Get an Item from a Dictionary action. Then do the following:

  • In the item by name or path hyperlink, type in exactly “d/GetContextWebInformation/FormDigestValue” without the quotes.
  • In the dictionary hyperlink, choose the variable ContextInfo that was specified in step 4.
  • In the item hyperlink in the “Output To” section, create a new string variable called X-RequestDigest.

All this should result in the action below.

image

Now let’s take a quick pause to understand what we did in this step. You should recognise the d/GetContextWebInformation/FormDigestValue as parsing the JSON output. We get the value of FormDigestValue and assign it to the variable X-RequestDigest. As a reminder, here is the JSON output from calling the contextinfo web service using Fiddler. Note the path from d –> GetContextWebInformation –> FormDigestValue.

image_thumb17

Step 7:

In the transition to stage section, add an If any value equals value condition. For the first value hyperlink, choose the variable X-RequestDigest that we created in step 6. Click the equals hyperlink and change it to is empty.

image

Step 8:

Under the newly created If Variable: X-Request is empty condition, add a Go to a stage action and set it to End of Workflow. In the Else section of the condition, add a Go to a stage action and set it to the Get Process Owners stage.

image

Cool! We have our X-Request Digest stage all done. Here is what it looks like…

image

This has all been very easy so far hasn’t it! A big difference to some of the previous posts. But now its time to wire up the CAML inside REST web service call, and SharePoint is about to throw us another curveball…

Get the Process Owner…

Our next step is to rip the guts out of the existing stage to get the process owner. Unlike our first solution, we no longer need to loop through the process owners list which means the entire Find Matching Process Owner stage is no longer needed. So before we add new actions, lets do some tidying up.

Step 1:

Delete the entire stage called “Find Matching Process Owner”. Do this by clicking the stage to select all actions within it, and then choose delete from the SharePoint Designer ribbon. SPD will warn you that this will delete all actions. Go ahead and click OK.

image

Our next step is to attempt to make the CAML inside REST web service call. To remind you of what the URL will look like, here is the one we successfully tested in part 10. Ugly isn’t it. Now you know why developers are an odd bunch – they deal with this stuff all day!

http://megacorp/iso9001/_api/web/Lists/GetByTitle(‘Process%20Owners’)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query><ViewFields><FieldRef%20Name=’Organisation’/><FieldRef%20Name=’AssignedTo’/></ViewFields><Where><Eq><FieldRef%20Name=’Organisation’/><Value%20Type=’TaxonomyFieldType’>Megacorp%20Burgers</Value></Eq></Where></Query></View>”}

Let’s take our time here, because as you can see the URL we have to craft is complex. First up, we need to use a Build a Dictionary action to create the HTTP headers we need (including the X-RequestDigest). Recall in part 9, that we also need to set Content-length to 0 and Accept to application/json;odata=verbose.

Step 2:

Add a Build dictionary action as the first action in the Get Process Owners section. Click the this hyperlink and the add button in the Build a Dictionary dialog. Add the following dictionary items:

  • Add a string called Accept and a value of: application/json;odata=verbose

image

  • Add a string called Content-length and a value of 0

image

  • Add a string called X-RequestDigest. In the value textbox, click the fx button and choose the workflow variable called X-RequestDigest.

image  image  image

Your dictionary should look like this:

image

Click ok and set the dictionary variable name to be the existing variable called RequestHeader. The completed action should look like the image below:

image

Now let’s turn our attention to creating the web service URL we need.

Step 3:

Find the existing Call HTTP Web Service action in the Get Process Owner stage. Click the URL hyperlink and click the ellipses to bring up the string builder dialog. Delete the existing URL so we can start over. Add the following entries back (carefully!)

  • 1) A lookup to the Site URL from the Workflow Context
  • 2) The string “_api/web/Lists/GetByTitle(‘Process%20Owners’)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query><ViewFields><FieldRef%20Name=’Organisation’/><FieldRef%20Name=’AssignedTo’/></ViewFields><Where><Eq><FieldRef%20Name=’Organisation’/><Value%20Type=’TaxonomyFieldType’>”
  • 3) A lookup to the Organisation workflow variable
  • 4) The string “</Value></Eq></Where></Query></View>”}”

This should look like the image below:

image

A snag…

Click OK and see what happens. Uh-oh. We are informed that “Using the special characters ‘[%%]’ or [%xxx%]’ in any string, or using the special character ‘{‘ in a string that also contains a workflow lookup may corrupt the string and cause an unexpected error when the workflow runs” – Ouch!

image

How do we get out of this issue?

Well, we are using two workflow lookups in the string – the first being the site URL at the start and the second being the Organisation variable embedded in the CAML bit of the URL. Since it is complaining of using certain special characters in combination with workflow lookups, let’s break up the URL into pieces by creating a couple of string variables. At the start of step 3 above, we listed 4 elements that make up the URL. Let’s use that as a basis to do this…

Step 4:

Add a Set Workflow Variable action below the build dictionary action in the Get Process Owner stage. Call the variable URLStart and set its value to: _api/web/Lists/GetByTitle(‘Process%20Owners’)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query><ViewFields><FieldRef%20Name=’Organisation’/><FieldRef%20Name=’AssignedTo’/></ViewFields><Where><Eq><FieldRef%20Name=’Organisation’/><Value%20Type=’TaxonomyFieldType’>

image   image

Step 5:

Add another Set Workflow Variable action in the Get Process Owner stage. Call the variable URLEnd and set its value to: “</Value></Eq></Where></Query></View>”}”

image

Step 6:

Edit the existing Call HTTP Web Service action in the Get Process Owner stage. Click the URL hyperlink and add the following entries back (carefully!)

  • 1) A lookup to the Site URL from the Workflow Context
  • 2) A lookup to the URLStart workflow variable
  • 3) A lookup to the Organisation workflow variable
  • 4) A lookup to the URLEnd workflow variable

This should look like the image below:

image

Click OK and in the Call HTTP Web Service dialog, make sure the HTTP method is set to HTTP POST. Click OK

image  image

Step 7:

Select the Call HTTP Web Service action and click the Advanced Properties icon in the ribbon. In the Call HTTP Web Service Properties dialog box, click the RequestHeaders parameter and in the drop down list to the right of it, choose the RequestHeader variable created in step 3. Click OK.

image_thumb97    image_thumb103

 

Step 8:

Select the Call HTTP Web Service action and click the variable next to the ResponseContent to section. Create a variable called ProcessOwnerJSON. This variable will store the JSON returned from the web service call.

image    image

Step 9:

In the Transition to stage section of the Get Process Owners stage, look for the If responseCode equals OK condition. Set the stage to Obtain Userid as shown below:

image

Step 10:

To make the workflow better labelled, rename the existing Get Process Owners stage to Prepare and execute Process Owner web service call. This workflow stage is going to end when it has attempted the call and we will create a new stage to extract the process owner and create the approval task. At this point the workflow stage should look like the image below:

image

Conclusion

We will end the post at this point as it is already very long. In the next post, we will make a couple of tweaks to the Obtain Userid workflow stage and test the workflow out. For your reference, here is the complete workflow as it stands…

image

image

image

Thanks for reading

Paul Culmsee

HGBP_Cover-236x300.jpg

www.hereticsguidebooks.com

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

No Tags

Send to Kindle

Trials or tribulation? Inside SharePoint 2013 workflows–Part 10

This entry is part 10 of 13 in the series Workflow
Send to Kindle

Hi there and welcome back to my series of articles that puts a real-world viewpoint to SharePoint 2013 workflow capabilities. This series is pitched more to Business Analysts, SharePoint Hackers and generally anyone who might be termed a citizen developer. This series shows the highs and lows of out of the box SharePoint Designer workflows, and hopefully helps organisations make an informed decision around whether or not to use what SharePoint provides, or moving to the 3rd party landscape.

By now you should be well aware of some of the useful new workflow capabilities such as stages, looping, support for calling web services and parsing the data via dictionary objects. You also now understand the basics of REST/oData and CAML. At the end of the last post, we just learnt that it is possible to embed CAML queries into REST/oData, which gets around the issue of not being able to filter lists via Managed metadata columns. We proved this could be done, but we did not actually try it with the actual CAML query that can filter managed metadata columns. It is now time to rectify this.

Building CAML queries

Now if you are a SharePoint developer worth your salt, you already know CAML, because their are mountains of documentation on this topic on MSDN as well as various blogs. But a useful shortcut for all you non coders out there, is to make use of a free tool called CAMLDesigner 2013. This tool, although unstable at times, is really easy to use, and in this section I will show you how I used it to create the CAML XML we need to filter the Process Owners list via the organisation column.

After you have downloaded CAMLDesigner and successfully gotten it installed, follow these steps to build your query.

Step 1:

Start CAMLDesigner 2013 and on the home screen, click the Connections menu.

image

Step 2:

In the connections screen that slides out from the right, enter http://megacorp/iso9001 into the top textbox, then click the SharePoint 2013 and Web Services buttons. Enter the credentials of a site administrator account and then click the Connect icon at the bottom. If you successfully connect to the site, CAMLDesigner will show the site in the left hand navigation.

image  image

Step 3:

Click the arrow to the left of the Megacorp site and find the Process Owners list. Click it, and all of the fields in the list will be displayed as blue boxes below the These are the fields of the list section.

image

Step 4:

Drag the Organisation column to the These are the selected fields section to the right. Then do the same for the Assigned To column. If you look closely at the second image, you will see that the CAML XML is already being built for you below.

image     image

Step 5:

Now click on the Where menu item above the columns. Drag the Organisation column across to the These are the selected fields section. As you can see in the second image below, once dragged across, a textbox appears, along with a blue button with an operator. Also take note of the CAML XML being build below. You can see that has added a <Where></Where> section.

image

image

image

Step 6:

In the Textbox in the Organisation column you just dragged, type in one of the Megacorp organisations. Eg: Megacorp Burgers. Note the XML changes…

image

Step 7:

Click the Execute button (the Play icon across the top). The CAML query will be run, and any matching data will be returned. In the example below, you can see that the user Teresa Culmsee is the process owner for Megacorp Burgers.

image

image

Step 8:

Copy the XML from the window to clipboard. We now have the XML we need to add to the REST web service call. Exit CAMLDesigner 2013.

image

Building the REST query…

Armed with your newly minted CAML XML as shown below, we need to return to fiddler and draft it into the final URL.

<ViewFields>
   <FieldRef Name='Organisation' />
   <FieldRef Name='AssignedTo' />
</ViewFields>
<Where>
   <Eq>
      <FieldRef Name='Organisation' />
      <Value Type='TaxonomyFieldType'>Megacorp Burgers</Value>
   </Eq>
</Where>

As a reminder, the XML that we had working in the past post looked like this:

http://megacorp/iso9001/_api/web/Lists/GetByTitle(‘Process%20Owners’)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query></Query></View>”}

Let’s now munge them together by stripping the carriage returns from the XML and putting it between the <Query> and </Query> sections. This gives us the following large and scary looking URL.

http://megacorp/iso9001/_api/web/Lists/GetByTitle(‘Process%20Owners’)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query><ViewFields> <FieldRef Name=’Organisation’ /> <FieldRef Name=’AssignedTo’ /> </ViewFields> <Where> <Eq> <FieldRef Name=’Organisation’ /> <Value Type=’TaxonomyFieldType’>Megacorp Burgers</Value> </Eq> </Where></Query></View>”}

Are we done? Unfortunately not. If you paste this into Fiddler composer, Fiddler will get really upset and display a red warning in the URL textbox…

image

If despite Fiddlers warning, you try and execute this request, you will get a curt response from SharePoint in the form of a HTTP/1.1 400 Bad Request response with the message HTTP Error 400. The request is badly formed.

The fact that Fiddler is complaining about this URL before it has  even been submitted to SharePoint allows us to work out the issue via trial and error. If you cut out a chunk of the URL, Fiddler is okay with it. For example: This trimmed URL is considered acceptable by Fiddler:

http://megacorp/iso9001/_api/web/Lists/GetByTitle(‘Process%20Owners’)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query>

But adding this little bit makes it go red again.

http://megacorp/iso9001/_api/web/Lists/GetByTitle(‘Process%20Owners’)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query><ViewFields> <FieldRef Name=’Organisation’ />

Any ideas what the issue could be? Well, it turns out that the use of spaces was the issue. I removed all the spaces from the URL above and where I could not, I encoded it in HTML. Thus the above URL turned into the URL below and Fiddler accepted it

http://megacorp/iso9001/_api/web/Lists/GetByTitle(‘Process%20Owners’)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query><ViewFields><FieldRef%20Name=’Organisation’ />

So returning to our original big URL, it now looks like this (and Fiddler is no longer showing me a red angry textbox):

http://megacorp/iso9001/_api/web/Lists/GetByTitle(‘Process%20Owners’)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query><ViewFields><FieldRef%20Name=’Organisation’/><FieldRef%20Name=’AssignedTo’/></ViewFields><Where><Eq><FieldRef%20Name=’Organisation’/><Value%20Type=’TaxonomyFieldType’>Megacorp%20Burgers</Value></Eq></Where></Query></View>”}

image

So let’s see what happens. We click the execute button. Wohoo! It works! Below you can see a single matching entry and it appears to be the entry from CAMLBuilder2013. We can’t tell for sure because the Assigned To column is returned as AssignedToID and we have to call another web service to return the actual username. We covered this issue and the web service to call extensively in part 8 but to quickly recap, we need to pass the value of AssignedToID to the http://megacorp/iso9001/_api/Web/GetUserById() web service. In this case, http://megacorp/iso9001/_api/Web/GetUserById(8) because the value of AssignedToId is 8.

The images below illustrate. The first one shows the Process Owner for Megacorp burgers. Note the value of AssignedToID is 8. The second image shows what happens when 8 is passed to the GetUserById web service call. Check Title and LoginName fields.

image image

Conclusion

Okay, so now we have our web service URL’s all sorted. In the next post we are going to modify the existing workflow. Right now it has four stages:

  • Stage 1: Obtain Term GUID (extracts the GUID of the Organisation column from the current workflow item in the Documents library and if successful, moves to stage 2)
  • Stage 2: Get Process Owners (makes a REST web service call to enumerate the Process Owners List and if successful, moves to stage 3)
  • Stage 3: Find Matching Process Owner (Loops through the process owners and finds the matching organisation from stage 1. For the match, grab the value of AssignedToID and if successful, move to stage 4)
  • Stage 4: Obtain UserID (Take the value of AssignedToID and make a REST web service call to return the windows logon name for the user specified by AssignedToID and assign a task to this user)

We will change it to the following  stages:

  • Stage 1: Obtain Term Name (extracts the name of the Organisation column from the current workflow item in the Documents library and if successful, moves to stage 2)
  • Stage 2: Get the X-RequestDigest (We will grab the request digest we need to do our HTTP POST to query the Process Owners list. If successful move to stage 3)
  • Stage 3: Get Process Owner (makes the REST web service call to grab the Process Owners for the organisation specified by the Term name from stage 1. Grab the value of AssignedToID and move to stage 4)
  • Stage 4: Obtain UserID (Take the value of AssignedToID and make a REST web service call to return the windows logon name for the user specified by AssignedToID and assign a task to this user)

One final note: After this epic journey we have taken, you might think that doing this in SharePoint Designer workflow should be a walk in the park. Unfortunately this is not quite the case and as you will see, there are a couple more hurdles to cross.

Until then, thanks for reading…

Paul Culmsee

HGBP_Cover-236x300.jpg

www.hereticsguidebooks.com

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

No Tags

Send to Kindle

Trials or tribulation? Inside SharePoint 2013 workflows–Part 7

This entry is part 7 of 13 in the series Workflow
Send to Kindle

Hi and welcome to part 7 of my series of articles around SharePoint 2013 workflows. We have been examining the trials and tribulations (mainly trials so far) of implementing a relatively simple document approval workflow for a mythical organisation called Megacorp Inc. In the last post, I started illustrating the first of two approaches to filtering a list based on a managed metadata column using the Call HTTP Web Service capability. If you have been following along with me, we just successfully called the SharePoint lists web service via REST. The diagram below shows where we are at… Now it is time to work with the data that is returned.

image

Very soon we are going to use the loop function of workflows to go through each process owner and check the GUID of the Organisation field. This is probably best done as a new workflow stage that only runs if the response code from the webservice is 200 (OK). So let’s make some minor changes to the workflow…

Step 1:

Add a new workflow stage and name it “Find Matching Process Owner

Step 2.

In the Transition to stage section of the new stage, add a Go to a stage action and set the stage as “End of Workflow

image

Step 3:

In the Transition to stage section of “Get Process Owners” Stage, delete the “Go to End of Workflow” action. In its place, add an “If any value equals value” condition

Step 4:

Click the first value hyperlink of the newly added condition from step 3 and click the fx button. In the Define Workflow lookup dialog, choose Workflow Variables and Parameters as the Data source and choose the responseCode variable in the Field from Source dropdown. Click OK

image  image

Step 5:

Click the second value link of the newly added condition from step 3. Type in the value “OK” and press enter.

image

Step 6:

Under the If Variable: responseCode equals OK condition, add a Go to a stage action. Set the stage as Find Matching Process Owner (the stage created in step 1).

Step 7.

Under the Else condition, add a Go to a stage action. Set the stage as End of Workflow

image

Ok, so now we have a new workflow stage to work with called Find Matching Process Owner. This is where we are going to manipulate dictionary variables and in particular, the ProcessOwnersList variable that contains the data returned by the call we made to the REST webservice in part 6. The first thing we will do is count how many items are in the ProcessOwnersList dictionary, otherwise we won’t know when to stop the loop. This is where the Count Items in a Dictionary workflow action comes in. Let’s try it on the ProcessOwnerList variable now.

Step 8:

In the Find Matching Process Owners stage, add a Count Items in a Dictionary action. Click the dictionary hyperlink and choose Variable: ProcessOwnersList from the list of dictionary variables.

image

Now we should check to see if the value of count is what  we are expecting. For your reference, here are the 9 entries currently in the Process Owners list…

Megacorp Defense Paul Culmsee
Megacorp Burgers Teresa Culmsee
Megacorp Iron Man suits Chris Tomich
Megacorp Gamma Radiation Experiments Peter Chow
Megacorp Pharmaceutical Hendry Khouw
Megacorp Fossil Fuels Sam Zhou
Perth Du Le
Sydney Paul Culmsee
Alaksa Du Le

Step 9:

Add a Log to Workflow History action. Click the message hyperlink and click the ellipses button. In the string builder dialog, type in “Process Owners count: “

image

Step 10:

Click the Add or Change Lookup button. In the Lookup for string dialog, choose Workflow Variables and Parameters from the data source dropdown and choose the variable count. Click OK. You should see the following text in the string builder dialog. Click OK. Review the workflow stage and confirm it looks like the second image below.

image  image

Publish the workflow and run it. Take a look at the workflow history and see what we get. Uh-oh… what now? It says we only have 1 item in the dictionary, but we have 9 process owners. What the…?

image

A JSON (and fiddler) interlude…

In part 6, I stressed the point that dictionary variables can contain any type of variable available in the SharePoint 2013 Workflow platform, including other dictionary variables! This now becomes important because of the way JSON works. To explain better, it is time for me to formally introduce you Fiddler.

Fiddler is a multipurpose utility that sits between the web browser and web sites. It logs all HTTP(s) traffic and is used to view and debug web traffic. Any web developer worth their salt has Fiddler as part of the troubleshooting toolkit because of its ability to manipulate the data as it travels between browser and server. Bets of all, it can be used to compose your own HTTP requests.

So first up, install Fiddler and then start it. In your browser, head over to a web site like Google. Go back to Fiddler and you should see entries logged in the left hand panel. Click on an entry and click the inspectors tab. From here you can see all the gory detail of the conversation between your web browser and the web site.  Neat eh?

Snapshot

If you look in the tabs in the Fiddler window, you will see one labelled Composer. Let’s use it right now… Paste the webservice URL that we used in part 6 into the composer textbox as shown below. Also, paste the string “Accept: application/json;odata=verbose” into the Request headers textbox as shown below. If you recall the HTTP interlude from part 6, this tells SharePoint to bring back the data in JSON format, rather than XML.

image

Click execute and Fiddler will send the request to the server. It will also switch to inspector mode for you to see the request and the response as it happened. If you look closely at the response (by clicking the JSON tab), you will see a structure that looks a bit like that I have pasted below. This is the same data that is now being stored in the ProcessOwnerList dictionary variable.

image

So why did the workflow report that there was only one item in the dictionary then? The short answer is, because there is only one item in the dictionary! A dictionary called “d”. To understand better, take another look at the JSON structure below. What is the first entry in the JSON data? a section called “d”. If you were to collapse d, all other data would be inside it. Therefore, as far as the dictionary variable is concerned, there truly is only one entry.

- JSON
  + d

Note: In case you are wondering what the deal is with the whole “d” thing, given that the it appears to be somewhat redundant. The answer that Microsoft oData standards stipulate that all data returned is wrapped by an outer “d”. Why you may ask? Well you really don’t want to know, but if you must insist, it is for security reasons related to JavaScript (see, I told you that you didn’t want to know!).

So if there is only one entry in the dictionary, than how can we get to the data we need buried deeper? The answer is that this is one of those “dictionary in a dictionary” situations that I described in part 6. The dictionary variable ProcessOwnerList has a single item. That item happens to be another dictionary with multiple items in it. So our first task is to get to the inner dictionary that contains the data we need!

Getting to the real count…

Now we will make use of the Get Item from a Dictionary action to get stuff under the d branch. Looking at the fiddler JSON output above, we need to get to the children of the results branch.

Step 1:

In the Find Matching Process Owner stage, add a Get Item from a Dictionary action as the first action in the stage.

image

Step 2:

Click the item by name or path hyperlink and type in “d/results”. Note: This is case sensitive, so has to match the JSON data shown in the fiddler screenshots. Click the dictionary hyperlink and choose the ProcessOwnersList dictionary variable as the source data.

image

Step 3:

Click the item hyperlink and choose the create a new variable (the bottom option in the dropdown). Call the variable InnerProcessOwnerList and make sure it is set to type dictionary.

image

Step 4:

Now we need to modify the count items action to count the new variable InnerProcessOwnerList. In the Count Items action below the action we just created, click the Variable: ProcessOwnersList and change it to InnerProcessOwnerList.

image

Right! Let’s retest this workflow by republishing it and running it. Now that’s more like it!!!

image

Building the loop…

Now we get to a powerful new feature of workflows in SharePoint 2013 – the ability to perform loops. What we need to do here is loop through the 9 process owners, and for each, compare the GUID of the Organisation column to the GUID on the document from which the workflow was triggered. If the term matches, we exit the loop and assign a task to the person in the Assigned To column.

Let’s go through the process step by step.

Step 1:

Make sure that your cursor is below the last action in the Find Matching Process Owner stage. Add a Set Workflow Variable action. Click the workflow variable hyperlink and choose to create a new variable. Call the variable loop and make it an integer. Click the value hyperlink and set it to zero “0”.

image  image

We are going to use this variable in the looping section  of the workflow to get to each process owner. It’s purpose will become clear soon…

Step 2:

In the ribbon, click the Loop button and choose Loop with Condition. This will add a loop step into the stage. The name “1” is not exactly meaningful so lets change it. Click the title bar of the loop and change the name to For each process owner…

image  image

Now we will set the condition for how long this loop will run for. Note that we have a variable called count that stores the number of entries in the process owners list and in step 1, we created a variable called loop. We will set the workflow to loop around while the value of loop is less than the value of count.

Step 3:

Click the leftmost value hyperlink. In the LoopCondition Properties dialog, click the fx button for the top value. In the Define Workflow Lookup dialog, choose Workflow Variables and Parameters as the data source and find the variable called loop. Click the equals dropdown and choose Is less than. Finally, click the fx button for the bottom value. In the Define Workflow Lookup dialog, choose Workflow Variables and Parameters as the data source and find the variable called count.

image  image  image   image

image

Step 4:

Inside the newly created loop, add a Get Item from a Dictionary action. Click the item by name or path hyperlink and click the ellipses button to display the string builder. Type in a left bracket “(“ and then click the Add or Change lookup button. In the Lookup for String dialog, choose Workflow Variables and Parameters as the data source and find the variable called loop  and click OK. Finally, type in the following string “)/Organisation/TermGuid”.

image

If you look closely at this string, it is referring to the TermGuid in the JSON data. The value of loop (currently 0) will be used to create the string. Hence “(0)/Organisation/TermGuid” will be used to grab the first Organsiation GUID, (1)/Organisation/TermGuid for the second and so on…

Step 5:

Click the dictionary hyperlink and choose InnerProcessOwnerList.

Step 6:

Click the item hyperlink and choose to create a new variable. Call the variable ProcessOwnerTermGUID and set it to a string.

image  image

By this stage, we should have the value of the term GUID for the process owner. Let’s log the value to workflow history so we can confirm things are working…

Step 7:

Add a log to workflow history action and click the message hyperlink. Click the fx button and in the Lookup for String dialog, choose Workflow Variables and Parameters as the data source and find the variable called ProcessOwnerTermGUID.

Step 8:

Now we need to increment the value of the loop variable so it can select the next process owner from the InnerProcessOwnerList variable. Add a Do calculation workflow action, click the first value hyperlink and click the fx button. In the Lookup for Number dialog, choose Workflow Variables and Parameters as the data source and find the variable called loop. Click the second value hyperlink and type in the value “1”.

image

Note that this action creates a new workflow variable (called calc1 by default) that stores the value of loop + 1. We will use this variable in the next workflow step.

Step 9:

Add a Set Workflow Variable action. Click the workflow variable hyperlink and choose loop. Click the value hyperlink and click the fx button. In the Lookup for Number dialog, choose Workflow Variables and Parameters as the data source and find the variable called calc1.

image

Right, that should be enough to test. The each iteration of the loop will log the Organisation term GUID for each process owner. It then increments the value of loop and does it again. Once the value of loop is equal to the value of the variable count, the loop should finish. So publish the workflow and let’s see what happens.. Brilliant! Below the count of process owners (line 3), we have looped though the process owners list and extracted the GUID for the organisation term!

image

Conclusion…

Well, it has taken us a while to get here, but we now have all of the information we need to be able to assign a task to the process owner. if you look in the log above, the first entry was the GUID for the organisation assigned to the document this workflow was run against. Scanning the list of GUID’s from the process owners list, we can see that the matching GUID was on line 5. So in the next post, we will modify our loop to stop when it has a match, and we will examine what we need to do to assign a task to the appropriate process owner.

Until then thanks for reading…

Paul Culmsee

HGBP_Cover-236x300.jpg

www.hereticsguidebooks.com

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

No Tags

Send to Kindle

Trials or tribulation? Inside SharePoint 2013 workflows–Part 5

This entry is part 5 of 13 in the series Workflow
Send to Kindle

Hi and welcome to part 5 of my series of articles that take a peek under the hood of SharePoint 2013 workflows. These articles are pitched at wide audience, and my hope is that they give you some insights into what SharePoint Designer 2013 workflows can do (and cannot). This is a long tale of initially dashed hopes and then redemption, based around what I think is a fairly common business scenario. To that end, the scenario we are using for this series is a basic document approval workflow for a fictitious diversified multinational company called Megacorp. It consist a Documents library and Process Owners list. A managed metadata based site column called Organisation has been added to each of them. In the second post we created a very basic workflow, using the task approval action. In part 3 and part 4, we have been trying to get around various issues encountered. At the end of the last post, we just learnt that Managed Metadata column cannot be filtered via the REST calls used by the built-in SharePoint Designer workflow actions.

…or can they?

In this post, we are going to take a look at two particular capabilities of the new SharePoint 2013 workflow regime and see if we can use them to get out of this pickle we are in. Once again, a reminder that this article is pitched at a wide audience, some of whom are non developers. As a result I am taking things slow…

Capability 1: Dictionaries

SharePoint workflows have always been able to store data in variables, which allows for temporary storage of data within the workflow. When creating a variable, you have to specify what format the data is in, such as string, integer or date. In SharePoint 2013, there is a new variable type called a Dictionary. A dictionary can be used to store quite complex data, because it is in effect, a collection of other variables. Why does this matter? Well, consider this small snippet of XML data below. I could store all of this data in a single dictionary variable and call it CD, rather than multiple stand-alone variables.

Snapshot  <CD>
    <ARTIST>Bob Dylan</ARTIST>
    <COUNTRY>USA</COUNTRY>
    <COMPANY>Columbia</COMPANY>
    <YEAR>1985</YEAR>
  </CD>

Now storing complex data in a single variable is all well and good, but what about manipulating it once you have it? As it happens, three new workflow actions have been specifically designed to work with dictionary data, namely:

  • Build Dictionary
  • Get an Item from a Dictionary
  • Count Items in a Dictionary

The diagram below illustrates these actions (this figure came from an excellent MSDN article on the dictionary capability). You will be using the “Build Dictionary” and “Get an item from a dictionary” actions quite a bit before we are done with this series.

image

There is one additional thing worth noting with dictionaries. Something subtle but super important.  A Dictionary can contain any type of variable available in the SharePoint 2013 Workflow platform, including other dictionary variables! If this messes with your head, let’s extend upon the XML example above of the Bob Dylan album. Let’s say you have an entire catalog of CD’s. For example:

<CATALOG>
   <CD>
     <ARTIST>Bob Dylan</ARTIST>
     <COUNTRY>USA</COUNTRY>
     <COMPANY>Columbia</COMPANY>
     <YEAR>1985</YEAR>
   </CD>
   <CD>
     <ARTIST>Keith Urban</ARTIST>
     <COUNTRY>USA</COUNTRY>
     <COMPANY>Columbia</COMPANY>
     <YEAR>2006</YEAR>
   </CD>
</CATALOG>

Using a dictionary, we can create a single variable to store details about all of the CD’s in the catalog. We could make a Dictionary variable called Catalog, which contains a dictionary variable called CD. The CD variable contains the string and date/time details for each individual CD. This structure enables the Catalog dictionary to store details of many CD’s. Below is a representation on what 3 CD’s would look like in this model…

Snapshot

Okay, so after explaining to you this idea of a dictionary, you might be thinking “What has all of this got to do with our workflow?” To answer that, have another look at the JSON output from part 4 in this series. If you recall, this is the output from talking to SharePoint via REST and asking for all documents in the document library. What do you notice about the information that has come back? To give you a hint, below is the JSON output I will remind you what I said in the last post…

Now let’s take a closer look at Organisation entry in the JSON data. What you might notice is that some of the other data entries have data values specified, such as EditorID = 1, AuthorID =  1 and Modified = 2013-11-10. But not so with Organisation. Rather than have a data value, it has sub entries. Expanding the Organisation section and you can see that we have more data elements within it.

image_thumb14  image_thumb16

In case it is still not clear, essentially we are looking at a data structure that is perfectly suited to being stored in a dictionary variable. In the case of the Organisation column, it is a “dictionary within a dictionary” scenario like my CD catalog example.

Okay, I hear you say – “I get that a dictionary can store complex data structures. Why is this important?”

The answer to that my friends, is that there is a new, powerful workflow action that makes extensive use of dictionaries. You will come to love this particular workflow action, such as its versatility.

Capability 2: The one workflow action to rule them all…

image

In part 3 and part 4 of this series, I have shown examples of talking to SharePoint via REST webservices and showing what the returning JSON data looks like. This was quite deliberate, because In SharePoint 2013, Microsoft has included a workflow action called Call HTTP Web Service to do exactly the same thing. This is a huge advance on previous versions of SharePoint, because it means the actions that workflows can take are only limited by the webservices that they talk to. This goes way beyond SharePoint too, as many other platforms expose data via a REST API, such as YouTube, Ebay, Twitter, as well as enterprise systems like MySQL. Already, various examples exist online where people have wired up SharePoint workflows to other systems. Fabian Williams in particular has done a brilliant job in this regard.

The workflow action can be seen below. Take a moment to examine all of the bits you need to fill in as there are several parameters you might use. The first parameter (the hyperlink labelled “this”) is the URL of the webservice that you wish to access. The request parameter is a dictionary variable that is sometimes used when making a request to the webservice. The response and responseheaders variables are also dictionaries and they store the response received from the webservice. The responseCode parameter represents the HTTP response code that came back, which enables us to check if there was an error (like a HTTP 400 bad request).

image

Dictionaries and web services – a simple example…

The best way to understand what we can do with this workflow action (and the dictionary variables that it requires) is via example. So let’s leave our document approval workflow for the time being and quickly make a site workflow that calls a public webservice, grabs some data and displays it in SharePoint. The public webservice we will use is called Feedzilla. Feedzilla is a news aggregator service that lets you search for articles of interest and bring them back as a data feed. For example: the following feedzilla URL will display any top news (category 26) that has SharePoint in the content. It will return this information in JSON format:

http://api.feedzilla.com/v1/categories/26/articles/search.json?q=SharePoint

Here is a fiddler trace of the above URL, showing the JSON output.  Note the structure of articles, where below the JSON label at the top we have articles –> {} and then the properties of author, publish_date, source, source_url, summary, title and url.

image

Therefore the string “articles(0)/title” should return us the string “Microsoft Certifications for High School Students in Australia (Slashdot)” as it is the title of the first article. The string articles(1)/title should bring back “Microsoft to deliver Office 2013 SP1 in early ’14 (InfoWorld)” as it is the second article. So with this in mind, let’s see if we can get SharePoint to extract the title of the first article in the feed.

Testing it out…

So let’s make a new site workflow based workflow.

Step 1:

From the ribbon, choose Site Workflow. Call the site workflow “Feedzilla test” as shown below…

image  image

Now we will add our Call HTTP Web Service action. This time, we will add the action a different way.

Step 2:

Click the flashing cursor underneath the workflow stage and type in “Call”. As you type in each letter, SharePoint Designer will suggest matching actions. By the time you write “call”,  there is only the Call HTTP Web Service action to choose from. Pressing enter will add it to the workflow.

image

image

Step 3:

Now click on the “this” hyperlink and paste in the feedzilla URL of: http://api.feedzilla.com/v1/categories/26/articles/search.json?q=SharePoint. Then click OK.

image

Next, we need to create a dictionary variable to store the JSON data that is going to come back from Feedzilla.

Step 4:

Click on the “response” hyperlink next to the “ResponseContent to” label and choose to Create a new variable…

image

Step 5:

Call the variable JSONResponse and confirm that it’s type is Dictionary. We are now done with the Call HTTP web service action.

image  image

Step 6:

Next step in the workflow is to extract just the article title from the JSON data returned by the web service call. For this, we need to use the Get an Item from a Dictionary action. We will use this action to extract the title property from the very first article in the feed. Type in the word “get” and press enter – the action we want will be added…

image

Step 7:

In the “item by name or path” hyperlink next to the Get label, type in this exactly: articles(0)/title as shown below. Then click on the “dictionary” hyperlink next to the from label and choose the JSONResponse variable that was created earlier. Finally, we need to save the extracted article title to a string variable. Click on the “this” hyperlink next to the Output to label and choose Create a new variable… In the edit variable screen, name the variable ArticleTitle and set its Type to String.

image

image

Step 8:

The next step is to log the contents of the variable ArticleTitle to the workflow history list. The action is called Log to History List as shown below.  Click the “message” hyperlink for this action and click the fx button. Choose Workflow Variables and Parameters from the Data Source dropdown and in the Field from Source dropdown, choose the variable called ArticleTitle. Click OK.

image

image  image

Step 9:

Finally, add a Go to End of Workflow action in the Transition to Stage section. The workflow is now complete and ready for testing.

image

Testing the workflow…

To run a site workflow, navigate to site contents in SharePoint, and click on the SITE WORKFLOWS link to the right of the “Lists, Libraries and other Apps” label. Your newly minted workflow should be listed under the Start a New Workflow link. Click your workflow to run it.

image  image

The workflow will be fired off in the background, and you will be redirected back to the workflow status screen. Click to refresh the page and you should see your workflow listed as completed as shown below…

image

Click on the workflow link in the “My Completed Workflows” section and examine the detailed workflow output. Look to the bottom where the workflow history is stored. Wohoo! There is our article name! It worked!

image

Conclusion…

It was nice to have a post that was more tribulation than trial eh?

By now you should be more familiar with the idea of calling HTTP web services within workflows and parsing dictionary variables for the output. This functionality is really important because it opens up possibilities for SharePoint workflows that were previously not possible. For citizen developers, the implication (at least in a SharePoint context) is that understanding how to call a web service and parse the result is a must. Therefore all that REST/oData stuff you skipped in part 4 will need to be understood to progress forward in this series.

Speaking of progressing forward, in the next post, we are going to revisit our approval workflow and see if we can use this newfound knowledge to move forward. First up, we need to find out if there is a web service available that can help us look up the process owner for an organisation. To achieve this, we are going to need to learn more about the Fiddler web debugging tool, as well as delve deeper into web services than you ever thought possible. Along the way, SharePoint will continue to put some roadblocks up but fear not, we are turning the corner…

Until then, thanks for reading and I hope these articles have been helpful to you.

Paul Culmsee

HGBP_Cover-236x300.jpg

www.hereticsguidebooks.com

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

No Tags

Send to Kindle

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