cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
josefm2
Helper I
Helper I

Room Reservation - Check if Date/Time Range is taken

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.

 

josefm2_2-1633119547905.png

josefm2_1-1633119404039.png

 

 

Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
RusselThomas
Community Champion
Community Champion

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

View solution in original post

8 REPLIES 8
RusselThomas
Community Champion
Community Champion

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

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
Community Champion
Community Champion

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

@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:

josefm2_1-1633436866318.png

 

WORKING DATE VALIDATION:

josefm2_0-1633436745427.png

 

RusselThomas
Community Champion
Community Champion

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

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

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
Community Champion
Community Champion

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

Helpful resources

Announcements

Celebrating the May Super User of the Month: Laurens Martens

  @LaurensM  is an exceptional contributor to the Power Platform Community. Super Users like Laurens inspire others through their example, encouragement, and active participation. We are excited to celebrated Laurens as our Super User of the Month for May 2024.   Consistent Engagement:  He consistently engages with the community by answering forum questions, sharing insights, and providing solutions. Laurens dedication helps other users find answers and overcome challenges.   Community Expertise: As a Super User, Laurens plays a crucial role in maintaining a knowledge sharing environment. Always ensuring a positive experience for everyone.   Leadership: He shares valuable insights on community growth, engagement, and future trends. Their contributions help shape the Power Platform Community.   Congratulations, Laurens Martens, for your outstanding work! Keep inspiring others and making a difference in the community!   Keep up the fantastic work!        

Check out the Copilot Studio Cookbook today!

We are excited to announce our new Copilot Cookbook Gallery in the Copilot Studio Community. We can't wait for you to share your expertise and your experience!    Join us for an amazing opportunity where you'll be one of the first to contribute to the Copilot Cookbook—your ultimate guide to mastering Microsoft Copilot. Whether you're seeking inspiration or grappling with a challenge while crafting apps, you probably already know that Copilot Cookbook is your reliable assistant, offering a wealth of tips and tricks at your fingertips--and we want you to add your expertise. What can you "cook" up?   Click this link to get started: https://aka.ms/CS_Copilot_Cookbook_Gallery   Don't miss out on this exclusive opportunity to be one of the first in the Community to share your app creation journey with Copilot. We'll be announcing a Cookbook Challenge very soon and want to make sure you one of the first "cooks" in the kitchen.   Don't miss your moment--start submitting in the Copilot Cookbook Gallery today!     Thank you,  Engagement Team

Announcing Power Apps Copilot Cookbook Gallery

We are excited to share that the all-new Copilot Cookbook Gallery for Power Apps is now available in the Power Apps Community, full of tips and tricks on how to best use Microsoft Copilot as you develop and create in Power Apps. The new Copilot Cookbook is your go-to resource when you need inspiration--or when you're stuck--and aren't sure how to best partner with Copilot while creating apps.   Whether you're looking for the best prompts or just want to know about responsible AI use, visit Copilot Cookbook for regular updates you can rely on--while also serving up some of your greatest tips and tricks for the Community. Check Out the new Copilot Cookbook for Power Apps today: Copilot Cookbook - Power Platform Community.  We can't wait to see what you "cook" up!      

Tuesday Tip | How to Report Spam in Our Community

It's time for another TUESDAY TIPS, your weekly connection with the most insightful tips and tricks that empower both newcomers and veterans in the Power Platform Community! Every Tuesday, we bring you a curated selection of the finest advice, distilled from the resources and tools in the Community. Whether you’re a seasoned member or just getting started, Tuesday Tips are the perfect compass guiding you across the dynamic landscape of the Power Platform Community.   As our community family expands each week, we revisit our essential tools, tips, and tricks to ensure you’re well-versed in the community’s pulse. Keep an eye on the News & Announcements for your weekly Tuesday Tips—you never know what you may learn!   Today's Tip: How to Report Spam in Our Community We strive to maintain a professional and helpful community, and part of that effort involves keeping our platform free of spam. If you encounter a post that you believe is spam, please follow these steps to report it: Locate the Post: Find the post in question within the community.Kebab Menu: Click on the "Kebab" menu | 3 Dots, on the top right of the post.Report Inappropriate Content: Select "Report Inappropriate Content" from the menu.Submit Report: Fill out any necessary details on the form and submit your report.   Our community team will review the report and take appropriate action to ensure our community remains a valuable resource for everyone.   Thank you for helping us keep the community clean and useful!

Community Roundup: A Look Back at Our Last 10 Tuesday Tips

As we continue to grow and learn together, it's important to reflect on the valuable insights we've shared. For today's #TuesdayTip, we're excited to take a moment to look back at the last 10 tips we've shared in case you missed any or want to revisit them. Thanks for your incredible support for this series--we're so glad it was able to help so many of you navigate your community experience!   Getting Started in the Community An overview of everything you need to know about navigating the community on one page!  Community Links: ○ Power Apps ○ Power Automate  ○ Power Pages  ○ Copilot Studio    Community Ranks and YOU Have you ever wondered how your fellow community members ascend the ranks within our community? We explain everything about ranks and how to achieve points so you can climb up in the rankings! Community Links: ○ Power Apps ○ Power Automate  ○ Power Pages  ○ Copilot Studio    Powering Up Your Community Profile Your Community User Profile is how the Community knows you--so it's essential that it works the way you need it to! From changing your username to updating contact information, this Knowledge Base Article is your best resource for powering up your profile. Community Links: ○ Power Apps ○ Power Automate  ○ Power Pages  ○ Copilot Studio    Community Blogs--A Great Place to Start There's so much you'll discover in the Community Blogs, and we hope you'll check them out today!  Community Links: ○ Power Apps ○ Power Automate  ○ Power Pages  ○ Copilot Studio    Unlocking Community Achievements and Earning Badges Across the Communities, you'll see badges on users profile that recognize and reward their engagement and contributions. Check out some details on Community badges--and find out more in the detailed link at the end of the article! Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio    Blogging in the Community Interested in blogging? Everything you need to know on writing blogs in our four communities! Get started blogging across the Power Platform communities today! Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio   Subscriptions & Notifications We don't want you to miss a thing in the community! Read all about how to subscribe to sections of our forums and how to setup your notifications! Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio   Getting Started with Private Messages & Macros Do you want to enhance your communication in the Community and streamline your interactions? One of the best ways to do this is to ensure you are using Private Messaging--and the ever-handy macros that are available to you as a Community member! Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio   Community User Groups Learn everything about being part of, starting, or leading a User Group in the Power Platform Community. Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio   Update Your Community Profile Today! Keep your community profile up to date which is essential for staying connected and engaged with the community. Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio   Thank you for being an integral part of our journey.   Here's to many more Tuesday Tips as we pave the way for a brighter, more connected future! As always, watch the News & Announcements for the next set of tips, coming soon!

Hear what's next for the Power Up Program

Hear from Principal Program Manager, Dimpi Gandhi, to discover the latest enhancements to the Microsoft #PowerUpProgram, including a new accelerated video-based curriculum crafted with the expertise of Microsoft MVPs, Rory Neary and Charlie Phipps-Bennett. If you’d like to hear what’s coming next, click the link below to sign up today! https://aka.ms/PowerUp  

Top Solution Authors
Top Kudoed Authors
Users online (3,111)