Execute SQL and Web Request Examples for Office 365

I am going to share configuration examples of Execute SQL and Web Request workflow actions for Office 365. The example I used is based on the previous post by Dan Stoll on Sign your name across my heart and my previous post on Extending Nintex Mobile Signature control to desktop/browser to demonstrate how the Nintex Signature Control example could be used for a Shipping or Delivery Order scenario of business process. I have simply modified the Title control to enable the barcode scanning feature, and renamed the label to Item ID as shown in the below setting of the Title (i.e. Single Line Textbox control).

The outcome of that is shown in the diagram below, which could be used as a Delivery Form to which a delivery man could use to scan the Bar code or QR Code of a parcel or item delivered, and get the consignee to sign the Delivery Form for confirming the parcel/items have been delivered.

Once the form data had been submitted and sync back to the Sharepoint on Office 365, a workflow will be triggered to send Delivery Tracking Status to inform delivery status over email and/or SMS. Two workflow actions will be used in this scenario to make the process complete:

  1. Execute SQL action for querying the email and mobile number from SQL Server,
  2. Web Request action for calling a web service provided by SMS Gateway service provide to send SMS message(s).

Here is how the Database table looks like, contains the Email and SMS registered for the Delivery Status Tracking purpose. The database in my example is in an on-premise environment, the Execute SQL will be querying from office 365 to the on-premise environment, you will need to make sure the SQL database server is reachable to your tenant from the cloud.

The “Send Delivery Tracking Status” workflow in my example starts with the Execute SQL action and its configuration as shown.

The Execute SQL asction required two mandatory parameters – Connection String and Query. For quick access to the returned results of the Query, you can specify a single column values you want to retrieve (i.e. specify in “Column to retrieve” parameter) from your Select statement, the values are saved to a collection variable (i.e. specified in “Retrieved column values” parameter – colEmail in my example). The parameter of “Results in XML” is used to store the returned results of the Query in array/collection of XML (i.e. I used colXML collection variable in my example to store the entire returned results from the SQL Statement).

The Web Request action in this example is necessary as we need to call a SMS Gateway function provided by third party to send SMS messages. The web service I used in my example is provided by Clickatell (i.e. www.clickatell.com) supports different Web API, one of them being SOAP API. As such I am using the Web Request action to call the SOAP web service for sending SMS message(s).

The WSDL Location provided by the service provider for accessing the web service is:

WSDL: http://api.clickatell.com/soap/document_literal/webservice?wsdl

This is what we need for the URL value of the Web Request action, but without the ?wsdl at the end of the URL. That is only needed when getting the schema.

The Method we going to use will be SOAP 1.2 as this is a SOAP API with supports of SOAP 1.2 standard provided by the SMS service provider.

The following sample request body was given for sending SMS messages.

<?xml version="1.0" encoding="UTF-8"?> <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="http://api.clickatell.com/soap/document_literal/webservice"> <SOAP-ENV:Body><ns1:sendmsg> <api_id>123456</api_id> <user>MyUserName</user> <password>MyPassword</password> <text>This is my message here!</text> <to>2799900001</to> <to>2799900002</to> </ns1:sendmsg> </SOAP-ENV:Body> </SOAP-ENV:Envelope>‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

What we need is just to replace the parameter values and use it as the Body of the Web Request configuration as shown in the diagram below.

The completed workflow of my example is shown in the below workflow diagram, which performs two branches of State Machine,

  1. State 1 is to send an Email, followed by
  2. State 2 to send SMS via the Web Request action.

Get Item from Collection” ① is to retrieve the first Email value from the collection variable of colEmail (i.e. retrieved values in the Execute SQL action), here is how the action is being configured. The Email value is stored in variable txtEmail in my case.

Get Item from Collection” ②is to retrieve the first XML content from the XML collection (i.e. colXML collection variable), the result is stored in txtXML text variable, here shows how the action is being configured.

The above action will give us below XML that is stored in txtXML workflow variable.

Query XML” ③is being configured to retrieve the Mobile number from the above XML content, and stored the value in txtMobile workflow variable.

That’s all we need to build a Delivery Form and  Delivery Status Tracking Notification process. Hope this example gives a good reference on how the Execute SQL and Web Request actions of Nintex Workflow for Office 365 can be configured.

Why do I need Nintex Workflow for Sharepoint?

I was asked a lot in the past, since in Sharepoint by default supports creation of workflows to support business process automation, why is there a need to consider Nintex Workflow? In this write up, I am trying to revisit the approaches and steps involved in Sharepoint workflows. With the summary on that, I am hoping to identify key challenges one might face, or benefits one could get if Nintex is in place for the same purpose.

I was asked a lot in the past, since in Sharepoint by default supports creation of workflows to support business process automation, why is there a need to consider Nintex Workflow? In this write up, I am trying to revisit the approaches and steps involved in Sharepoint workflows. With the summary on that, I am hoping to identify key challenges one might face, or benefits one could get if Nintex is in place for the same purpose.

Let’s revisit the approaches in implementing workflow in default Sharepoint environment. To simplify, I grouped the approaches into three as summarized in the table below:

Out-of-the-box (OOB)WorkflowsAutomate common business tasks such as getting approval or collecting feedback on documentsWithin Sharepoint environment  via browser
Custom WorkflowsDefine from scratch with customer business process logic or states. Opportunity to assign own workflow actions that are made available default Sharepoint installationSharepoint Designer
Custom Workflows with customer actions/activitiesIn the event if the default workflow actions do not fulfilled the business process steps requirement. Programmers can encapsulate customer code in new actions.Visual Studio

Out-of-the-box Workflows

OOB workflows are pre-programmed workflows that are included in Sharepoint, OOB workflows are template driven workflows allowing one to select options with the initiation form when adding the workflow to list or library in a Sharepoint site. Different version of Sharepoint comes with different set of OOB workflows,  Sharepoint 2013 come with – Approval, Collect Feedback, Collect Signatures, Disposition Approval, and Three-State OOB workflows. Here is the link to an Overview of workflows included with Sharepoint provided by Microsoft.

Adding a OOB workflow to a list or library can be achieved by just select the “Add a Workflow” from the list or library’s “Workflow Settings” menu in the Ribbon menu. The “Add a workflow” configuration page will be presented allowing one to select a OOB workflow and other configuration values. This is illustrated in the diagram below,

Add a Workflow.png

The steps usually involved with an initiation form allow additional assignment of required parameters such as Approvers to be used for the workflow to send or assign a task to. This is illustrated in the diagram below.

new workflow.png

OOB Workflows – Key Challenges

OOB Workflows provide a great way to business users for automating business tasks that are seen commonly across different organizations, these are processes such as getting an approval, sharing and collecting feedback on documents. Unfortunately, when it comes to actual implementation, the majority of the time we will need to do more than just getting an approval or changing the status of the document from “Under review” to “Approved”.  Most of the time we would need additional tasks to be accomplished such as:

  • Sending a notification to the initiator when the item is being assigned to someone for approval
  • Copying the document to a destination repository when it was approved or final
  • Etc.

You will realize too, you will need to hard code the “Reviewers” in the above “Approval” workflow sample by selecting a person whom you want the document to be assigned to. This could cause a huge maintenance issue or effort if the approver left the organization or switches roles, which happens very often in today’s business environment.

Due to this, I find a lot of time we would need to use a Custom Workflow instead of the OOB Workflow that comes with Sharepoint, as it allows us to add own workflow action(s) or steps according to the need for a business process.

Custom Workflow

The second approach for workflow implementation in Sharepoint is the Custom Workflow, which gives us the flexibility to define the steps needed for a business process automation. Sharepoint comes with a set of out-of-the-box Workflow Actions to support the common business process automation. The following is a grouped list of Actions available in the default Sharepoint 2013 installation.

Core ActionsAdd a CommentAdd Time to DateDo CalculationLog to History ListPause for DurationPause until DateSend an EmailSet Time Portion of Date/Time FieldSet Workflow StatusSet Workflow VariableStop WorkflowDocument Set Actions (not available in SharePoint Foundation)Capture a version of the Document SetSend Document Set to RepositorySet Content Approval Status for the Document SetStart Document Set Approval ProcessList ActionsCheck In ItemCheck Out ItemCopy List ItemCreate List ItemDeclare RecordDelete ItemDiscard Check Out ItemSet Content Approval StatusSet Field in Current ItemUndeclare Record (not available in SharePoint Foundation)Update List ItemWait for Field Change in Current ItemRelational Actions (not available in SharePoint Foundation)Lookup Manager for a UserTask ActionsAssign a Form to a GroupAssign a To-do ItemCollect Data from a UserStart Approval Process (not available in SharePoint Foundation)Start Feedback Process (not available in SharePoint Foundation)Utility ActionsExtract Substring from End of StringExtract Substring from Index of StringExtract Substring from Start of StringExtract Substring of String from Index with LengthFind Interval Between Dates

The process involved in implementing a Custom Workflow in Sharepoint can be summarized in the below sequence:

1. Authoring and Deploying a workflow

Defining the process by adding Stages/Steps, Workflow actions, activities, and conditions. Once a Workflow is defined, it will be deployed to Sharepoint as a workflow template available for associating to the list or libraries.

2. Associating a Workflow

A Workflow Template is to be attached/associated to Sharepoint list or content type before in instance can be created or executed.

3. Instantiating a Workflow

Workflow is being executed or instantiated either by manual triggered or when an item is being created or changed.

The designing or authoring of a Custom Workflow has to be done using the Sharepoint Designer IF and ONLY IF encapsulation or coding new Actions is not required to fulfill the business process automation. In the event of new actions being created for the workflow authoring purpose, the programmer will need to create the custom action(s) using Visual Studio for the purpose.

Creating a workflow by using Sharepoint Designer 2013 can be found in the Microsoft MSDN site, details how to install, open, and create a workflow by using SharePoint Designer 2013 and the SharePoint 2013 Workflow platform. In summary it involves steps to

  1. Install Sharepoint Designer (i.e. if it has not been installed in your Sharepoint environment)
  2. Open or connect to a Sharepoint site in the Sharepoint Designer
  3. Creating workflow in the Sharepoint Designer for a Sharepoint environment

The key activities involve in creating a workflow using Sharepoint Designer covers add Actions, Conditions, Stages, Steps, and Loops to build your workflow. These workflow components are available in the ribbon of SharePoint Designer 2013, as shown in the following figure


Custom Workflow – Key Challenges

The Sharepoint Designer extends the capability not just to create Custom Workflow in supporting business process automation, it also provides other functionalities to support Sharepoint site customization such as Page Layout or Page authoring, etc. Unfortunately, when looking at just creating Custom Workflow, the following drawbacks are the key challenges most users find:

  • Sharepoint Designer is seen as a designer tool in general Sharepoint site customization instead of just focusing for Workflow development or designing purpose.
  • The business process owner or designer will need to install Sharepoint Designer to design Custom Workflow
  • By default the Sharepoint Designer supports only the authoring of workflow in a declarative rules-based and text definition/statements, which is not “flowchart” graphical design experience. “Flowchart” style is only available since Sharepoint Designer 2013 but being supported by additional Visio application that will need to be installed separately.
  • The need to install additional software(s) to support the Custom Workflow is one key challenges. But the actual issue with workflow implementations is the lack of Workflow Actions supporting the business process automation. Majority of time, when you are creating a Custom Workflow, the default out-of-the-box Workflow Actions (i.e. less than 40 as listed in the previous paragraph) are not sufficient to fulfill the requirement of business process automation, and then IT programmers are loaded with the efforts to program custom actions using Visual Studio.

Nintex Workflow advantages

Just to highlight a few, here is how Nintex Workflow for Sharepoint helps in supporting the workflow design process:

  1. Business Process owner or developer can now accomplish the Custom Workflow design process without leaving the Sharepoint environment (i.e. within the browser environment).
  2. Nintex Workflow designer supports the workflow design process in a quick and easy manner.
  3. Nintex Workflow designer is embedded into a Sharepoint site, without the need for workflow developer or process owner to install additional software to design workflow.
  4. Nintex Workflow comes with over 160 default workflow actions to support business process automation, and additional actions can be downloaded from the online “Catalog” (i.e. Nintex store).
  5. Nintex Workflow encourages no custom coding of custom actions required, this is achieved with the rich set of Workflow Actions that come with Nintex Workflow installation. In the event that a custom action coding is needed, the SDK provided allows programmers to develop custom actions.
  6. The workflow design in a “flowchart” graphical format provides self-explanatory to business users, and reduces tremendous maintenance efforts when it come to changes to the business processes.