Vadim Tabakman

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

Nintex Workflow SDK

.



Files to Download

Download the 3rd Monday of the Month Workflow

Posted by Vadim Tabakman Sunday, February 5, 2012 10:54:00 PM Categories: Calculate a Date Call Web Service Collection Variable Execute SQL Nintex Nintex Workflow Nintex Workflow SDK
Copyright Vadim Tabakman
Rate this Content 0 Votes

Comments

Tuesday, May 12, 2015 6:13:12 AM
Happy
Gravatar

re: Nintex Workflow - 3rd Monday of the Month

 
Hi Vadim,

 

I am facing a issue while executing SQL .

Error Message:

 

The execution returned an unexpected error.

Incorrecet syntex near 'ItemProperty:'.

 

Please help

Tuesday, May 12, 2015 8:00:39 AM

re: Nintex Workflow - 3rd Monday of the Month

Hi Happy,

I would guess that some data you are putting into the SQL has a comma in it that is breaking the SQL query

cheers,

Vadim

Wednesday, May 13, 2015 1:17:03 AM
Happy
Gravatar

re: Nintex Workflow - 3rd Monday of the Month

Hi Vadim,

 

What value i will use  if i want to find 3rd monday of the month.Please help for the lines in code which are "Bold"

 

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

Wednesday, May 13, 2015 7:45:50 AM

re: Nintex Workflow - 3rd Monday of the Month

Hi Happy,

as mentioned in the post above, for the 3rd Monday, you want the following:

{ItemProperty:Day} should be Monday

{ItemProperty:Of_x0020_The_x0020_Month} should be 3.

cheers,

Vadim

Monday, May 18, 2015 1:57:46 AM
Happy
Gravatar

re: Nintex Workflow - 3rd Monday of the Month

Hi VAdim,

 

I put

{ItemProperty:Monday}

{ItemProperty:3of_x0020_The_x0020_Month}

But I am still getting the same error.Please assit.

Monday, May 18, 2015 8:07:21 AM

re: Nintex Workflow - 3rd Monday of the Month

Hi Happy,

you can't just change the value like that. The token like {ItemProperty:Day} is a SharePoint list/doclib item named Day.  When the action runs, it replaces that token with the current items Day field value.

The same for Of The Month. It's actually looking for a SharePoint field on the current item called Of the Month.

cheers,

Vadim

Monday, April 4, 2016 2:23:19 PM
Maulee Vadi
Gravatar

re: Nintex Workflow - 3rd Monday of the Month

Hello,

 

I am trying to send email on every 15th of the  month on all items int he list that has start date between current 15th of month to next 15th of the month. 

 

Any idea on how to achieve this?

 

Thanks,

Monday, April 4, 2016 2:31:58 PM

re: Nintex Workflow - 3rd Monday of the Month

Hi Maulee,

Where are you stuck?

The Calculate a Date action will let you add 1 month to a date.  Then you simply do a Query List action to query for items whose start date is greater than the current date time and less than 1 month from now.

Vadim

You must sign in to this site to post comments.
Already Registered?
Sign In
Not Yet Registered?
Register





 

Statistics

  • Entries (260)
  • Comments (1766)

Categories