Answered

Hide drop down items depending on SP list and selection

(0) Share

Report

Posted on by pm4698

Hello there,

I am building a powerapp for bookings and I save data in a SP list. Among others, SP list has columns: Date and Timeslot.

Inside my powerapp, I have one datepicker field and 1 drop down field with custom value items like this:

["09:00 - 10:00", 10:30 - 10:45",....""]

When someone is booking, the value of datepicker field and dropdown are saved in the SP list to the related columns.

What I am trying to accomplish is when someone selects a date from the datePicker (onChange event) for the Timeslot dropdown to be updated like this:

A Query/filter would get the selected date, search the SP list where Date='date from datepicker' and for each one of those records to be removed from the timeslot dropdown in my powerapp. This way, I am intending not to have double bookings for the specific date/timeslot.

Any ideas of how to do this?

Thank you in advance

Categories:

Building Power Apps

All responses (3)

Answers (1)

Sort by

Verified answer

C-Papa 1,836 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi, there are a few way to do this, i think the best would be to create a collection of the times booked on a selected day e.g.

ClearCollect(col_booked_times,Filter(MyList, Date = DatePicker1.SelectedDate))

Then create collection of the booking slots that you would like to be available to book, and lookup the above collection to see if the time is booked on the selected date

ClearCollect(col_Available_times,{SlotTime:"9am",booked:LookUp(col_booked_times,TimeSlot = "9am").TimeSlot},{SlotTime:"10am",booked:LookUp(col_booked_times,TimeSlot = "10am").TimeSlot},{SlotTime:"11am",booked:LookUp(col_booked_times,TimeSlot = "11am").TimeSlot},{SlotTime:"12pm",booked:LookUp(col_booked_times,TimeSlot = "12pm").TimeSlot},{SlotTime:"1pm",booked:LookUp(col_booked_times,TimeSlot = "1pm").TimeSlot})

In the above i have assumed you have a SharePoint column called TimeSlot where the booked times are stored. if you look at the format you can add as many times as you want or change the format if required, you can even put a range in.

then on the dropdown items you would have

Distinct(Filter(col_Available_times,IsBlank(booked)),SlotTime)
pm4698 on at

Like
a
(0)

Report
Copy link

Link copied!

@C-Papa you are the best. That worked like a charm!
jam23 2 on at

Like
a
(0)

Report
Copy link

Link copied!

I am looking to develop something similar - Vehicle Reservation. But instead of the dates, I have dropdown list of vehicles for the users to reserve. If Vehicle A has been reserved for that day, it should be greyed out when the next user comes in to reserve a vehicle. But Vehicle A will be available to reserve for the next day.
Appreciate any help on this!