Nintex Workflow – 3rd Monday of the Month
A situation recently arose, where someone wanted to schedule a Nintex Workflow on the 3rd Monday of every month. Out-of-the-box, there really isn’t a way to do this, because the user interface for configuring the schedule of a workflow only allows you to schedule it every ‘n’ number of hours, days or months.
I guess you could configure it to run every 28 days (4 weeks), but because of the different number of days in each month, this would get out of whack pretty quickly.
Solution
So my idea, was to first find a way to get the next 3rd monday of the month. If we can get that, then we can create a schedule for it. The Nintex Workflow web service exposes two web methods we can use : AddWorkflowSchedule and AddWorkflowScheduleOnListItem
I will focus my test on a SharePoint List, so using the AddWorkflowScheduleOnListItem will be the method of choice here.
The Next 3rd Monday of the Month
I hunted around online for an algorithm to calculate this, so that I could build up a workflow snippet. Well, I found a better way, but unfortunately I don’t know where I found it. What it was, was a SQL query. Perfect. That means that instead of having a bunch of actions strung together to give me what I want, I can just have on. The Execute SQL action.
I needed to make a few modifications to the query, so that it is a little more generic. Why have a query that specifically gets the 3rd Monday of the Month. What if you want the 2nd Wednesday, or the 20th Saturday?
CREATE TABLE #Calendar(Calendar_Date varchar(32),Month_Num smallint)
DECLARE @dtDate datetime
SET @dtDate = '{WorkflowVariable:textFormattedCurrentDate}'
WHILE (@dtDate <= '{WorkflowVariable:textFormattedMonthFromNowDate}')
BEGIN
INSERT INTO #Calendar
(Calendar_Date,Month_Num)
SELECT @dtdate,datepart(month,@dtdate)
WHERE datename(dw,@dtdate) = '{ItemProperty:Day}'
SET @dtDate = DATEADD(dd,1,@dtDate)
END
select top 1 Calendar_Date from
( SELECT row_number() over ( partition by Month_Num order by Month_Num desc) as rid, * FROM #Calendar)t
where t.rid = {ItemProperty:Of_x0020_The_x0020_Month}
drop table #Calendar
The {WorkflowVariable:textFormattedCurrentDate} represents the current date in a MM/dd/yyyy format, so we know where to start figuring out the next 3rd Monday of the month.
The {WorkflowVariable:textFormattedMonthFromNowDate} represent the last date we want to look at. Which means if we want the 3rd Monday of the Month, that falls between the previous and this date.
The {ItemProperty:Day} represents which day of the week we are talking about. This can be any one of the following values : Monday, Tuesday, Wednesday, Thursday, Friday, Saturday or Sunday.
The {ItemProperty:Of_x0020_The_x0020_Month} represents the number of the month we are interested in. eg. 3 if your want the 3rd, or 10 if your want the 10th Monday of the month.
The SharePoint List that I created, contained a number field (day of the month) and a single line of text field (day of the week).
The Workflow
The workflow itself it fairly straight forward.
First we format the current date into a format that is compatible with SQL (M/dd/yyyy). Then we add 1 month to it, because we are currently only interested in the next 3rd Monday of the Month. Then we run the SQL query above in the Execute SQL action, inserting the appropriate parts from the List fields, and the variables we have calculated.
The results of the Execute SQL action, I am storing in a collection variable, but given that we are expecting only the one result, we could just store it in a DateTime variable.
The AddWorkflowScheduleOnListItem requires that the dates passed into it, must be in an ISO8601 format. You can do this, but with the Calculate a Date action, because one of the Store results in options, is to store the date in that format.
You can then use this text variable in the Call Web Service action, and call the Nintex Workflow web service and the AddWorkflowScheduleOnListItem web method.
When I run this workflow, it completes successfully. *good sign* Next, click on the item to see the dropdown menu, and select Schedule Workflows. You’ll then see the new schedule that was just added to this item.
Conclusion
This might not be a 100% complete solution to something like this, but it will atleast get you started with getting where you’d want to go.
Each time this workflow would run on a schedule, it will find the next 3rd Monday of the month, and add a new single schedule to that item.
It’s interesting what you can do with all the tools you have at your disposal.
One of the important things to take away from this, is that we are using the Execute SQL action. We still need a valid database to run this SQL query, but the query we are running, does not actually hit any tables in the database. It’s a standalone query. Although I haven’t tried this, you could potentially run SQL Date Functions in the Execute SQL action to perform functions that may not be available to you out of the box with Nintex Workflow.
To find out more about what web methods are available to you in the Nintex Workflow Web Service, take a look at the
.
Files to Download
Download the 3rd Monday of the Month Workflow
