Filtered list of times

[Chris_Jeal]

Hi team,

I would like to know if any of you would have an idea of how to accomplish the following?:

I have a table of times:

[Time]
07:00
07:15
07:30
07:45…

I also have a schedule table that is using times[Time] list to select the [Time From] and [Time To] for a booking.

[ID]
[Date]
[Time From]
[Time To]

However, I would like to be able to only show the times that have not been selected previously and also any times in between all previous [Time From] and [Time To] within the schedule for the same date.

For example:

If I have a booking from 07:00 to 07:30 then the [Time From] will not allow me to select either 07:00 or 07:15 but as the booking has ended at 07:30 I can select 07:30 for the next booking slot.

Many thanks
Chris.

[Austin]

I had a similar problem and could not find a simple solution at that time. A possible solution if you have date in your time table, would be to make a virtual column in your time slot to whether or not any rows has a time from that is less than its time AND a time to that is greater than it. If you dont have date in your slot you would possibly be able to calculate this in your form but I would guess it would be extremely slow. The other solution is a bit messy still but you could make an entry for 7 and 7:15 (include 7:30 if they cant be right on top of each other) so you would then have an entry to block off 7:15. Blocking “imaginary” times is something that I haven’t found a good solution so if anyone else has something else please post it. Hope those ideas help.

[Austin]

MIGHT be able to make a virtual list of Days that each time slot is available and only include time slots that have the selected date in that virtual column would only work within a date range that is figured out with some method but would be a more flexible solution.

[WillowMobileSys]

I have not done this but I think this is what I would do to accomplish it;

1. Create a hidden List column within each booking that computes the Blocked Times - in your example that would be 07:00 and 07:15 for that booking.

2. Then compute the available time list with an expression like this:

times[Time] - Bookings[Blocked Times]

You may need to adjust the Blocked Times list part of the expression since it will be a list of lists to something like (i’m guessing - I’ll try it when I get a minute):

times[Time] - LIST(Split(Bookings[Blocked Times], ","))

If memory serves, the expression above (if needed) will split the inner lists and the LIST() will put them all back again into a single list…but I’m not sure I’m remember correctly.

[WillowMobileSys]

I built a quick app to prove this out.

I found it easier to use a key value with the Times. I named the column Order to indicated the order of the time values.

Then I can assign the list of “From” Time values with this expression:

Times[Order] - SPLIT(Bookings[Blocked Times], ",")

The "To " dropdown list would have an expression like including the removal of the “To” selected value (would be nice to be able to remove any other values preceding the “To” time):

Times[Order] - SPLIT(Bookings[Blocked Times], ",") - LIST([From])

You can add any other adjustments needed such as the first available time can never be the “To” value so why not remove it. The last available time can never be the “From” value - remove it.

---

The tricky part, as @Austin_Lambeth is pointing out, is calculating the list of Blocked Times based on the To and From selected since it could be several values.

I wasn’t able to come up with some slick expression to retrieve the range of values. I would create a set of actions that recursively adds the key value to the Blocked Times list. Then attach that set of actions to the Form Save function. Not the most the most elegant approach but will work.

Maybe some of the expression experts can come up with the expression that will extract the range of times between “To” and “From”??

[Lynn]

Hi, what if you concatenated the start and finish times and only allowed unique?