I’m creating a happy hour app and I would like to sort upcoming happy hours by the current time.
So, if it’s 1:00 pm any happy hours that are ongoing will be displayed first as “ongoing” and any happy hours that will start after 1:00 pm will say “upcoming”, and happy hours that have finished before 1:00 pm will be hidden.
When adding a happy hour to the database I fill out four time-related inputs for each happy hour.
The opening_hour and closing_hour are data types that are linked to an option set with the numbers 1:00 through 12:00 in 15-minute increments.
Trying to display the data as I mentioned above is where I am stuck.
When a user adds their happy hour to the database a workflow copies the opening_hour input into a data field called sort_time which is a number field. Then, I sort the repeating group by the sort_time.
However, this is a problem because any happy hours that start before 12:00 pm noon are at the very bottom of the repeating group because the 11 in 11:00 am is a larger number than 1.
How can I sort my repeating group to also take into consideration the am or pm, and make sure that the am times go before the pm times?
I should be able to figure out the “ongoing” and “upcoming” conditionals afterward.
Add Order field of type number to option set, fill it appropriately and sort by that field.
Thanks so much for your response!
Would you mind sharing more information? I’m not sure how to do what you’ve suggested.
I have two option sets, one for hours of the day and one for am/pm
This is what the hours of the day option set looks like:
This is what the am/pm option set looks like
The sort_time is a data field inside my Happy Hour data type. This is how I’m currently sorting by opening time, but it doesn’t take into consideration the am/pm option set.
This is what the Happy Hour data type looks like
Thank you for your help!
Then I’d suggest a different approach:
Happy Hour data type add
close_time fields of type
_Hours_Of_The_Day add field
Offset of type number indicating number of minutes since 12:00, and fill them appropriately, so that
12:15 option would have it set to
1:15 would be set to
75 and so on…
_Hours_AmPm add field
Offset of type
number and set it to
720 (12 * 60) for
- When creating/updating
Happy Hour thing, set
_Hours_Of_The_Day's Offset + _Hours_AmPm's Offset, same about
- You can calculate hour of closing_hour using
close_time / 60 :floor, and minutes of closing_hour using
close_time <- modulo -> 60, and time in a date using
date :rounded down to date :+minutes Happy Hours's close_time
- Now to correctly sort hours within a day, just use natural sorting by close_time or open_time.
- If you need to display happy hours for the whole week, you will probably add similar
Offset field to
_Day_Of_The_Week indicating number of minutes since Sunday midnight, but there you will most probably face leap year problem as well as locale specificity (not sure if this is important for your case). In this case you need to rethink the whole approach, as it does not suit locale-aware contexts (when, for example, week starts on Monday instead of Sunday).
You don’t need AM/PM, you just need to work with time as computers do, which is in 24 hour increments, and broken down into minutes and even further into milliseconds.
Data for time should just be a number field, between 0-23 for hours and have another field for minutes, between 0-59.
You can alter your option sets to include attributes such as these, but that will make sorting impossible since you can not sort a data type based on data fields related to option set values.
So, perhaps, on the data type, just have a single field of type number that represents a total number of minutes (12:15PM is equal to 12*60+15=735 minutes) then you can sort by that field from the data type, and can easily display values to users in format of 12:15PM by doing simple math from the field)