cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Meneghino
Level 10

Re: Check Date Availability for a Sharepoint item and then change Edit Lookup to show items availabl

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.

View solution in original post

24 REPLIES 24
Community Support Team
Community Support Team

Re: Check Date Availability for a Sharepoint item and then change Edit Lookup to show items availabl

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

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Check Date Availability for a Sharepoint item and then change Edit Lookup to show items availabl

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
Level 10

Re: Check Date Availability for a Sharepoint item and then change Edit Lookup to show items availabl

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.

View solution in original post

Re: Check Date Availability for a Sharepoint item and then change Edit Lookup to show items availabl

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")

 

2017-06-29_13-48-54.png

 

Re: Check Date Availability for a Sharepoint item and then change Edit Lookup to show items availabl

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.

Re: Check Date Availability for a Sharepoint item and then change Edit Lookup to show items availabl

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
Level 10

Re: Check Date Availability for a Sharepoint item and then change Edit Lookup to show items availabl

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.

 

Re: Check Date Availability for a Sharepoint item and then change Edit Lookup to show items availabl

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
Level 10

Re: Check Date Availability for a Sharepoint item and then change Edit Lookup to show items availabl

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

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 451 members 5,592 guests
Please welcome our newest community members: