Send SMS reminder automatically based on appointment date

[Verify_i-Nine]

The primary table contains an appointment date (date field - ‘Appt Date’) and pre-set appointment times (single select field - ‘Appt Time’). We have configured a single-select field (‘Action’) to manually add the record to a view, which successfully triggers the sending of a text message via Zapier and Twilio.

We want to automate the sending of the text 30 minutes before the appointment time, but we have almost no experience with formulas. The formula should set the ‘Action’ field to ‘Send Appt Text Reminder’ 30 minutes before the ‘Appt Time’ when ‘Appt Date’ is today.

Your help is appreciated and suggestions for a better way are welcome. We are slightly limited, I think, by the fact that the Zapier/Airtable trigger is when a record first appears in a view. But we have everything working manually; now we want to automate the sending.

[Databaser · Accepted solution]

Hi @Verify_i-Nine and welcome to the community!

If your time in “Appt Time” is configured as, eg, “08:00”, you can use this formula (works for me):

DATETIME_FORMAT(DATEADD(DATETIME_PARSE({Appt Time},"hours"),-30,"minutes"),"HH:mm")

• “DATETIME_PARSE” is needed to convert the string in your “single select” field to a date configuration
• “DATEADD” lets you subtract the 30 minutes
• “DATETIME_FORMAT” lets you have an output without the date. Because the “Appt Time” field only has the hours and minutes, if you wouldn’t add this, it would always also show the current date.

Does this work for you? You can set the automation to this hour, although I didn’t test that part.

[Verify_i-Nine]

Thank you, Databaser; this almost worked. Here are the results and a few more details.

Currently, times in the single select “Appt Time” are in the format 9:30am 10:30am, etc. There are entries on the quarter hour: 9:15pm, 10:45am, etc.

The formula (now in the field “Reminder Time”) converted:

9:30am to 08:30 (1 hr)
10:00am to 09:30 (30 min)
10:30am to 09:30 (1 hr)
11:00am to 10:30 (30 min)
9:15pm to 08:30 (45 min)

To use the formula, I must apparently convert the “Appt Time” entries to military time.

Regarding the automation, I think it should be configured to trigger “When ‘Reminder Time’ is …X” and ‘Appt Date’ is today. X should probably be current time, but I am not sure what to enter for ‘X’ since ‘Reminder Time’ is a formula field.

[Databaser]

Databaser:

DATETIME_FORMAT(DATEADD(DATETIME_PARSE({Appt Time},“hours”),-30,“minutes”),“HH:mm”)

Ah yes the 12am/pm… Nothing beats our 24h clock :grinning_face_with_sweat: If you add a 0 (zero) to your time slots, you can use this formula, which leaves out the “am”/“pm” text:

DATETIME_FORMAT(DATEADD(DATETIME_PARSE(LEFT({Appt Time},5),"HH:mm"),-30,"minutes"),"HH:mm")

[Verify_i-Nine]

This worked perfectly; thanks!

Regarding the automation, I think it should be configured to trigger “When ‘Reminder Time’ is …X” and ‘Appt Date’ is today. X should probably be current time, but I am not sure what to enter for ‘X’ since ‘Reminder Time’ is a formula field.

[Databaser]

So

“Reminder Time” > needs small “hh” iso “HH” to work with am/pm iso 24h clock I realised

DATETIME_FORMAT(DATEADD(DATETIME_PARSE(LEFT({Appt Time},5),“hh:mm”),-30,“minutes”),“hh:mm”)

new formula field > “Current Time”

DATETIME_FORMAT(NOW(),“hh:mm”)

another field to match “reminder time” with “current time” > “send sms”

IF({Reminder Time}= {Curent Time},“send sms”,BLANK())

Now you can use “send sms” as a trigger. “When ‘send sms’ is not empty” > trigger.

Be aware though, there are limitations for

NOW()

and

TODAY()

fields as stated here.

Be aware 2: depending on where you are located, you’ll probably have to add a local timezone.

[Verify_i-Nine]

Once again, Databaser, thanks for the incredibly helpful instructions!

Because the “Current Time” doesn’t refresh in real time, “Current Time” never equals “Reminder Time” (except maybe occasionally by chance) so the automation never triggers. I changed the formula for the “Send Appt Reminder” field (you called it “send sms”) slightly:

Blockquote
IF({Reminder Time}<{Current Time},“SEND”,BLANK())

Now, the automation can work. It’s less than optimal because the automatic update of the current time only happens every seven minutes or so, but at least it happens. I increased the reminder lead time to 45 minutes to compensate.

The time comparison above causes “SEND” to appear in a field we called “Send Appt Reminder.” This moves the record into another view. That is a trigger for the Airtable Zap in Zapier. We send the SMS reminders through Twilio.

I stuck with military time for the formulas but that resulted in texts like “Your appointment is today at 16:30 ET. Call 800-555-1212 at that time.” Not everyone understands military time, so I used Zapier’s Formatter to change the time format from military to am/pm right before the text is sent.

Everything works as desired. Databaser, we wouldn’t have figured this out without your assistance! It was greatly appreciated.