Dropdown to show the available time slots for a selected date

[sasrsc1966]

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

[v-bofeng-msft · Accepted solution]

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

Best Regards,

Bof

[sasrsc1966]

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