Thursday, January 25, 2018

Date Range Comparisons within Nintex Workflow

I recently was tasked to amend a document submission/review workflow, where documents could only be submitted (via changing a status to 'Ready for Review') during a particular window. Typically the window was only for a certain day each month, between 7am and 7pm.

I first started by creating a separate list, which would contain a single list item that had a 'Date From' and 'Date To' field. I then added a query list action with a CAML query of:

<Query>
  <Lists>
    <List ID="{D5DA2C78-F280-4A30-9D9D-FD43997B5B08}"/>
  </Lists>
  <ViewFields>
    <FieldRef Name="ID">
  </ViewFields>
  <Where>
    <DateRangesOverlap>
      <FieldRef Name="DateFrom" />
      <FieldRef Name="DateTo" />
      <Value IncludeTimeValue="TRUE" Type="DateTime">
        <Today/>
      </Value>
    </DateRangesOverlap>
  <Where>
<Query>

Note: I substituted <Now/> for <Today/> and also a Workflow variable for the current date time (derived from a Calculate date action) to see if it made any difference.

I found that if the submission date was on a different day to today, the logic would work fine and not return any results (which is what I was checking for after the query list action). However, if the current datetime was outside of the window (which was today), it would return a result, even if the time was outside of the window.

I found the article https://community.nintex.com/thread/3213, where Paul Svetleachni said:
"The time is used to calculate if one period of time turns into next day or not. Thus only calculated based on actual day and hours are used if it is next day or not. So, filtering by specific hour/min/second is not possible, it is only used to determine if next day is added or subtracted based on calculation of date." So I tried various attempts using a set a condition action (based on https://community.nintex.com/thread/10160?commentID=32146#comment-32146), which didn't work for me. I amended this logic to the following, but still no luck:

Condition: If any value equals value
Where: "Workflow Data" - "Current Datetime"
is greater than "Workflow Data" - "DateTo"

OR

Condition: If any value equals value
Where: "Workflow Data" - "Current Datetime"
is less than "Workflow Data" - "DateFrom"

In the end I decided to convert the dates into numbers, then do my comparison that way, which WORKED!

Steps to reproduce:
1. Build String to populate Workflow variable "CurrentTimeAsNumberString". The formula for this was:
fn-FormateDate({Common:CurrentDate},"yyyyMMdd")fn-FormatDate({Common:CurrentTime},"HHmm")

This produced a string that looked like "201801250915"

2. Convert the CurrentTimeAsNumberString workflow variable to CurrentTimeAsNumber, using the Convert value action (where "Input" is CurrentTImeAsNumberString and "Store result in" is CurrentTimeAsNumber).

3. Repeat the Build String action, this time to populate the "DateFromAsNumberString" using the formula:
fn-FormateDate({WorkflowVariable:DateFrom},"yyyyMMdd")fn-FormatDate({WorkflowVariable:DateFrom},"HHmm")

4. Convert DateFromAsNumberString to DateFromAsNumber using the convert value action.

5. Repeat the Build String action, this time to populate the "DateToAsNumberString" using the formula:
fn-FormateDate({WorkflowVariable:DateTo},"yyyyMMdd")fn-FormatDate({WorkflowVariable:DateTo},"HHmm")

6. Convert DateToAsNumberString to DateToAsNumber using the convert value action.

7. Update the set condition action shown above to use the numbers instead of dates in the comparison:

Condition: If any value equals value
Where: "Workflow Data" - "CurrentTimeAsNumber"
is greater than "Workflow Data" - "DateToAsNumber"

OR

Condition: If any value equals value
Where: "Workflow Data" - "CurrentTimeAsNumber"
is less than "Workflow Data" - "DateFromAsNumber"


I hope this helps someone, as I was pulling my hair out trying to figure out why such a simple thing such as a data range comparison doesn't work naturally within SharePoint (and therefore Nintex Workflow).