Check Date Availability for a Sharepoint item and then change Edit Lookup to show items available

[Alerts_OM_USA]

I have 2 lists in Sharepoint.

One list with all the Apartments.

A second (main) list with all the guests that are registered (including their arrival and departure date, and what apartment they are staying in.) The guest can choose an apartment to stay in, and I want the dropdown list in the PowerApps app to show only apartments that are not already booked during that time (apartments that don't have arrival or departure dates that overlap with the new applicants arrival and departure dates).
Does anyone have any guidance on how I could do this?

Thanks.

[v-monli-msft]

Hi @Alerts_OM_USA,

I have searched for a few thread that is talking about the simialr issue, please check if these help:

https://stackoverflow.com/questions/43725028/filter-a-dropdown-based-on-the-content-of-another-dropd...

https://powerusers.microsoft.com/t5/PowerApps-Forum/Filter-gallery-based-on-choice-field-in-list-not...

Regards,

Mona

[Alerts_OM_USA]

I know how to filter based on another column like making the apartments listed filter based on how many bedrooms you choose. But I am finding it difficult to think of a way to filter which apartments are available and not already booked during the selected dates.

[Meneghino · Accepted solution]

Hi @Alerts_OM_USA

Let's say the list of apartments is called Apartments and has at least an ID column.

Let's say your main list is called Registrations and has at least the following columns:

Apartment_ID

Booking_start_date

Booking_end_date

Let's say that you want to know which apartments are available for the whole period between Selected_arrival_date and Selected_departure_date.

You first create a list of apartments that are not available in that period by doing this:

GroupBy(
Filter(Registrations,
(Booking_start_date<=Selected_arrival_date&&Booking_end_date>=Selected_departure_date)||
(Selected_arrival_date>=Booking_start_date&&Selected_arrival_date<=Booking_end_date)||
(Selected_departure_date>=Booking_start_date&&Selected_departure_date<=Booking_end_date)
), "Apartment_ID", "AllRows")

Let's call the above expression BookedApartments

Then to get a list of free apartments all you need is this:

Filter(Apartments, Not(ID in BookedApartments.Apartment_ID))

PS This assumes that filtering by date columns is currently working and is delegated.  There have been bugs in the past and I have written many posts on this. It also assumes that Not and in are delegated in SharePoint lists, which could also not be the case.  Anyway please try and let me know how you get on.

[Alerts_OM_USA]

So here is what I have with the help you've given. I have the first block of code on a Listbox (Items) and then the second on a seperate Listbox (Items). But I'm getting an error where it says the name of the group "BookedApartments" is invalid.

GroupBy(
Filter('Guest Registration Form',
(Arrival_x0020_Date<=DataCardValue9.SelectedDate&&Departure_x0020_Date>=DataCardValue11.SelectedDate)||
(DataCardValue9.SelectedDate>=Arrival_x0020_Date&&DataCardValue9.SelectedDate<=Departure_x0020_Date)||
(DataCardValue11.SelectedDate>=Arrival_x0020_Date&&DataCardValue11.SelectedDate<=Departure_x0020_Date)
), "Apartment_ID", "BookedApartments")

[Alerts_OM_USA]

Ah I see what my problem was. I needed to use Update() to make BookedApartments and not make the group name BookedApartments.

Anyway, thank you so much for your help! I was unaware of the usefulness of GroupBy until now, and you've saved me countless hours of trial and error.

[Alerts_OM_USA]

I am wondering though, how do I make the custom field I just made for which apartment to book, actually change the Apartment record in the Sharepoint List. Usually I can just check the checkbox of the Sharepoint item and then it auto links to the sharepoint list so when I fill out the form it auto populates the data. But with a Custom field I am not sure how to make it change the Sharepoint List.

[Meneghino]

Hi @Alerts_OM_USA

I assume that you are using a form in PowerApps.  I know virtually nothing about PA forms.

As I have posted in various other threads, I believe that as soon as you want to make any significant customisation then you are better off abandoning forms and making your own from the indivdual controls.

There is a learning curve there at first, but a rewarding one.  I now always just make the forms from scratch and then post the data back to the data source via the Patch function.

[Alerts_OM_USA]

I believe you can use PowerApps forms in combination with "Custom Fields" and then the custom fields have flexibility and customization options. I have this line of code of my Custom field for the apartment:

Patch('Guest Registration Form',First(Filter('Guest Registration Form',Value(Label16.Text)=ID)),{Apartment: Dropdown2.Selected.Value})

Label16 has the ID of the Item.

Am I patching incorrectly? 

If this looks correct, then possibly it could be that it can't patch and create a new form in the same screen.

[Meneghino]

hi again @Alerts_OM_USA

assuming that 'Guest registration form' is your data source (ie. your database table/sharepoint list), then your line of code looks correct in terms of syntax.

PS I just looked over your posts and it would seem that the data source is Registrations, as it should be.  So I am not sure what 'Guest registration form' is as a data source