Appointment Notification and Reminder

[JM-Koalas]

Greeting,
I am using airtable as CRM with all leads being managed and assigned via airtable.
One of the main challenge I have is that a lot of the Sales team are missing their appointments (Whenever a lead come to the system, it comes with the best date and time to call)

I want to create a reminder and email notification to be send to the sales team 10 minutes before each appointment so they can call the customer on time

How can i create such column with 0 or 1
Whenever the time of appointment is 10 minutes earlier to the current time it turns into 1
then i can create a view with filter for all the value of (1)
And finally i can create a zap for each new record on that particular view (I can take care of it afterward)

Is that possible?
If yes, then how?

Thanks in advance for your support and collaboration.

Regards

[Justin_Barrett]

Sorry that this hasn’t had a response sooner. I’ve been automating some Airtable tasks internally using a time comparison between a date field and NOW(), and was able to leverage that approach to test this idea, and it was successful. That said, my prior experience with these internal time comparisons is that they don’t always happen precisely when you expect. My formula below uses a 10-minute lead time per your request, but you might need to pad your lead time by a few extra minutes just in case.

IF(Date, VALUE(DATETIME_FORMAT(SET_TIMEZONE(NOW(), "America/Los_Angeles"), "YYMMDDHHmm")) >= VALUE(DATETIME_FORMAT(SET_TIMEZONE(DATEADD(Date, -10, "minutes"), "America/Los_Angeles"), "YYMMDDHHmm")))

Change the timezone references to your local zone, and change Date to the name of the field containing your appointment date and time. If you want to increase the lead time, change -10 in the DATEADD() function.