Power Platform Community Forum Thread Details

I am building a Desk and Meeting Room Booking App in Power Apps using SharePoint as the backend.

On the Home screen, I have a gallery displaying rooms from a SharePoint list (Rooms List). When a user clicks on a gallery item, it navigates to a calendar screen, where the user can:

1)Select a start date and end date.

2)Select start time and end time.

3)Enter the number of members (desks needed)

What I want to implement:

The app should check desk availability by comparing the selected date and time with existing bookings in the Desk Booking List.

If there is a conflict (i.e., overlapping time and the required number of desks exceeds availability), a notification should display:

1)"Only X desk(s) available during this time. Please choose a different time or reduce the number of members."

If desks are available, it should navigate to the booking screen.

The issue I'm facing:

My current formula does not consistently detect overlapping bookings, especially when different time ranges are selected and also some times same time ranges i am giving it is also not working.

I'm using the Sum() function to calculate the total number of desks booked (No of Members), but it doesn't seem to work correctly in all cases.

I also want to mark already booked slots as unavailable or show a message like "Slot Already Booked" if someone selects a conflicting time.

"Once a desk is booked, the booking should remain in a pending state until it is confirmed by an admin."

In calendar screen code:

// Step 1: Set your selected values into variables
Set(selectedStartDate, dpstart.SelectedDate);
Set(selectedEndDate, dpend.SelectedDate);
Set(selectedFormTime, drpfromTime.Selected.Value);
Set(selectedToTime, drptoTime.Selected.Value);
Set(selectedMembers, Value(Dropdown1.Selected.Value));
// Step 2: Combine date and time into DateTime
Set(varStartDateTime, DateTimeValue(Text(selectedStartDate & " " & selectedFormTime)));
Set(varEndDateTime, DateTimeValue(Text(selectedEndDate & " " & selectedToTime)));
// Step 3: Validate and check availability
If(
    varEndDateTime <= varStartDateTime,
    Notify("End time must be after start time.", NotificationType.Error),
    With(
        {
            ConflictingBookings: Filter(
                'Desk Booking List',
                'Office Id' = selectedRoom.Title &&
                'Start Date' <= varEndDateTime &&
                'End Date' >= varStartDateTime
            )
        },
        With(
            {
                TotalOccupied: Sum(ConflictingBookings, 'No of Members'),
                AvailableDesks: selectedRoom.'No of Desks' - Sum(ConflictingBookings, 'No of Members')
            },
            If(
                AvailableDesks < selectedMembers,
                Notify(
                    "Only " & Text(AvailableDesks) &
                    " desk(s) available during this time. Please adjust your time or members.",
                    NotificationType.Error
                ),
                Navigate(Deskbooking_screen)
            )
        )
    )
)

Categories:

Building Power Apps

Hi,

I can't guarantee that my suggestion will fix your issue, but it worth a try. Everytime I compare dates, I make sure dates are in the exactly same format. Sometimes, the format of dates based on string type and give you strange behaviors since they can be like this 'yyyy/mm/dd' or like that 'yyyy/mm/dd HH:mm' or in another format.

Try this for every date you have in the app (even format those in your Filter formula):

Text(<yourDate>; "yyyy/mm/dd HH:mm")

Let me do it for you:

Set(selectedStartDate, Text(dpstart.SelectedDate, "yyyy/mm/dd HH:mm"));

Set(selectedEndDate, Text(dpend.SelectedDate, "yyyy/mm/dd HH:mm"));

Set(selectedFormTime, drpfromTime.Selected.Value);

Set(selectedToTime, drptoTime.Selected.Value);

Set(selectedMembers, Value(Dropdown1.Selected.Value));

// Step 2: Combine date and time into DateTime

Set(varStartDateTime, Text(DateTimeValue(Text(selectedStartDate & " " & selectedFormTime)), "yyyy/mm/dd HH:mm"));

Set(varEndDateTime, Text(DateTimeValue(Text(selectedEndDate & " " & selectedToTime)), "yyyy/mm/dd HH:mm"));

// Step 3: Validate and check availability

If(

    varEndDateTime <= varStartDateTime,

    Notify("End time must be after start time.", NotificationType.Error),

    With(

{

            ConflictingBookings: Filter(

                AddColumns('Desk Booking List', startDate, Text('Start Date', "yyyy/mm/dd HH:mm"), endDate, Text('End Date', "yyyy/mm/dd HH:mm")),

                'Office Id' = selectedRoom.Title &&

                startDate <= varEndDateTime &&

                endDate >= varStartDateTime

)

},

        With(

{

                TotalOccupied: Sum(ConflictingBookings, 'No of Members'),

                AvailableDesks: selectedRoom.'No of Desks' - Sum(ConflictingBookings, 'No of Members')

},

If(

                AvailableDesks < selectedMembers,

                Notify(

                    "Only " & Text(AvailableDesks) &

                    " desk(s) available during this time. Please adjust your time or members.",

                    NotificationType.Error

),

                Navigate(Deskbooking_screen)

)

)

)

)

I hope it helps!

______________________________________________________________

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.