Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Dec 28, 2021 09:16 PM
I’m trying to design an easy-to-enter table or form where people can enter bookings very quickly. The issue is with choosing a date, a start time and an end time.
In other software I’ve used - you can tap a 'Start time" field - quickly type 10 or 1030, or 10:30, or 7 or 730 or 0730 or 07:30 and the software sorts out the formatting. (This is 24hr format by the way)
I’ve been trying to get something similar in Airtable and got halfway there with DateTimeParse - but it has been choking on 3-digit times (eg 730 for 7:30am). I’ve even done a formula including an IF statement to check the length of the input and try a different format.
But frankly these all seem like massive cludges to fix a basic lack in Airtable - either a Time-only field type, or Input Masking - or input format forcing.
I should be able to easily choose the correct format for a field and if someone enters a time incorrectly it will pop up a message to direct them to use the correct format (in this case a 4-digit time field).
I’ve been adding second and even third Formula Columns next to the input column in order to correctly format all possible input formats.
By the way I don’t want to use the normal Date field with time, because it is unnecessary extra steps for the user, and can also add errors into the entries. People are choosing a single date, then entering a start time and an end time.
Perhaps there’s a simpler route that I’m not considering?
Dec 28, 2021 09:50 PM
Welcome to the community, @MarcusV! :grinning_face_with_big_eyes: Airtable’s lack of input validation is a known sore spot for many. When it comes to using a field to only enter times, here are some workarounds:
DATETIME_PARSE()
to parse the final result. Pros: it’s impossible for the user to make a mistake. Cons: it takes a bit of time to set up all of the options.Dec 29, 2021 02:33 AM
Thanks for the terrific response Justin. Very much appreciated!
I had begun work on two of your possible solutions in some form.
1 - a single line text field with another column that uses a formula to parse the input using some combination of DateParse and DateFormat. Haven’t quite nailed it yet though
2 - I started setting up a table to link to that contained all the possible time combinations so that we could use single select to choose a time.
But having a looong list to pick from isn’t really the best user experience!
We already use 24hr time and we are all collaborators, or will be if this works. So some of your suggestions are still valid options.
I’ve haven’t looked into regex validation yet. I have very limited experience in regex so that will be another learning curve! Which seems ridiculous for airtable to force me into jumping through so many hoops to achieve this simple option that I could do in an Excel spreadsheet probably decades ago!
The second component of my “complaint” is the lack of a native time field. I wouldn’t even be looking for input validation workarounds if we just had that. Seems pretty basic.
Oh well I appreciate your excellent reply and I will give your suggestions a go soon!
Dec 29, 2021 10:12 AM
True, and there are other threads that have discussed this issue at length. The main argument that I’ve seen against having a time-only field is that time isn’t necessarily an independent data point. It’s always part of a reference to a specific day, which (guessing) is why Airtable only includes time as an option in date fields. Even when working in JavaScript, there isn’t a separate data type to represent time only. It’s part of the Date
type. My gut says that a separate time field will never be implemented, but part of me kinda-sorta wants to be proven wrong.