staff accommodation booking app

[garymiller]

Good evening all

so i am trying to make a staff accommodation booking system and i am a bit stuck,

i have 2 lists,

the first list is just a list of all the rooms 

the second list  is where all the bookings are placed 

when i am trying to do my app i have a date picker and a gallary,  i am trying to select a date and see what rooms are available 

this is the filter i have in place on the gallary items 

Filter(
rooms,
IsBlank(
LookUp(
Bookings,
Room.Value = Title &&
(
(DatePicker2.SelectedDate >= 'Date From' && DatePicker2.SelectedDate <= 'Date To') ||
(DatePicker2.SelectedDate <= 'Date From' && DatePicker2.SelectedDate >= 'Date To') ||
(DatePicker2.SelectedDate >= 'Date From' && DatePicker2.SelectedDate >= 'Date To')
)
)
)
)

i am getting no errors but nothing is showing,

when i replace the code with this 

Filter(
rooms,
true // This will display all rooms in the gallery
)

i can see all the rooms 

can anyone help?

[LaurensM]

Hi @garymiller,

I think explicitly defining the rooms datasource loop, should do the trick. With nested loop functions (AddColumns, Filter, Lookup...) PowerApps sometimes gets confused which column is referencing what loop.

Filter(
    //Explicitly define loop record
    rooms As Main,
    IsBlank(
        LookUp(
            Bookings,
            Room.Value = Main.Title &&
            (
            (DatePicker2.SelectedDate >= 'Date From' && DatePicker2.SelectedDate <= 'Date To') ||
            (DatePicker2.SelectedDate <= 'Date From' && DatePicker2.SelectedDate >= 'Date To') ||
            (DatePicker2.SelectedDate >= 'Date From' && DatePicker2.SelectedDate >= 'Date To')
            )
        )
    )
)

In the Filter above you are using a LookUp for each room. It might be beneficial to simply fetch all bookings inbetween the dates, and check whether the room title is present in that list:

With(
    {
        //Fetch all bookings between those dates and save the distinct room titles
        wBookings: Distinct(
            Filter(
                Bookings,
                (
                (DatePicker2.SelectedDate >= 'Date From' && DatePicker2.SelectedDate <= 'Date To') ||
                (DatePicker2.SelectedDate <= 'Date From' && DatePicker2.SelectedDate >= 'Date To') ||
                (DatePicker2.SelectedDate >= 'Date From' && DatePicker2.SelectedDate >= 'Date To')
                )
            ),
            Room.Value
        )
    },
    Filter(
        rooms,
        //Only return available rooms
        !(Title in wBookings)
    )
)

Please keep in mind that you are currently using the same DatePicker for both the Start and End date.

If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.

Thanks!

[garymiller]

@LaurensM 

thankyou very much i used your first code and it showed all my rooms,

but the ones that are taken are not showing available after the date from but are showing if i select a date before it was book

[LaurensM]

@garymiller you are currently only using 1 DatePicker which causes that behaviour. I would expect the user to select a date range which would make your filter look as follows:

(
(DatePickerStartDate.SelectedDate >= 'Date From' && DatePickerEndDate.SelectedDate <= 'Date To') ||
(DatePickerStartDate.SelectedDate <= 'Date From' && DatePickerEndDate.SelectedDate >= 'Date To') ||
(DatePickerStartDate.SelectedDate >= 'Date From' && DatePickerEndDate.SelectedDate >= 'Date To')
)

When using 1 DatePicker, the last line in your original code will always return true - resulting in the room not being shown:

DatePicker2.SelectedDate >= 'Date From' && DatePicker2.SelectedDate >= 'Date To'

If you only want to use 1 selected date, the following condition should suffice:

DatePicker2.SelectedDate >= 'Date From' && DatePicker2.SelectedDate <= 'Date To'

I hope this helps!

[garymiller]

@LaurensM 

this isn't for the users to book atm (this screen) it is just to see what is available

[LaurensM]

@garymiller ok I understand. It is a page to check which rooms are available on a certain date?

In that case I think my last suggestion will help.

Filter(
    //Explicitly define loop record
    rooms As Main,
    IsBlank(
        LookUp(
            Bookings,
            Room.Value = Main.Title,
            DatePicker2.SelectedDate >= 'Date From' && DatePicker2.SelectedDate >= 'Date To'
        )
    )
)

Or

With(
    {
        //Fetch all bookings between those dates and save the distinct room titles
        wBookings: Distinct(
            Filter(
                Bookings,
                (DatePicker2.SelectedDate >= 'Date From' && DatePicker2.SelectedDate >= 'Date To')
            ),
            Room.Value
        )
    },
    Filter(
        rooms,
        //Only return available rooms
        !(Title in wBookings)
    )
)

I hope this helps!

[garymiller]

@LaurensM  firstly thankyou very much for you help,

but the first code doesnt do anything with current booking,

the 2nd code seems to be doing the opposite , when there booked there showing with all the other rooms when there not booked they are not showing with all the others

[LaurensM]

@garymiller my apologies, I used the wrong date filter - not sure what happened there. 😅

Please swap it with the following:

DatePicker2.SelectedDate >= 'Date From' && DatePicker2.SelectedDate <= 'Date To'

[garymiller]

@LaurensM  i just tried it and i think it works but i will relook at tomorrow since i opened a can or 2 getting stressed with this lol again i really apricate your help 

[LaurensM]

@garymiller glad to help!

Let me know should something be unclear tomorrow. 😊