Trials or tribulation? Inside SharePoint 2013 workflows–Part 4

Hi and welcome to part 4 of my series of articles that take a peek under the hood of SharePoint 2013 workflows from . In part 1, I introduced you to Megacorp Inc and their need for a controlled documents approval workflow. In part 2, we created a basic SharePoint 2013 workflow and in part 3, we made our first attempt to publish the workflow. Unfortunately, we encountered an error and had to work our way around some particularly unhelpful error messages. Now we are at part 4, and we will have another go at publishing our workflow from part 2.

Now like the last post, I’ll tell you up front that our second attempt to run this workflow is not going to work. Remember that my intent here is to show you a “warts and all” view of this functionality – both the great bits and the not so great bits. I hope that this gives you development and troubleshooting ideas in your own workflow adventures.

If you have been following along so far, you should have a simple workflow like the one below. It is attempting to assign a task to a nominated process owner for controlled documents. We just fixed a configuration issue in part 3 that prevented the workflow from working. We did this by disabling the default behaviour of the workflow where it updates the workflow status with the current stage name.

So let’s run the workflow on the same document as part 3 – the Burger Additives Standards for Megacorp GM Foods. Do we have liftoff yet? Nope – the workflow was cancelled as shown below, with another cryptic message.

RequestorId: 8ad4a017-7e6f-0d0f-35d2-81c56a05b37c. Details: System.InvalidCastException: The value ‘d/results(0)/Organisation’ cannot be read as type ‘String’. at Microsoft.Activities.GetDynamicValueProperty`1.CheckedRead(String propertyName, DynamicItem value) at Microsoft.Activities.GetDynamicValueProperty`1.Execute(CodeActivityContext context) at System.Activities.CodeActivity`1.InternalExecute(ActivityInstance instance, ActivityExecutor executor, BookmarkManager bookmarkManager) at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)

So what do we make of this message and in particular, “The value ‘d/results(0)/Organisation’ cannot be read as type ‘String’”?. Firstly, you might be wondering what this  “d/Results(0)/Organisation” stuff is  all about. Secondly, even if you do know what that is about, why the hell can’t it be read as type string?

A REST and JSON interlude

For the non developers (and self-described citizen developers) reading this series, I am going to attempt to explain what’s going on here because it is important foundational knowledge. If you are a developer who understands REST/OData and JSON,  feel free to skip this bit because you probably won’t like how I explain it.

First up, remember my dodgy diagram in part 3 that explained how Workflow Manager talks to SharePoint? I made the point that workflow manager uses REST web services to do all of its interactions with SharePoint content. REST is actually a really cool technology, and if you are serious about learning to use SharePoint Designer 2013 workflows you should learn more it.

Let’s put aside our workflow for a second, and instead access a REST webservice ourselves, just like workflow manager does behind the scenes. To do this is easy. Open up internet explorer and turn “feed reading view” off. Then try this URL, adjusting it to your site name:

http://megacorp/iso9001/_vti_bin/client.svc/web/lists/getbytitle(‘Documents’)/Items

If you have done it right, you will get a heap of ugly XML data back in your browser. If this worked then congratulations – you are now a REST guru. You have successfully asked SharePoint to send you information about all documents in the Documents library, including the data stored in the columns. Each <entry> tag in the XML represents a document – and you can collapse these entries as shown below..

<?xml version="1.0" encoding="utf-8" ?> 
- <feed xml:base="http://megacorp/iso9001/_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>76c69d23-d5f3-4cee-a954-9910ad81bd16</id> 
  <title /> 
  <updated>2013-11-27T23:49:25Z</updated> 
+ <entry m:etag=""6"">
+ <entry m:etag=""7"">
+ <entry m:etag=""7"">
+ <entry m:etag=""8"">

If you expand one of those entries, you will see the full detail of that document. Scroll down into the detail and look for the <Content Type> entry in the XML as shown below. This is the same data that your workflow is working with.

- <content type="application/xml">
-   <m:properties>
      <d:FileSystemObjectType m:type="Edm.Int32">0</d:FileSystemObjectType> 
      <d:Id m:type="Edm.Int32">10</d:Id> 
      <d:ContentTypeId>0x010100683F42634030C946A9F8165B365FD886</d:ContentTypeId> 
      <d:Title m:null="true" /> 
      <d:OData__dlc_DocId>DPRYTK5567JW-5-10</d:OData__dlc_DocId> 
-     <d:OData__dlc_DocIdUrl m:type="SP.FieldUrlValue">
        <d:Description>DPRYTK5567JW-5-10</d:Description> 
        <d:Url>http://megacorp/iso9001/_layouts/15/DocIdRedir.aspx?ID=DPRYTK5567JW-5-10</d:Url> 
      </d:OData__dlc_DocIdUrl>
      <d:URL m:null="true" /> 
      <d:DocumentSetDescription m:null="true" /> 
-     <d:Organisation m:type="SP.Taxonomy.TaxonomyFieldValue">
         <d:Label>9</d:Label> 
         <d:TermGuid>f2109460-a473-493f-9d08-fb01ecbf793b</d:TermGuid> 
         <d:WssId m:type="Edm.Int32">9</d:WssId> 
      </d:Organisation>
      <d:Modified m:type="Edm.DateTime">2013-11-10T00:21:45Z</d:Modified> 
      <d:Process_x0020_Owner_x0020_Approval m:null="true" /> 
      <d:ID m:type="Edm.Int32">10</d:ID> 
      <d:Created m:type="Edm.DateTime">2013-11-08T14:33:12Z</d:Created> 
      <d:AuthorId m:type="Edm.Int32">1</d:AuthorId> 
      <d:EditorId m:type="Edm.Int32">1</d:EditorId> 
      <d:OData__CopySource m:null="true" /> 
      <d:CheckoutUserId m:null="true" /> 
      <d:OData__UIVersionString>2.0</d:OData__UIVersionString> 
      <d:GUID m:type="Edm.Guid">c75a0728-7a5f-4236-8d45-7b72fa41781e</d:GUID> 
    </m:properties>
  </content>

Now when you add a workflow action, and Workflow Manager then talks to SharePoint to perform the action, it is doing a very similar thing to the URL we just accessed. The only difference is that when workflow manger does it, it asks for the data to be returned in a different format than XML called JSON – a more lightweight but less human readable data format. Below is a tiny snippet of that the JSON version of the above data looks like – ugh! no wonder XML is the default return format eh?

{“d”:{“results”:[{“__metadata”:{“id”:”71deada5-6100-48a5-b2e3-42b97b9052a2″,”uri”:”http://megacorp/iso9001/_api/Web/Lists(guid’a64bb9ec-8b00-407c-a7d9-7e8e6ef3e647′)/Items(1)”,”etag”:”\”6\””,”type”:”SP.Data.DocumentsItem”},”FirstUniqueAncestorSecurableObject”:{“__deferred”:{“uri”:”http://megacorp/iso9001/_api/Web/Lists(guid’a64bb9ec-8b00-407c-a7d9-7e8e6ef3e647′)/Items(1)/FirstUniqueAncestorSecurableObject”}},”RoleAssignments”

Fortunately, there are plenty of tools out there that parse JSON data and Fiddler is one of them. We will be using Fiddler later in this series, so I will save a detailed introduction to the tool for later. But below is a screenshot of the above JSON data displayed in Fiddler. Now that’s a bit more palatable!

Now that we can read the JSON data in a meaningful way, let’s go back to the error message in the workflow. It stated that “The value ‘d/results(0)/Organisation’ cannot be read as type ‘String’”. Now look in the JSON screenshot above. If you look at the hierarchy and look at the message, we can see now what the message meant. It has a problem with the Organisation entry. Follow the path below the JSON label at the top… We have d –> Results –> {} –> Organisation. This essentially matches the ‘d/results(0)/Organisation’ in the message.

So takeaway number 1 – workflow uses JSON format when it makes REST calls to SharePoint, so learn to recognise a JSON reference when you see it. As a future workflow developer – and later in this series – you will have to learn how to parse JSON data in more fine detail.

Now let’s take a closer look at Organsiation entry in the JSON data above. 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. Note that we do not see the organisation name at all. We have numbers and a GUID – so what gives?

So what was the error again? ‘Organisation’ could not be read as type ‘String’. Kind of makes sense now doesn’t it? The managed metadata column called Organisation doesn’t store the organisation name, but a pointer to the organisation name, as it is specified in the managed metadata term store. The workflow assumes that the data returned from the REST call is going to be string, and cannot handle the format of the data above.

Troubleshooting Attempt #1 – Use Organisation_0

So at this point, you might be thinking “What the hell?” how can I get the name of the Organsiation if it not actually in the data returned by the REST web service call?

Well, if you were paying attention back in part 2, I noted the existence of another column called Organisation_0. This column was listed as one of the columns available from the current item (“Current item” being the document that the workflow was triggered from). It is now time to understand what this column does. To do so, let’s use another workflow action. This time, we will use the Log to History List action. When you add this action, click the fx button and choose Current Item from the Data source dropdown. Then choose Organisation_0 from the Field from source dropdown as shown below.

 

Now if you rerun your workflow and then check the workflow status, you will see what has been logged to the workflow history. Note the description column. Aha! We see our organisation name buried in there.

Now if you look closely, you will notice that we also have the GUID of the managed metadata term. The term and its GUID are separated by a pipe character. Even better, it is in string format (note Return field as dropdown above).

It turns out that when you create a managed metadata column, behind the scenes two columns get created. The second column is a hidden column that is a multi-line of text format. This is the the one with an _0 appended to the end. In other words, the Organisation column only stores the pointers (lookup values) to the term, but this hidden column actually stores the names and Id’s of each term the user has added. So let’s use this column instead because it’s a string format. To do this, return to the task assignment action in our workflow. We still need to get the Assigned To field from the Process Owners list, so we leave that alone. But below that, in the Find the List Item section, we need to make a change.

Unfortunately (and perhaps ominously), the Organisation_0 field seems to only be selectable for the Current Item, because clicking the Field dropdown (which displays all columns for process owners), only lists the Organisation column. Why is this? Well, it appears that hidden columns are displayed on Current item, but not displayed when you specify a different list. Thus, we are forced to leave Organisation from Process Owners as is. So click the fx button next to the Value textbox and choose Current Item from the Data source dropdown and Organisation_0 from the Return field as dropdown as shown below.

 

Now republish the workflow and let’s give it a go. Checking the workflow status screen and we find the workflow is started. Are we onto a winner here?

Gong! there still another of those exciting error messages. This time we have a complaint of a HTTP BadRequest. Given my explanation of how managed Metadata columns work behind the scenes, can you guess what the issue is?

Retrying last request. Next attempt scheduled in less than one minute. Details of last request: HTTP BadRequest to http://megacorp/iso9001/_vti_bin/client.svc/web/lists/getbyid(guid’0ffc4b79-1dd0-4c36-83bc-c31e88cb1c3a’)/Items?%24filter=Organisation+eq+’Metacorp+Burgers%7Ce2f8e2e0-9521-4c7c-95a2-f195ccad160f’&%24select=ID%2CGUID Correlation Id: f16749d5-1bfe-4a8d-9e06-a5b196907e9c Instance Id: 60c538e8-f7a9-4945-919b-ca973c00eb31

Now this error message might look evil, but it is actually the most useful one so far as it shows us the REST call made by the workflow manager as part of the task assignment action. If I remove the encoded spaces to make things more readable, the workflow attempted this call.

http://megacorp/iso9001/_vti_bin/client.svc/web/lists/getbyid(guid’0ffc4b79-1dd0-4c36-83bc-c31e88cb1c3a’)/Items?”filter=Organisation+eq+’Metacorp+Burgers|e2f8e2e0-9521-4c7c-95a2-f195ccad160f’&”select=ID,GUID

This webservice essentially says to SharePoint “Using the Process Owners list (which is GUID 0ffc4b79-1dd0-4c36-83bc-c31e88cb1c3a), please bring me back the ID and GUID of any list entries where the Organisation column is equal to the value “Metacorp+Burgers|e2f8e2e0-9521-4c7c-95a2-f195ccad160f”.

Now even if it cannot find a matching item, this should return a HTTP 200 with 0 items matched. But the error that has been returned (400) suggests that there is a problem with the above request itself. Hmmm – eventually the workflow will give up and cancel the workflow. It then logs a more verbose message…

RequestorId: 8ad4a017-7e6f-0d0f-35d2-81c56a05b37c. Details: System.ApplicationException: HTTP 400 {“error”:{“code”:”-1, Microsoft.SharePoint.SPException”,”message”:{“lang”:”en-US”,”value”:”The field ‘Organisation’ of type ‘TaxonomyFieldType’ cannot be used in the query filter expression.”}}} {“Transfer-Encoding”:[“chunked”],”X-SharePointHealthScore”:[“0″],”SPClientServiceRequestDuration”:[“221″],”SPRequestGuid”:[“a202df2e-69df-4a31-b63f-dac25f84676d”],”request-id”:[“a202df2e-69df-4a31-b63f-dac25f84676d”],”X-FRAME-OPTIONS”:[“SAMEORIGIN”],”MicrosoftSharePointTeamServices”:[“15.0.0.4420″],”X-Content-Type-Options”:[“nosniff”],”X-MS-InvokeApp”:[“1; RequireReadOnly”],”Cache-Control”:[“max-age=0, private”],”Date”:[“Thu, 28 Nov 2013 03:31:09 GMT”],”Server”:[“Microsoft-IIS\/8.0″],”X-AspNet-Version”:[“4.0.30319″],”X-Powered-By”:[“ASP.NET”]} at Microsoft.Activities.Hosting.Runtime.Subroutine.SubroutineChild.Execute(CodeActivityContext context) at System.Activities.CodeActivity.InternalExecute(ActivityInstance instance, ActivityExecutor executor, BookmarkManager bookmarkManager) at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)

Despite the ugliness of the above message, this time we get the root cause logged. Note the section that states:  “The field ‘Organisation’ of type ‘TaxonomyFieldType’ cannot be used in the query filter expression”. So what does this mean?

Another REST interlude…

If you re-examine the REST web service call that was logged by the workflow, you will see some stuff we have not covered so far. The first half of the URL was pretty much what I showed you in the first REST interlude. We are getting all of the items from a SharePoint list, except this time we are using the GUID of the list rather than its name as shown below.

http://megacorp/iso9001/_vti_bin/client.svc/web/lists/getbyid(guid’0ffc4b79-1dd0-4c36-83bc-c31e88cb1c3a’)/Items

But what is this extra stuff tacked on the rest of the URL – $filter and $select as seen below?

$filter=Organisation+eq+’Metacorp+Burgers|e2f8e2e0-9521-4c7c-95a2-f195ccad160f’&$select=ID,GUID

The answer is that Microsoft’s use of REST (called oData) allows you to do SQL like queries to filter the data that comes back. This is really handy indeed and to help you understand it, here is an example: The URL below says “Give me all documents with an a title of ‘Burger Additives Standards’”

http://megacorp/iso9001/_vti_bin/client.svc/web/lists/getbyid(guid’0ffc4b79-1dd0-4c36-83bc-c31e88cb1c3a’)/Items?$filter=Title eq ‘Burger Additives Standards’

This example says “Give me just the Titles of all documents created after November 1 2013”

http://megacorp/iso9001/_vti_bin/client.svc/web/lists/getbyid(guid’0ffc4b79-1dd0-4c36-83bc-c31e88cb1c3a’)/Items?$filter=Created gt datetime’2013-11-01T00:00:00’&$select=Title

Now that you have seen those examples, take another look at what the workflow was trying to do without any luck…

http://megacorp/iso9001/_vti_bin/client.svc/web/lists/getbyid(guid’0ffc4b79-1dd0-4c36-83bc-c31e88cb1c3a’)/Items?”filter=Organisation+eq+’Metacorp+Burgers|e2f8e2e0-9521-4c7c-95a2-f195ccad160f’&”select=ID,GUID

Why did the REST call made by the workflow return a HTTP 400 error given my two working examples that look very similar? The answer is that the $filter option does not work with Managed Metadata columns. As I described in this article previously, managed metadata columns are not compatible with the $filter operator – hence the error message “The field ‘Organisation’ of type ‘TaxonomyFieldType’ cannot be used in the query filter expression.”

Damn!

Conclusion…

So it seems that for every step forward, we have taken a step back again. Fear not though, as the next post will start to show a way forward. But be warned – we are about to get deeper into the bowels of REST/oData, so make sure that you fully understand this article before moving on. To that end, if anything is unclear, please let me know and I will adjust these articles accordingly.

Thanks for reading

Paul Culmsee

www.hereticsguidebooks.com