Recently, I was asked to query a list for values based on date range entered by end users while developing a Nintex Workflow in Office 365. For example, what if the workflow needs to determine the amount of stat holidays that falls within the range a user entered. This seems simple enough to implement using the Office 365 Query List action. You drag the action onto the workflow designer and you configure it with the target list, the fields to return, the credentials to use and of course the filter. [statHolidayDate] is greater than or equal to the [StartDate] and [statHolidayDate] is less than or equal to the [EndDate]. The screen shot below shows the filter configuration:
This configuration will return zero results in Office 365. But why? To answer that question I decided to remove the filter and then log the statHolidayDate value and as well as the start and end dates the user entered to the workflow history and found the answer staring back at me. See below:
The date format that was returned from the O365 query list was "yyyy-MM-dd h:mm AM/PM" and the date format entered by the user in the
current list item date/time field was "M/dd/yyyy h:mm AM/PM". I then confirmed the date format in the Stat Holiday list (my lookup list) and found the format to be the same format as that stored in the current list item entered by which is the date/time format of Office 365 "M/dd/yyyy h:mm AM/PM" and we have little/no control over that format. So, why does the O365 query list return a date format that is different from the format used in Office 365? And, how do I solve this issue?
To answer the first question, I asked this question on the Nintex forum here.
For the second question I did the following:
- I created two new calculated (calculated based on other columns) Date time fields. The first one is StartDateCalc and the other EndDateCalc.
- In the formula for these two fields I took the value of the original “StartDate” or “EndDate” depending on the field that is being configured. For example, the formula for StartDateCalc is =TEXT(StartDate,"yyyy-MM-dd h:mm AM/PM") and the formula for EndDateCalc is =TEXT(EndDate,"yyyy-MM-dd h:mm AM/PM"). See example below:
- Note there is no need to show the calculated fields in any of the list views or on the custom Nintex input form but I included it in a view to validate the results. below is an example:
- Then I configured the filter in the O365 query list action as before but this time I use the calculated fields in the filter criteria. For example: [statHolidayDate] is greater than or equal to the [StartDateCalc] and [statHolidayDate] is less than or equal to the [EndDateCalc].
This is how I implemented a solution to this issue. Let know if this solution is helpful or if you were faced with a similar issue and how you solved it.