Tag: Nintex

Calculate a Due Date Based on Business Hours With Nintex Workflow

Published January 16, 2015

More than a few times, I have come across the requirement to calculate a due date based on business hours. Recently I needed such a capability for a help desk application that had been built in Nintex Workflow. It’s not quite as simple as it sounds – it’s the business hours part of it that makes it relatively tricky. After a cursory search, while there were a few that would calculate based on days, I couldn’t find any solutions out there to suit this requirement, so I decided to build my own.

The help desk workflow looks up the type of issue from a list, retrieves the expected close time from a Service Level Agreement (SLA) field, and that is used to calculate the due date (along with alert times). SLAs are in business hours, and the business is measure on overdue times, so it is important that this is accurate.

The workflow itself had been built originally with Nintex Workflow 2007, and a web service had been utilized that served this purpose, but that option was no longer available in the customer’s new SharePoint 2013 environment due to authentication issues. The good news is, Nintex Workflow 2013 contains a feature called User Defined Actions (UDAs) that would allow me to get the job done.

In a nutshell, a UDA allows a workflow developer to encapsulate a workflow, have it accept input parameters have it output results as parameters. Then, that UDA can be used in other workflows as required. It is essentially a workflow function. I was able to successfully build a UDA to do this, and I am providing it here:

Nintex UDA to calculate a due date based on business hours

The basic logic of the UDA goes something like this. Starting with a date, a number of days, hours and minutes, it first calculates the number of minutes that the issue is to be open. The number of days is multiplied by the number of business hours in a day (retrieved from workflow constants), the hours by 60, and these are added to the minutes parameter.

Next, we test to see if it is a business day (determined from a list of holidays and whether the day is a Saturday or Sunday). Next, we see if the date is today, and if we are within business hours. If so, we subtract the remaining minutes in the business day from the minutes outstanding. We then increment the day, and repeat the loop until there are more minutes in the current date than are outstanding. Once that happens, we add the outstanding minutes to the current date, and output is as the due date/time.

The package above contains two files – the UDA itself, and a list template. In order to get the UDA working, you first need to create the Holidays list, and two Nintex Workflow constants. The package contains a list template, Holidays.stp. I’m going to assume that adding a list template to the list template gallery is a well known procedure, and not step through it here, but it needs to be done. Once added, create a new list using the Holidays template, and pops appropriate. It comes with 3 annual holidays already defined, Christmas, Boxing Day, and New Years Day. If the holiday falls on the same date every year, it only needs to be added once, and the “Annual” option checked (the year can be ignored). Otherwise, each holiday must be explicitly added.

Next, two workflow constants need to be created – WorkdayEnd and WorkdayStart. They will use the Number type and represent the number of minutes from midnight that the work day starts and ends. 8:00 AM is 8×60 minutes from midnight and therefore the value is 480.

We then need to import our UDA. To do so, go to Settings – Nintex Workflow – Manage User Actions.

Next import the UDA from the file extracted from the package above (Calculate_Due_Date.uda). Once imported, it will likely be necessary to edit a couple of the UDA actions to connect them to the list and constants. These actions are easy to identify as they will display a yellow alert flag.

You will not be able to publish the UDA until all of these connections are made.

Once published, you can use the UDA in your workflows. To do so, open the User Defined Actions section in your Nintex Workflow Designer, and drag the “Calculate Due Date” action onto the workflow design surface. Next, configure the action.

Supply a starting date/time, the number of days, hours, and minutes to leave it open, and then assign the Due Date parameter to your workflow variable. That’s all there is to it.

There is nothing compiled in the UDA, and you can modify it to meet your needs. Hopefully this helps out a few people looking for this capability.

16 Comments

How to Refresh Data Models in Office 365 from On Premises Using Nintex Workflow – A Hybrid Approach

Published March 13, 2014

One of the viable use cases for Power BI is to provide reporting to a mobile workforce, even when the organization has invested in BI on premises. In this scenario, there may be a SharePoint farm using PowerPivot for SharePoint with multiple workbooks connected to a myriad of data sources, including SQL Server Analysis Services cubes and models.

The Problem

In this scenario, the workbooks required by the mobile users can be copied up to the Office 365 tenant, and consumed from there. However, how is the data kept current? One way to do so is to set up the Power BI Data Management Gateway (DMG) to do this. The DMG is a great solution, but is relatively new, and has a few inherent limitations at the moment. Chief among them is that data can only be refreshed from SQL Server or Oracle data sources. For the moment at least, Analysis Services is left out in the cold. However, PowerPivot for SharePoint doesn’t share these limitations, and has no problem refreshing data from a wide variety of sources. Unfortunately, if you’re using Power BI, and need to refresh from an unsupported data source, you’re out of luck for the moment, so this is a problem.

Keeping a cloud copy of a local workbook carries the inherent problems of managing two different copies of the same workbooks. Every modification must be performed twice and there is always the chance that something will get missed. Ideally, for this scenario, we should have a publishing mechanism. We can use Nintex Workflow (on premises) to provide this publishing mechanism for us. Whenever the model is refreshed, the workflow will fire, and copy the file up to Office 365 to update the model there.

In order to implement this solution, you’ll need a copy of Nintex Workflow. The low end (Team) edition is fine, but what we’re after is the “Copy document to Office 365” action, and that is available through the Nintex Live action set.

The Solution

To start, we’ll work with an Excel workbook that is using an embedded model created from the standard Contoso Data Warehouse. In this example, we’ve built a simple pivot table to interact with the model. The model has been published to a PowerPivot gallery, and the refresh schedule has been set. Once we’ve tested the refresh to ensure that it’s working, we can create the workflow. To do so, we go to the workflow options section in the library ribbon and create a new Nintex workflow.

We then select the Blank template. Once presented with the design canvas, we select Workflow Settings, give it a good name, and set the workflow to run on create and on edit.

Once the settings have been saved, we can start to build the actual workflow. The first action will be a “Pause for” action. Find it in the Workflow Actions toolbar, and drag it onto the design surface. Select configure from its drop down menu, and set it to pause for a period of time, in this case, 5 minutes.

The amount of time that we need to pause for depends on how long it takes our model to refresh. When the refresh operation begins, it “edits” the document, which kicks off the workflow. We don’t want to upload the workbook until the refresh operation is complete, so we need to pause it. In this case, we are working with a data model that is about 100 MB and comprised of about 3 million rows of data. In this environment, it requires about 2.5 minutes to refresh, so a 5 minute pause is sufficient.

Next, we need to use the “Office 365 upload file” action. This action is one of the Nintex Live actions that can be added at no extra charge to the Nintex environment. If it hasn’t already been added to the toolbar, we need to do so. Assuming that Nintex Live has been enabled for the farm, we browse the available actions through the Catalog which is available through the designer ribbon.

Once in the catalog, we can browse the available actions, and click the Add button to add them to the toolbox. The action that we need to add is named “Office 365 upload file”. Once at least one action is added, an new section will appear in the Workflow Actions toolbar named “Nintex Live”. We then open that section and drag the Office 365 file upload action onto the design surface after the pause action, then configure it.

Walking through the options:

File to upload: We want the current file to be uploaded to Office 365, so we select current item
Destination site URL: This is in fact the URL of the site itself. Everything up to, but not including the document library
Folder path: This is the name of the destination document library, and if appropriate, any subfolders
File name: We can create or derive a new name for the file, but in this case, we will be using the same name as the source file
Overwrite existing file: We select this option, as we will be updating existing content.
Fields: We can add additional metadata values if we wish, but in this case, we leave it empty
SharePoint Online URL: this is the URL to the root of the Office 365 tenant for the destination
Username and password are for a proxy account that will be used to upload the file. Secure workflow constants can be used.

Once we configure the action, we save it, our workflow should appear as follows:

and we are ready to publish the workflow. To do so, select the Publish button, then close the workflow designer.

We are now ready to test our workflow. We can force the workflow to run on demand for our workbook, but a better test is to let the refresh trigger it. The most frequently that PowerPivot for SharePoint refreshes can be scheduled to run is once per day. but there is a way to force the refresh to happen on demand.

To force a refresh, we need to edit the refresh schedule for the workbook. Open up the refresh history screen by selecting Manage Data Refresh for the workbook.

From the Refresh history screen, select the Configure Schedule link to edit the schedule. This will allow us to edit the existing schedule, but more importantly, it will also let us force a refresh by selecting the “Also refresh as soon as possible” option.

If we are using a specific account as a credential, we will also need to re-enter that. Once complete, selecting OK will queue the refresh job for execution. The refresh job will happen the next time that the “PowerPivot Data Refresh Timer Job” executes, and it runs every 5 minutes.

If you’re really impatient, and you have access to central administration, you can edit this timer job and force it to run immediately on a case by case basis.

Moving back to the refresh history screen we will notice that the refresh is currently running.

And in the workflow history screen for the workbook, we can see that the workflow is also running.

Once both operations have completed, we can navigate to our Office 365 site, and we will find the file in the destination folder. If our model is greater than 10 MB in size, we will require Power BI in order to interact with it in the browser, and we will need to enable it for use with Power BI through the Power BI application. If it has already been enabled, then no additional actions are required. If the workbook is less than 10MB, it will work directly in any Office 365 library that is enabled for Excel Services.

More Possibilities

If you’ve worked with the Power BI Data Management Gateway at all, you’ll know that data refreshes can be scheduled at most daily. This limitation is also shared by PowerPivot for SharePoint, but it is possible to change its behaviour. Using the techniques outlined in this article by Ian Smith, and this article by Tim Laqua, refreshes can be made as granular as 5 minutes. These articles are written for SharePoint 2010, but the principles work with 2013 (hint, the stored procedure that forces a refresh is [DataRefresh].[AddRefreshSchedule]). Be warned, this technique is a hack, and not supported by Microsoft. However, it can be a way to up the frequency of refreshes on premises, and with the above technique, in Office 365 too.

Another way to refresh the data more often is by using SQL Server Integration Services (SSIS). SSIS can update an embedded data model in an Excel workbook through a script action (thanks to Gobán Saor). SSIS packages can be scheduled with SQL agent jobs, which means that they can be run as frequently as every minute. Another advantage of this approach is that with Excel 2013, this method should be fully supported.

Finally…

By relying on our workflow to publish the refreshed model, we work around the current limitations of the Data Management Gateway. We do incur the cost of a Nintex license, but a few days of development would easily exceed that cost. In fact, if our data model doesn’t exceed 10 MB, we don’t even need Power BI for this solution to work.

1 Comment

Conditionally Starting a Workflow with the Nintex Workflow Filter Action

Published November 13, 2013

For quite some time now, it has been possible to start workflows conditionally with Nintex Workflow. Standard options for starting a workflow are manual start, run when items are created, and run when items are edited. For the latter two, it’s possible to set conditions for the triggers, which is to say “only run when these conditions are satisfied”. This is a great feature, and works in most cases, but I’ve run across cases when it is problematic.

In some cases, if the list item is encountering multiple, simultaneous edits, the underlying SharePoint engine may back off, and place the workflow into a “Starting” state for a period of time (usually until the next timer job run). This also may be OK, but in other cases, this lag may be unacceptable. Another problem is that “these conditions” are restricted to the values available on the item that the workflow is running on. There’s no way to interrogate system values, time values, database values, etc. It may be necessary to perform a few actions before deciding not to run further.

Whenever I’ve encountered these conditions, I swap out the conditional start capability for a filter action. I can’t say that I much like the name of the action (it’s not very descriptive), but it’s quite useful. In essence, what it does is to evaluate a condition, and if the condition fails, the workflow is ended. It’s almost like it never ran at all, which is the effect that we’re trying to achieve.

If I’m just trying to get past the stuck on “Starting” problem, I’ll add a filter action (found in the logic and flow section) to the beginning of the workflow.

Configuring it is straightforward. Simply enter your conditions in the same manner as you would a “Run If” action. You can leverage all of your Nintex workflow design elements – variables, list lookups, context items, constants or user profiles.

In the above a simple item value is interrogated. If its value is greater than zero, the workflow continues, otherwise, it ends. That’s it.

Another nice feature of the filter action is that it can be placed anywhere in the workflow, not just at the beginning, which allows you to conditionally run only part of the workflow. In many cases there are also other ways to accomplish this, but this is quick and easy.

If the standard conditional start options work for you, then by all means continue to use them. However, if you start to run into concurrency problems, or your requirements grow in complexity, you might want to have a look at the filter action. It has worked well for me every time.

2 Comments

Installing Office 365 App : Sorry, there was something wrong with the download

Published October 10, 2013

I recently ran into a bit of trouble installing the Nintex Workflow for Office 365 app to our main Office 365 tenant. Every time I tried to add the app from the store, I would receive the above error, or “Sorry, something went wrong with adding the app”.

With some great help from Nintex support, I was able to sort this out, but when I tried to repeat the process for writing this article, I also received the error:

The app package does not support the culture en-CA specified for installation

Yes, being Canadian has a few disadvantages, but not many. This is one of them. We do have a slightly different English than our cousins to the south.

I tested this on a few other apps and ran into similar problems with them. The problem appears to be a mismatch between the local settings for your tenant, and those explicitly supported by the app. You can work around these problems during installation by selecting en-US as the language. You do this during the app addition process. However, you first need to remove the failed addition.

Hover over the failed app, and click on the ellipsis that appears. Next, click on the ellipsis in the “about” box that pops up, and select remove.

Once it is remove, again begin the process of adding the app. When you come to the trust screen, click on the “SHOW LANGUAGE OPTIONS” link. From there, select English (United States). My default was English (Canada).

Once done, and you click the “Trust It” button, your app should install normally. I am told that this is a bug – I’m not sure if it’s on the Office 365 side or the vendor side, but it should get fixed. In the meantime, hopefully this workaround helps someone.

3 Comments

How To Configure the Nintex Workflow Preview on Office 365

Published August 7, 2012

Anyone that has worked with my company, UnlimitedViz, knows that we’re unabashed fans of Nintex Workflow. As far as I’m concerned, it’s one of the most cost effective pieces of software I’ve ever come across. Projects that would require days or even weeks of consulting using the out of the box features in SharePoint can be accomplished in mere hours with Nintex Workflow, and what’s more, they can be accomplished by power users, not developers.

Another exceptional aspect of Nintex workflow is that is doesn’t replace the out of the box SharePoint workflow engine, it simply enhances it, through a browser based graphical designer/viewer, and multiple custom actions. This design approach really showed its value on several SharePoint/Nintex 2007 to SharePoint/Nintex 2010 upgrades that we’ve performed. We were able to upgrade entire farms and maintain in-flight workflows.

Unfortunately, as I wrote about previously, once we moved our organizational assets to Office 365, we were no longer able to use Nintex Workflow ourselves, because NWF is an on-premise solution. However, with a little redevelopment, changes in the SharePoint development model (for a reference, see Jeremy Thake’s excellent summary) open the doors for third party code to be used with Office 365 sites in addition to on-premise sites.

Happily, these changes have not gone unnoticed by the folks at Nintex, and they are one of the first vendors out of the gate with a solution in the SharePoint store. It’s just a preview of what will be coming very shortly after the release of SharePoint 2013, which is important, because Office 365 sites will be upgraded very shortly after release.

Walkthrough

It should go without saying, but in order to try the Workflow Preview, you’ll need to be using an on premises SharePoint farm that has been enabled for SharePoint 2013 workflows, or using the Office 365 preview. In our case, we’re using the Office 365 preview.

Firstly, you’ll need to add the app to the Office 365 App Catalogue. To do this, go to the Office 365 Admin screen and click on apps.

Next, click on Purchase Apps. You’ll see a yellow alert bar indicating that you don’t have permission to add apps, but as far as I can tell, it’s a bug. Click on the “Nintex Workflow Platform Preview” icon.

Once that’s done, you’ll be presented with a summary screen. When ready, click the “Add It” button.

You’ll then be presented with a summary screen indicating that you now have a site license.

At this point, you’ll need to navigate back to the site that will be using the workflows. Now that the app is available, you won’t be turning on features for a site anymore, you’ll be adding the app to any site that you want to use the workflow preview with. In this case, we’ll be adding the preview app to a basic team site. Assuming that you have the appropriate permissions, you’ll start by adding an app by clicking on Site Contents – Add an App.

The Workflow Preview should appear in the “Apps you can add” section.

Click it, and you’ll be asked if you trust it – of course you do! Click the Trust It button and in a moment the app will be added.

What’s actually happening behind the scenes is that an app subsite is being provisioned that will contain all of the assets needed for the app to work. This is done to keep the app itself isolated from the content of the site. It may take a minute, so have patience.

Once it’s ready, you’ll see “Nintex Workflow Platform Preview” comingled in with the other site lists and apps. Clicking on it takes you into the workflow designer where you can design a site workflow.

Drag and drop actions to your heart’s content, and when ready, click Publish from the ribbon, give it a name, and save it. You can now run your Nintex site workflow in Office 365. You should note that this is a small subset of actions intended for preview only, but the Nintex Live actions are available, so you can translate items, use current exchange rates, etc.

If you want to design a list workflow, simply navigate to a library or list within the site, and click on the appropriate list or library tab. Over on the right near the standard workflow button, you’ll find a “Nintex Workflow” button.

Clicking on it will once again open the designer. If you examine the URL, you’ll notice that what it’s really doing is opening the isolated app subsite and linking back to the list where the workflow will be saved.

In this case, we’ll build a quick translation workflow that will take content from the English field, use the Bing Translation action available through Nintex Live, and then write the value to the French field.

The workflow can be set to run on item creation, and then published. So that adding a new item:

Then saving it, after a few moments results in a translated value appearing:

This is early days, and we’re looking at previews on previews, but I’m quite encouraged with this. Not only will I be able to use Nintex workflows for our own Office 365 site shortly after launch, but in my opinion, this preview validates the new application model. Yes there are currently bumps, understandable, given that this is a preview on a preview, but this is functional and responsive. The folks at Nintex have been doing their homework, and appear to be hitting the ground running into the emerging Office 365 market.

1 Comment