cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sasrsc1966
Resolver I
Resolver I

Dropdown to show the available time slots for a selected date

I have a date picker, when they onChange the date I want to hit a SP List and get back the rows for that given date. I have that working. But I want to do is now loop through and ask the question - is 10am for 30 mins free, is 11am for 30 mins free, etc... (there's a separate array for the possible start time being on the hour 10am, 11am, 12pm, 1pm, 2pm and the person selects a duration of either 30,45 or 60 mins.  The SP list stores the start datetime and a duration.
I want to keep this flexible as possible in case they decide to add 9am or 3pm as a possible start time later.
I have therefore 2 collections...
a) what's already reserved for that date - if anything
b) fixed appointment times

The response should be that the dropdown only contains the b) array only if there's no conflicts.

This is how I get collection a...there's a few more pieces in there to clean up bad data but a is getting populate.

ClearCollect(getBookings,SortByColumns(Filter(
    ForAll(
        WebinarSchedule As _s,
        Patch(
            _s,
            {
                tempEnd: DateAdd(
                    _s.'Air Date Time',
                    If(
                        IsBlank(_s.Duration) || _s.Duration = "Other",
                        60 + tempBuffer,
                        Value(_s.Duration) + tempBuffer
                    ),
                    Minutes
                ),
                tempStart: DateAdd(
                    _s.'Air Date Time',
                    - tempBuffer,
                    Minutes
                )
            }
        )
    ),
    DateValue(tempStart) = DateValue(RequestStartTime)),"tempStart"))






 

1 ACCEPTED SOLUTION

Accepted Solutions
v-bofeng-msft
Community Support
Community Support

Hi @sasrsc1966 :

Do you want to filter out the available time periods?I've made a test for your reference:
1\I assumer there is a data table WebinarSchedule

ClearCollect(
  WebinarSchedule,
  {'Air Date Time':DateTimeValue("2022/1/13 13:00:00"),Duration:45},
  {'Air Date Time':DateTimeValue("2022/1/13 14:00:00"),Duration:30}
)

2\A data picker DatePicker1

3\A drop down control Dropdown2 to choices duration:

items:

[30,45,60]

 4\You could use this formula to get the available start datetime:

Filter(
    ForAll(
        Sequence(5,10,1),{Timestemp:DateAdd(DatePicker1.SelectedDate,Value,Hours),Hour:Text(DateAdd(DatePicker1.SelectedDate,Value,Hours),"HH AM/PM")}),
        IsEmpty(
        Filter(
            WebinarSchedule,
            (Timestemp >= 'Air Date Time' && Timestemp< DateAdd('Air Date Time',Duration,Minutes))||
            (DateAdd(Timestemp,Dropdown2.SelectedText.Value,Minutes) > 'Air Date Time' && DateAdd(Timestemp,Dropdown2.SelectedText.Value,Minutes)<= DateAdd('Air Date Time',Duration,Minutes))||
            (Timestemp <= 'Air Date Time' && DateAdd(Timestemp,Dropdown2.SelectedText.Value,Minutes)>= DateAdd('Air Date Time',Duration,Minutes))
        )
    )
)

vbofengmsft_0-1642053286386.png

Best Regards,

Bof

 

View solution in original post

2 REPLIES 2
v-bofeng-msft
Community Support
Community Support

Hi @sasrsc1966 :

Do you want to filter out the available time periods?I've made a test for your reference:
1\I assumer there is a data table WebinarSchedule

ClearCollect(
  WebinarSchedule,
  {'Air Date Time':DateTimeValue("2022/1/13 13:00:00"),Duration:45},
  {'Air Date Time':DateTimeValue("2022/1/13 14:00:00"),Duration:30}
)

2\A data picker DatePicker1

3\A drop down control Dropdown2 to choices duration:

items:

[30,45,60]

 4\You could use this formula to get the available start datetime:

Filter(
    ForAll(
        Sequence(5,10,1),{Timestemp:DateAdd(DatePicker1.SelectedDate,Value,Hours),Hour:Text(DateAdd(DatePicker1.SelectedDate,Value,Hours),"HH AM/PM")}),
        IsEmpty(
        Filter(
            WebinarSchedule,
            (Timestemp >= 'Air Date Time' && Timestemp< DateAdd('Air Date Time',Duration,Minutes))||
            (DateAdd(Timestemp,Dropdown2.SelectedText.Value,Minutes) > 'Air Date Time' && DateAdd(Timestemp,Dropdown2.SelectedText.Value,Minutes)<= DateAdd('Air Date Time',Duration,Minutes))||
            (Timestemp <= 'Air Date Time' && DateAdd(Timestemp,Dropdown2.SelectedText.Value,Minutes)>= DateAdd('Air Date Time',Duration,Minutes))
        )
    )
)

vbofengmsft_0-1642053286386.png

Best Regards,

Bof

 

sasrsc1966
Resolver I
Resolver I

Thanks @v-bofeng-msft - what a sublime solution. That's exactly what I needed.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Carousel_PP_768x460_Wave2 (1).png

2022 Release Wave 2 Plan

Power Platform release plan for the 2022 release wave 2 describes all new features releasing from October 2022 through March 2023.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Top Solution Authors
Users online (4,786)