I am creating a workflow where I am attempting to check the start and end dates of a meeting room entry (via for each loop), compare the dates and if the current item start and end dates overlap, end the workflow. If no overlap after looping through confirm booking, do other actions etc.
So, I created a query list item that has a filter to filter all items that match the current item's meeting room (no point checking other items that are bookings for a different meeting room!) and also doesn't return the current item ID.
I then record their ID's in a collection variable called booking Items.
Next step, I created a for each loop that loops through the ID's (booking items collection variable) and outputs result to current Index variable (number). Inside the loop I have a query list action that returns items based on the meeting room field matching the current item meeting room value and where the ID matches the current Index variable, first ID inside booking items collection. I then output the returned item's start and end dates to two start and end date variables. I then have a condition action that then checks whether the start and end dates of the current item (the item the workflow is running against) are greater lesser etc than the variables start and end dates returned from the query list action inside the loop and if they overlap, end workflow and report double booking. If not, loop through checking next ID, /item. If no match- loop will end (as no more ID's to run through in booking item collection variable), then it does other booking actions outside of loop - send booking confirmation etc.
I have placed log to history values of the returned start and end date variables and value of current Index variable+ the current item's start and end dates and all appear to be in order.
Somehow the condition action in the loop, if start and end dates etc appears not to be working as an entry that clearly does not overlap the returned item in the for each loop stops the loop and reports an overlap / double booking.
I am so close to getting this to work I'd appreciate any help/advice.
My SharePoint list the workflow is attached to is just a calendar with start and end date fields and a choice column called meeting room with either London, Paris, Milan as choices.
Also, I attempted to do it in a normal loop where it loops through the amount of items instead. I found the same condition action logic works inside this loop (check the disabled loop/actions in the attached workflow) however the query action in the loop only returns the first item...how do I get it to return the next? if I use this kind of loop/go this route instead. I have attached the workflow.
Solved! Go to Solution.
I tried to upload your workflow into my environment, but I believe you named your calendar list something different than "calendar" so my query items lost your configurations. I was able to put most of it back together following your descriptive directions. (Thank you, it is much appreciated ). But looking within the for each loop you have your Set a Condition action. The condition there seems to have a flaw I wanted to ask about to make sure I got it right. I think the 4th condition should read
Where current item Start Time is Greater Than CalendaritemEndTime.
instead it reads Start time is greater than calendaritemstarttime (which is a duplication of condition 3)
If you validate that end time is always greater than start time, then you can skip this down to two conditions i believe.
Does this help at all?
thank you very much for replying. Good spot there with the forth condition. I have now changed the logic and workflow, please see attached (my calendar is named 'Double Booking Test'. I have also used variables to trap the original item's start and end times.
I'm not sure you can cut this down to two conditions due to checking if booking overlap's or fulls within the middle of another booking, For instance you may have a new booking where the end time is greater than the current item's start time, see below:
Current booking 10:00 - 13:00
New Item 11:00 - 16:00
Just checking that the end time is greater current booking start time will not trap the fact the new item overlaps..so should check if start time is greater than current item start time AND that the new new item's start time is also greater than the current item's end time.
Same for if a new booking is before a current item's start time as below:
Current booking 10:00 -13:00
New item 9:00 -14:00
If new item start time is less than current item start time, then new item end time should be less than current item start time.
Again, just checking the end time is greater than the current item's start time will not check overlap.
Am I understanding this correctly, if not could you supply an example condition?
Thanks once again for helping.
I have been making slow progress on this and now believe there is some bug/product error where Nintex is not reading/comparing the dates correctly. I have broken down the conditions and trapped all in right to history.
I have attached the workflow. I would advise creating a calendar named ‘Double Booking Test’ so the query list actions will marry up, then you can test the logic.
The logic takes the wrong route intermittently when adding booking/times in between dates, even though the dates miss one and meet the conditions. We have tried adding commit pending changes in case it’s an issues with how the workflow processes actions in batches, but we now believe it’s an intermittent issue in how the product is reading the item dates and does not compare them correctly.
As I say, this workflow works sometimes, loops through OK even when we enter booking /items with times/dates in between other entries, but then it will sometimes errors - takes wrong logic path even though dates do not overlap.
Please seen attached screen-shot of the list, workflow route and history.
In our last test the item called ‘early in between’ failed the condition when compared to item 103 ‘new early’, even though they do not overlap.
Is there any reason for this? I would like to get this resolved as soon as possible, any help appreciated.
I'm not sure if you can get down to a reason why it is intermittent. But maybe you could break down the condition action? Such as have the original condition check for
is currentitemEndTime(looping item from query) < StartTime(New item)
if this is TRUE, then there can be no overlap. The Yes branch is the "happy" path. The No branch leads you to another condition action that checks for
is currentItemStartTime > EndTime
If this is TRUE, then all is well and no overlap. If No, then there is an overlap and continue with your operations.
I re-designed the workflow, separating out the condition with just two paths as I believe your logic/design is correct/sound, but Nintex workflow is having other ideas!
It has failed on comparing two items and it is clear the calendar item's end date (from the loop/query) is less that the new item's start date but it still took the wrong logic path/conditions was validated as not true.
I attach the screen-shots/new workflow.
I believe there is a bug/ setting somewhere that is causing the Nintex workflow engine to trip up somewhere when validating/comparing the dates.
This is becoming very annoying.
yes the time is essential as the solution is for an ad-hoc meeting booking room system.
Nintex got back to me and said this is an issue with the query list item returning start and end times of an item and then comparing them in a condition. The time part is the issue apparently. Not sure if this happening in a loop is also contributing to the issue, they weren't clear on that.
What they said was problem appears when the workflow compares same data but different times.
Different date shouldn't be a problem.
Workaround is to use fn--DateDiff to compare between two dates using the result for the comparrasion,
I'll let you know how I get on.
Not sure if this is going to work, fn-dateDiffHours function will just return the difference in hours between the two dates/times. Not sure how this function (in build string action) and its outputs will be used to see if the end time of an item already created is less than start time of new item etc.