cancel
Showing results for 
Search instead for 
Did you mean: 
Workflow Hero

Query calendar to determine if there is an event on a specific date

I'm having an issue doing something I thought would be pretty simple.  We have a calendar in our company where our HR department maintains the list of corporate holidays.  I'm wanting to query the list for a given date, and determine if there is a holiday on the calendar.  The holiday events are setup as all day events, and if the even spans multiple days, then the start/end dates cover a span of days.

 

So in my workflow I have a single date that I'm attempting to query the calendar with, and determine if there is a holiday on that date.  Howeve the issue I'm having is that I'm getting results back for the days when there isn't an event.  For example there is an event for Thanksgiving that is starts on 11/27/2014 and ends on 11/28/2014.  However when I run a test using 11/26/2014, it returns the thankgsiving event in the results.  Here is how I have my Query List setup.  

 

Query_Holidays_List.png

 

I saw some posts on the old forum about changing the date to ISO format, so I have done that before I do the Query List.  But I still get the same results.  I'm stumped as to what the issue is.  Any assistance would be greatly appreciated.

Labels: (1)
Tags (2)
0 Kudos
Reply
6 Replies
Workflow Hero

Re: Query calendar to determine if there is an event on a specific date

Hey Brendan Murphy‌ can you attach your workflow, and are you just using a standard SP Calendar no mods ?

0 Kudos
Accept as Solution Reply
Workflow Hero

Re: Query calendar to determine if there is an event on a specific date

Hi Brendan, I recall running into the same issue a while back. When using the all day event, the time is ignored (set to midnight by default). Your test runs and returns Thanksgiving because it matches your "End Time" condition. We got around this by setting the end time in our calendar event to 11:59 PM. I guess you could also try changing your End Time condition to "greater than" (not "greater than or equal to").

0 Kudos
Accept as Solution Reply
Workflow Hero

Re: Query calendar to determine if there is an event on a specific date

I thought the same thing, but in my input date I'm using different times, that don't match the times for the Thanksgving day event.  Plus if I do earlier days, those don't return a match.  I've tried changing the conditions to only "greater/less than" and that didn't work either.  When I output the start/end date for the Thanksgiving event, it shows it as:

Start Date: 11/27/2014 12:00:00 AM
End Date: 11/28/2014 11:59:00 PM

So I'm assuming this is the information the workflow is using for the comparision. 

0 Kudos
Accept as Solution Reply
Workflow Hero

Re: Query calendar to determine if there is an event on a specific date

Dan Stoll‌, I don't see a way to actually attach a file here, unless I'm missing it.  Below is a screen shot of the entire workflow.  It's pretty basic.  I have a date/time variable that displays on the start form where I'm inputing the date to check.  The first action is converting that date into an ISO date string and storing it in a single text variable (st_Date). 

Yes, it's just a standard calendar from what I can tell.  Our HR deptartment owns it, but I don't see anything that indicates it's been changed.  All the fields are the standard calendar fields.

Query_Holiday_List_Entire_WF.png

0 Kudos
Accept as Solution Reply
Not applicable

Re: Query calendar to determine if there is an event on a specific date

Hi Brendan,

I'm not sure if this is still an issue but i have a few thoughts. (Please bear in mind i am quite new to Nintex but i have extensive experience with manipulating data.)

  1. From a Data perspective, if you can remove time portion of the dates it is easier to match things up.
  2. The Calendar has an "All Day Event" Column which you say you set to true for the Holiday Events.

So if you can match item where DateOnly = DateOnly and All Day Event = "True" that should give you what you need.

Please let me know if you want me to elaborate further

0 Kudos
Accept as Solution Reply
Workflow Hero

Re: Query calendar to determine if there is an event on a specific date

Were you able to get your WF to work?  I'm searching the forum for help.  I see a similarities with the issues I'm having.  I started to wonder if there are constraints on empty (blank or null) list items on date type columns, when looping through a list. 

0 Kudos
Accept as Solution Reply