cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
bimi82
Nintex Newbie

How to check if a Date & Time falls within a range and avoid double booking?

Jump to solution

Hello All

I am in process in creating a form for booking a car from the pool.
Now of course it is not ideal to book the same car between a date/time when it is already booked by somebody else.


The purpose is to avoid double booking!!

Lets take an example:

Now, nobody else should be able to book the same car on the same date/time and between the times.

However, the user can book the same car outside the date/time frame.


What is the best (easiest) way to perform such checkup?

Labels: (3)
Tags (1)
Reply
22 Replies
Automation Master
Automation Master

Re: How to check if a Date & Time falls within a range via nintex form?

Jump to solution

for single digit hours use "h" format specifier and for single digit minutes use "m" format specifier

Reply
bimi82
Nintex Newbie

Re: How to check if a Date & Time falls within a range and avoid double booking?

Jump to solution

I initially thought I could easily validate it against the duration of the booking but that is not correct.
The users could bypass the validation by selecting a date between the FromDate - UntilDate

Anybody got a better idea how to approach this with the validation?

0 Kudos
Reply
graham
Nintex Newbie

Re: How to check if a Date & Time falls within a range and avoid double booking?

Jump to solution

The Nintex conditional operations do not play nicely with times.

For this reason, I use the DateDiffSeconds function

For example if DateTime1 is before DateTime2, then

fn-DateDiffSeconds({WorkflowVariableSmiley Very HappyateTime1 },{WorkflowVariableSmiley Very HappyateTime2})

will return a positive number

if it is after then it returns a negative number

To test if one range overlaps another, assume

CarDTSt - datetime of the start of the existing booking

CarDTEn- datetime of the end of the existing booking

BookDTSt - datetime of the start of the new booking

BookDTEn - datetime of the end of the new booking

if BookDTSt  <= CarDTEn

          and

  BookDTEn  >= CarDTSt

Then there is an overlap

StRange = fn-DateDiffSeconds({WorkflowVariable:BookDTSt  },{WorkflowVariable:CarDTEn})

EnRange = fn-DateDiffSeconds({WorkflowVariable:BookDTEn  },{WorkflowVariable:CarDTSt})

If StRange >= 0 And EnRange >= 0 Then overlap

Does this help?

0 Kudos
Reply
bimi82
Nintex Newbie

Re: How to check if a Date & Time falls within a range and avoid double booking?

Jump to solution

I been thinking about this all day but unable to put in actions.
Does you suggestion not look at the Time at all?? Because that is important to avoid double booking.

Although trying to follow your instructions, I am getting confused....

By the way, it is not a workflow but rather a validation in the form when submitting.

This is my current scenario:

CustomList:  PoolCar

The Form

FromDate and UntilDate are the Date/Time fields.


The Validation


The validation should block from booking Ferrari between 21/11/2018 07:00 - 27/11/2018 07:00.
How would I implement your instructions?

0 Kudos
Reply
graham
Nintex Newbie

Re: How to check if a Date & Time falls within a range and avoid double booking?

Jump to solution

Ah - I didn't read that its for a form - I know nothing of those.

Maybe someone who knows about forms can jump in with how to implement the datetime validation.

Reply
Automation Master
Automation Master

Re: How to check if a Date & Time falls within a range and avoid double booking?

Jump to solution

I believe you are trying to add the validation on the Form side and not the workflow, which would be after submission was made. I believe you can use a calculated control and use the Lookup function to retrieve a single car's start and end date. You then validate the start date for the end user booking to the start date/end date of the lookup WHEN the same car is selected.

The part you need to work out is how to retrieve all of the bookings that you then validate against. Maybe Lookup can be responsive to the drop down for the car. Or you can have a javascript function run on submit to do a custom validation and do the same lookup and prevent the save.

0 Kudos
Reply
bimi82
Nintex Newbie

Re: How to check if a Date & Time falls within a range and avoid double booking?

Jump to solution

Hi Andrew

Yes, correct.
I am trying to validate the data in the form without the workflow.

I already tried with the Lookup function but as you said; it only retrieves one particular data and not all.
I also fail to be able to validate the FromDate & UntilDate with the exisiting dates to say > if selected dates fall between Date1 and Date2 then do show error otherwise continue...

I only manage to validate if the selected date are exactly as the existing date.
However, if the FromDate falls between the dates then it still goes through > which should not!


On other hand, there is the "List View" function which can display all the content based on selected data.

Here is an example where I setup the Filtering: By a control's value | Where field: PoolCar | Filtered by control: PoolCar

Now it the data is shown in the List View marked in red square.
Obviously whenever the Pool Car is changed via the dropdown, then the whole page gets refreshed :/ hmmm

Now the next questions is, how to validate the From Date & Until Date with the List View ?
Also, how do I limit the ListView to know show relevant information eg. show only booked cars if the selected dates fall within the range?


By the way, the squares in blue show the Lookup() value but as seen it only picks the top row.

0 Kudos
Reply
Automation Master
Automation Master

Re: How to check if a Date & Time falls within a range and avoid double booking?

Jump to solution

that might be a quite complex task with regular list and (nintex) forms. 

basically, what you need is a CAML query that looks for all the overlaps of already booked reservations with a datetime period of an event/reservation being booked. see example of such a query eg. here - February 2017 Mission: Real World Solution (Patching calendar) 

unfortunately, forms do not provide a way to do this easily. you'd need to write a javascript code that performs the query against the list. (maybe it might have been workable with web request control, but I hadn't played with that).

but I think there is a better/easier way how to implement this kind of logic in sharepoint.

create a resource calendar with single car(s) being resources. sharepoint will then automatically check for you that resource/car is not booked twice for the same time span.

see eg. these articles how to create such a calendar

Enable reservation of resources in a calendar - SharePoint 

https://www.dynamics101.com/how-to-create-a-resource-calendar-in-sharepoint-2010/ 

if you're on SP2013 you may need to perform an extra configuration step to make group-like features visible and configurable from site settings screens 

https://collab365.community/reservation-of-resources-in-sharepoint-2013-and-sharepoint-2013/ 

Reply
akrasheninnikov
Nintex Newbie

Re: How to check if a Date & Time falls within a range and avoid double booking?

Jump to solution

Hi. Just a heads-up. Even if at the time of submit you've successfully validated it on the form, another user can submit an overlapping time span at about the same time, so you should not get rid of the post-validation entirely. Do the double check if it's important.

0 Kudos
Reply