Room Reservation - Check if Date/Time Range is taken

[josefm2]

Hi, 

I'm trying to resolve if a certain date range + time are already reserved.

- 2 date pickers for start and end date. 2 dropdown lists of 12hr times. 1 dropdown for room selection.

- I have 5 SharePoint lists columns.

• TRAINING ROOM ( Single line of text)
• START DATE and END DATE (Date and Time)
• START TIME and END TIME (Choice).  

- I would like to display a text saying that a certain date/time is/are taken.

Thank you.

[RusselThomas · Accepted solution]

Hi @josefm2 ,

So, first question I would ask is - you are already capturing datetime in your date field - why also then go and capture time in another field?  (This seems like an over-complication?)

Second question would be - the user experience could be frustrating, as they have to 'guess' what times are available.  Naturally they'll start with the date and time they want, then as they're blocked, they'll start by changing times, then dates - but as they're are increasingly blocked by conflicts this might get frustrating.

Would it not be better to present them with a calendar view showing currently booked times, or even better, just available times for them to choose from?

To answer your question though, essentially we have to assume that any overlap from a requested timeframe on an existing booking is considered a block.  Therefore we have to test both ends of the request to see if the start falls inside a booking, or the end falls inside a booking.  Only if neither do can we be sure that there are no conflicts.

So, the process of evaluation would be;

1. Fetch all the bookings for the day of the request.
2. For each booking, test whether the request start time falls after the booking start AND before the booking end OR whether the request end time falls before the booking end AND after the booking start.
3. If any of those conditions are met, there is a conflict for that booking - if not, move onto the next booking and test again - repeat until you find a conflict or all bookings have been tested.

I don't know enough about your data construct to give you an exact solution, but in theory the logic of your behaviour formula might looks something like this;

Clear(collectConflicts);
ForAll(Filter(collectMySharePointBookings, bookingDate=requestDate),
   If(!IsEmpty(collectConflicts),
     If(
        (requestStart > bookingStart && requestStart < bookingEnd) OR
        (requestEnd < bookingEnd && requestEnd > bookingStart),
        Collect(collectConflicts, {Conflict: bookingName})
        )
     )
)

Behaviour formulas require an event to execute however, so you might want to put this on the OnChange: property of each dropdown/control.  We use a collection to contain the state of conflict within the ForAll() and an If() statement checking this collection each time to avoid unnecessary additional processing once a conflict is found.  The ForAll() will still run for each line, it just won't bother testing times once a conflict is found and should loop through the rest almost instantaneously.

As mentioned, this can make for a highly frustrating user experience, as they don't know which end of their booking is in conflict, so they have to keep choosing different times to see where they get lucky.  A better user experience might be to pop up a visual or calendar view for the day selected that shows either current bookings, or available slots for them to choose from.

[Edit]

Should probably also point out that the thing that will determine the visibility of your "Date already booked" warning would be whether collectConflicts is empty - so in the Visible: property of your warning text label, if the collection is empty, then the label is not visible - something like;

!IsEmpty(collectConflicts)

Hope this helps,

RT

[josefm2]

Hi, thank you for the suggestion. I will take a look at it. For now, I have some clarification about what are you pertaining about the ff items. Which are my sharepoint columns(already booked dates) and datepicker values?

1. bookingDate

2. requestDate

3. requestStart

4. bookingStart

5. bookingEnd

6. requestEnd 

7. bookingName

Thank you.

[RusselThomas]

Hi @josefm2 ,

Thanks for marking the original solution, but it just occurred to me that  there is a gap in my thinking - if someone has a small booking set for the middle of the day, and someone else tries to book a whole day session, the start and end of the request won't land inside the existing booking so we won't detect the conflict.  I'm not sure if this situation is likely to occur in your instance, but to correct for this we just need to test in the opposite direction as well (meaning we need to test all bookings against the request, not just the request against all bookings).  This would look something like this;

Clear(collectConflicts);
ForAll(Filter(collectMySharePointBookings, bookingDate=requestDate),
   If(!IsEmpty(collectConflicts),
     If(
        ((requestStart > bookingStart && requestStart < bookingEnd) OR
        (requestEnd < bookingEnd && requestEnd > bookingStart)) OR  //request against booking
        
        ((bookingStart > requestStart && bookingStart < requestEnd) OR
        (bookingEnd < requestEnd && bookingEnd > requestStart )), //booking against request
        
        Collect(collectConflicts, {Conflict: bookingName})
        )
     )
)

 Kind regards,

RT

[josefm2]

@RusselThomas thank you for the update.  followed your logic and came up with a modified version and it works. Validates a requested overlapping and within dates. The problem now is the time to the 'START DATE' and 'END DATE'.

Is it possible to join 2 sharepoint columns where:

1. A Date column(START/END DATE) is just a text field.

2. A Time column(START/END TIME) is a choice.

If I use just the START/END DATE column, the expression works.  It needs to find out if a certain date range is already taken or not. Now I'm trying to add the time to the START/END DATE and it prompts me with the error. If I encapsulate the START DATE and TIME with either value or text to make them the same, it returns saying the reverse. Now it says expecting TEXT value needed but if I set it as TEXT() it then it says it expects a number or value. 

ERROR:

WORKING DATE VALIDATION:

[RusselThomas]

Hi @josefm2 ,

Concat() used in this fashion will take all the rows in the source table and using the column you specify will concatenate all the values in that column with the separator you specify, resulting in a single string of concatenated values.  The separator you've chosen is 'START TIME'.Value so I'm not sure this is what you're looking for.

I believe your data constructs (Columns and types in SharePoint) are making your expressions overly complex.  You could resolve this with conversions and adding dates and times together, but I suspect your formulas might be almost unreadable by the time you're done.

I'm still unclear as to why you need to store Date in one column and Time in another? 

From what I've seen, much of this complexity can be resolved by simply adding a StartDateTime and EndDateTime column in SPO and setting them to allow Time.  Then your formula's will be relatively simple as you only have one value to reference and it will already be in DateTime format.  You can also extract DATE and TIME separately from this whenever you need to.

If you are being forced for some reason to use separate date and time columns, then I'd need to know what the format of your choices in the time column are to help you build the formula to add them with the date column.

The best I can offer is a guess that might help - eg: If they're in the format "08:30" then you could use Split() to pull out the hour and minute values, convert them to numbers using Value() and then plug them into a Time() function which will allow you to add them to the date;

'START DATE' + Time(Value(First(Split('START TIME'.Value, ":")).Result), Value(Last(Split('START TIME', ":")).Result), 0)

 This is however just a guess, and assumes 'START DATE' is actually a date format which, when converted to DateTime sets the time to 00:00:00 by default - which in turn allows us to add the Time component ourselves.

Hope this helps,

RT

[raf_and46]

Thanks so much @RusselThomas, this has helped me solve an issue I was fighting with for few days

[RossH85]

just what I have been looking for, I like this part especially "as they don't know which end of their booking is in conflict, so they have to keep choosing different times to see where they get lucky.  A better user experience might be to pop up a visual"

I have done like you suggested and have set (start date + time) together, that goes the same for my (end date +time) these are date and time columns in SharePoint. 

My question is what would be the best way to show the user what is available to them for their selected dates and time. I was thinking a gallery that filters on your choices automatically as you change the dates and times on the calendar.

How would you go about this? 

[RusselThomas]

Hi @RossH85 ,

Probably about 50 different ways to skin that particular cat, but I guess the easiest would be a DatePicker control and a small gallery that shows the events from the day selected in 30min increments - that way you can just colour each increment based on whether there is a meeting scheduled during it or not.  Of course, a much easier way might be to just set the calendar up as a resource in Exchange and let people book through Outlook, but assuming you're set on using PowerApps, then that's probably how I'd do it. 🙂

If we consider there are 24 hours in a day, then there are 48 half-hours in a day, so the increment Gallery Items: might look like this;

AddColumns(Sequence(48,0), "Time", DateAdd(DateTimeValue(DatePicker1.SelectedDate), Value * 30, TimeUnit.Minutes))

Then just compare each iteration with anything in the calendar that starts <= it and ends >= it.
Hope this helps,

RT