Consolidate List of Time Slots

Need help Database
1

Good day fellow Bubblers

I am having a hard time wrapping my head around something and I think I’ve gone so far in one direction that I have blinded myself with what could be a much easier solution.

I’m trying to make a “self scheduling” system where I provide a list of time slots and the max capacity of each time slot.

Time slots are in 30 minute increments from 7AM to 10PM so there are 30 slots in a day

Each slot had it’s own MaxCapacity value which is the maximum number of allowed users in that slot. (Eventually when a user selects a group of slots the MaxCapacity value will -1 or I’ll generate another field and have it +1 and run a check if that field is = MaxCapacity)

I own a delivery platform and this is to allow my drivers to schedule themselves based upon availability.

My data type named TimeSlots has the following fields and field types

StartTime - TEXT
EndTime - TEXT
StartTimeDATE - DATE
EndTimeDATE - DATE
MaxCapacity - NUMBER
Day - TEXT

I have a GroupA across the top of the page that houses 7 more groups. GroupDay1, GroupDay2… and so on through GroupDay7

Each GroupDayX has a text field that displays the dates for the current and next 6 days. So GroupDay1 text element displays Current Date (Formatted) and GroupDay2 text element displays Current Date +1 (Formatted) all the way through to GroupDay7 text element displaying Current Date +6 (Formatted).

Below GroupA is another Group called ScheduleGroup. ScheduleGroup has a Custom State “SelectedDate” type “number”

When any of the 7 GroupDayX is selected it set the “SelectedDate” state to the Current date/times “+X” Extracted date. (It’s setting the state to the day value so it can match the data in the “Day” field on the TimeSlots data)

Now here is the part I’m needing help with. I add a Repeating Group into the ScheduleGroup and set it’s type of content to “TimeSlots” and the Data source to “Search for TimeSlots” with the following constraints

Day = ScheduleGroups SelectedDate (state)
MaxCapacity > 0
StartTimeDATE > Current date/time

I have 2 input elements in the repeating group that just have the initial content set to “Current Cells Index +1” or “Current Cells Index -1” and those inputs are named "Input+1 " and “Input-1”.

There are 2 Text Elements in the Repeating Group as well. One to display StartTime and another to display EndTime

The StartTime text elements value is “Current cell’s TimeSlots StartTime” and I have the condition When “Current cell’s TimeSlots’s StartTime is RepeatingGroup TimeSlots’s List of TimeSlotses:Item #Input -1’s value’s EndTime” then the element is not visible.

The EndTime text elements value is “Current cell’s Timeslots EndTime” and I have the condition When “Current cell’s TimeSlots’s EndTime is RepeatingGroup TimeSlots’s List of TimeSlotses:Item #Input +1’s value’s StartTime” then the element is not visible.

The issue I’m stuck on is how to take this and fix it to where the start time and end time show on the same row of the repeating group. As is the start time and end time will show on separate rows unless the start time and end time is the exact values of one 30 minute slot.

I’m thinking I need to either use one text element that contains start time and end time but how do I consolidate the list for each?

Or I need to consolidate the list into it’s own consolidated list before trying to display it. but again, I’m not sure how.

Any help would be appreciated.

Thanks

2

Don’t have bandwidth to read thru entire post but off the bat:

  • Day should be # (day of week prob)
  • StartTime and EndTime should be number
  • Unless shifts cant overlap to next day then No need for End Time Date and if they can probably need Day begin and day end
3

I can change the Day field to a number type. I only use the day field to match it with the SelectedDay state

StartTime and EndTime can’t be numbers as they are the displayed fields and contain AM and PM

Shifts can not overlap but each shift has it’s own unique MaxCapacity number and the start at 7AM and end at 10PM

4

so why 2 dates?

you dont have to show them as numbers but if you want any sorting or filtering or calculations you need to stored them in DB as numbers.

5

2 dates? There are 7 dates worth of TimeSlots in the database. The StartTimeDATE and endTimeDATE fields that are type DATE is what I use for list sorting.

Timeslots are from 7AM to 10PM daily and each are 30 minutes in length. The each also have a MaxCapacity field that helps determine if that slot will be visible or not. A shift from 7AM - 7:30AM has a max capacity of 2 where a shift from 11AM to 11:30AM has a max capacity of 5

If all consecutive timeslots have a value greater than 0 in the MaxCapacity field then it can be added to the consolidated list. For instance if all 30 minute slots have a MaxCapacity value greater than 0 then it should display 7AM - 10PM on one line. but if the 11AM-11:30AM slot has a MaxCapacity value of 0 the the display should have 2 lines

Line 1 - 7AM - 11AM
Line 2 - 11:30AM - 10PM

6

You don’t need those. Use the start and end time. As a number as I mentioned. And if you insist on doing it that way then don’t store start Time or end Time at all.