How to parse JSON data in Nintex Workflow for Office 365

A workflow is usually described as a series of tasks that produce an outcome. In the context of Microsoft SharePoint Products and Technologies, a workflow is defined more precisely as the automated movement of documents or items through a specific sequence of actions or tasks that are related to a business process. SharePoint Workflows can be used to consistently manage common business processes within an organisation by allowing the attachment of business logic that is set of instructions to documents or items in a SharePoint list or library.

Nintex Workflow is one of the most popular 3^rd party workflow products, it adds a drag-and-drop workflow designer, advanced connectivity, and rich workflow features to give customers more power and flexibility. Nintex Workflow Products have Nintex Workflow for SharePoint and Nintex for Office 365. Nintex for SharePoint is targeted to SharePoint on premises and Nintex Workflow for Office 365 has seamless integration with Office 365 so you can use a browser based drag-and-drop workflow designer but have limited workflow activities compared to the on premises product.

Prior to SharePoint 2013, many organisations streamlined business process by building an InfoPath form and SharePoint workflow. With InfoPath being deprecated soon by Microsoft developers need to move away from that technology for their basic forms needs. A great InfoPath alternative is HTML fields form and store the fields as a JSON object in one field in the SharePoint list.

Nintex Workflow for Office 365 hasn’t released an activity in the workflow that can parse the JSON format, and there is no easy solution to get the JSON value out except using Regular Expression activity which is hard to maintain or update if JSON object structure changes. In July 2015 Product Release, Nintex Workflow contains a new feature – Collection Variable, it is extremely powerful and can speed up workflow design dramatically.

Below are the actions that are available in the cloud:

• Add Item to Collection
• Check if Item Exists in Collection
• Clear Collection
• Count Items in Collection
• Get Item from Collection
• Join Items in Collection
• Remove Duplicates from Collection
• Remove Item from Collection
• Remove Last Item from Collection
• Remove Value from Collection
• Sort Items in Collections.

I will walk you through a scenario how we can read the JSON data from the Nintex Workflow in the cloud. Below is a simple HTML form which contains basic employee information and it saves the form data as JSON to the field of a SharePoint list.

This is how it looks like JSON data stored in the SharePoint list field “Form Data“,

[code language=”javascript”]
{
"employeeDetails":
{
"dateChangeEffectiveFrom":"2015-09-22T21:49:15.866Z",
"dateChangeEffectiveTo":"2015-12-25T00:00:00.000Z",
"employee":
{
"employeeId":"2318",
"employeeFullName":"Ken Zheng",
"positionTitle":"Developer",
"departmentDescription":"IT"
}
}
}
[/code]

If you put in a JSON Parser it will give you better understanding the structure of the data, so you can see it contains an “employeeDetails” property which has “dateChangeEffectiveFrom“, “dateChangeEffectiveTo” and “employee“. And “employee” contains “employeeId“, “employeeFullName“, “positionTitle” and “departmentDescription” properties. In this example, we are going to get the value of “employeeFullName” from the JSON data.

Step 1: Create two Collection Variables: ‘FormData’ and ’employee’, a Dictionary variable for ’employeeDetails’ and a text variable named ‘employeeFullName’ in the Nintex Workflow Designer

Step 2: Set “FormData” variable to the value of “Form Data” field of the list:

Step 3: Use “Get an Item From A Dictionary” activity to set “employeeDetails” variable value, Item name or path must be the child element name

Step 4: Then add another “Get an Item From A Dictionary” activity to get Employee collection, because “employee” is the child of “employeeDetails“. You need to select “employeeDetails” as source Dictionary:

Step 5: Then another “Get an Item From A Dictionary” activity to get Employee Full name (Text variable)

The completed workflow diagram will look like this

Now if you log the variable “employeeFullName“, you should get value “Ken Zheng”.

With this Nintext Workflow Collection Variables feature we can now parse and extract information from JSON data field in a SharePoint list back into a workflow without creating separate list fields for each properties. And it can easily handle looping properties as well.