Calendar times and automated notifications based on user selection days/hours

[DesiLogi]

Hi,

I have a Calendar/Tasks table that stores a date for either an appointment or a task due date.  I need to add a few functions and was hoping someone might have an idea of the best way to do this: 

1) I need to add a Time selection for a calendar event. Meaning the user selects a date like normal (using the date picker) to put into a date/time field (the 'Event_Date' field in this case) but ALSO can select a time for the event (like a normal calendar like iCal or Google Cal). I know I can create a text field and use a lookup table for all the times (like 2pm, 2:15pm, 2:30pm, 2:45pm, etc.) and store it that way. My question is, can I use a text field that stores a time like this (i.e. "2:45pm") to perform calculations in a formula to get how many days/hours/minutes the current date/time is from that stored date/time? Or does that time data need to be in a specific format or included in the 'Event_Date' value (which is a date/time field).

2) What I'm trying to do is allow the user to do something like set an appointment for a date/time like 4/15/2020 1:30pm and also select a 'notify me' value like "2 days before" or "3 hours" before and then use the Automated Tasks feature to send an email/sms reminder to the user. 

3) The last issue is how to store the date/time of the event because Tasks runs on UTC time and my users are all over the globe. So if a user in California puts in "4/15/2020 1:30pm" it's not the same date/time value as someone in Australia, in terms of the Tasks trigger firing. Maybe convert the users local time to UTC after they input it? Not sure of the best approach but to the user, it needs to be viewed/entered as local time (not UTC values) so it's understandable. 

I wish Caspio had a full calendar like this, built in, as these features are an integral part of most user needs. But if I can build it myself that'd still be great.  The first 2 issues to me are the most important, though, so if anyone has some advice on this, or how to get the result I need (a normal calendar with times and notification choices) I'd really appreciate it. 

[areena]

Hi,

I have a Calendar/Tasks table that stores a date for either an appointment or a task due date.  I need to add a few functions and was hoping someone might have an idea of the best way to do this

[kcastagnaro]

Hey DesiLogi,

 

Did you ever find a solution to your problem?

[DesiLogi]

I ended up using a solution that Caspio had implemented in one of their 'out of the box' apps. There's a app for scheduling, I think it was, that has a calendar with a submission form that uses js to create a time picker for the user. You can use the same setup in your own app. The time picker adds hh:mm to the mm/dd/yyyy value chosen the normal way with the date picker and stores it in the same field. So your single date field will have mm/dd/yyyy hh:mm as it's value and can then be used for a daily schedule, to trigger reminders based on both day and hour differences, etc. It works really well. Hope this helps--just check out Caspio's demo apps and you'll see a couple with calendar scheduling (I think it was the medical appointment scheduler). 

[kcastagnaro]

Thank you!