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
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Top Kudoed Authors
Users online (4,562)