How to automate SMS according to date?

[Kieran_Ball]

Hi guys! Relatively new to Airtable. I’ve got what feels like an advanced question.

I want to be able to send SMS messages to members of my database. I know there is a Twilio block in which to do this, which is great and very useful (I like the feature that pulls in Name and other fields that personalise the messages). However, I want this to be automated. I am currently building a base for general management of members data, including their upcoming appointment dates in a field entitled ‘Date of appointment’.

What I want to happen, rather than doing it manually, is for an SMS such as ‘Hi {First Name, Last Name}, you have an appointment with {Name of doctor} on {Date of appointment}. Please call our offices if you’re running late on 07xxxxxxxxx.’ to be sent when {Date of appointment} is 24 hours away, for example.

I’ve also had a look at Zapier to help with this but I’m not entirely sure how to do it. Can any body help/point me towards a tutorial or something that deals with this?

I hope that’s clear and not too vague nor convoluted.

Thanks so much, Harry

[Adam_Dachis]

@Kieran_Ball

Hi Harry,

Yes, this is actually pretty easy (once you know what to do)! It seems like you’ve already got the message portion out of the way, which is great. If you just have a formula field created to automatically fill in the {curly bracketed} information then you just need a very simple automation to send the messages based on dates.

Any no-code automation platform can accomplish this. Zapier will make the setup as simple as possible but it will cost more and won’t work on records it has already seen. The latter isn’t a big deal once you’ve set everything up and activated the automation, but you can end up with a bunch of records that will never trigger when you create the automation the first time. This is easily resolved by duplicating them and deleting the originals after the fact, but it can get a little frustrating.

Integromat is another popular option that costs less and does not have this issue, but it is a little less intuitive to use for the first time. Personally, this is something I’d use Integromat to accomplish because the more-precise control it offers would make me feel more at-ease. With something like this, I’d rather have to specify more details to make sure it’s working exactly the way I want rather than go the simple route and be surprised by a problem.

That said, I wouldn’t expect Zapier’s limitations to lead to an issue in this case but I can see a few ways a small mistake might. Also, depending on your message volume, you could probably get away with using Integromat’s free plan for this. You could also probably do this in Zapier for free as well, but with greater limitations on message volume. Naturally, this is separate from the cost of using Twilio (which, as I’m sure you know, is very minimal for basic SMS).

As for how to do it…

1. You just need to create an automation on either platform that uses Airtable as its trigger and triggers whenever a new record comes into a specific view that you’ll create. This view should filter out every appointment that’s less than 24 hours away or in the past. You can set this up easily with the filtering options for your {Date of appointment} field. You should also create a {Message sent} checkbox field and filter out any messages with that box checked.
2. Next, have Zapier or Integromat look at that view for new records and send the contents of the formula field that contains the message with the customer’s information filled in to the number associated with that record. When it finds one, it should trigger a Twilio action that sends that message.
3. Finally, as a third action, it should update the record it found by simply checking the {Message sent} field’s box so that the appointment will not accidentally re-trigger for some reason. Because of Zapier’s limitation, you don’t really need this last step if that’s the service you’re using. (Also, if you’re using a free account you can’t add a third step—that’s a paid feature in Zapier.) While you probably don’t need this step in Integromat, either, you can do it with any account type and it’s simple to do so I figure it’s worth it for peace of mind. You don’t want to risk accidentally spam a user with appointment reminders, after all, even if it’s unlikely to occur. :slightly_smiling_face:

Although I don’t have a specific how-to article for you that shows exactly how to pull this off, I do have one on creating a daily digest which uses a very similar automation. Also, I do have a how-to on creating a simple booking system that doesn’t help with this, specifically, but provides a simple way to take appointments through an Airtable form if that’s something you want to do as well.

Lastly, if you want to request confirmations for appointments over SMS as well you can do this with an automation that’s somewhat the reverse of what I just outlined. So long as you never have someone with two appointments within 24 hours of the other it’s a pretty simple setup. If not you just have to do a few additional checks. Once you’ve got this going, if you’re not sure how to set that up and want some help just let me know. Of course, let me know if you have any questions about this as well.

Hope this helps! :slightly_smiling_face:
Adam

[Kieran_Ball]

Thanks so much Adam, this is so helpful and comprehensive. I will get to work and let you know if I have any questions.

Cheers,
Harry

[Kieran_Ball]

@Adam_Dachis I should clarify, I have only managed the message portion in the actual Twilio tool. I would love some help on how to do this in the Airtable formula field. I understand everything else, but would really appreciate some more on step 2.

Thank you!

Harry

[Adam_Dachis · Accepted solution]

@Kieran_Ball

Hi Harry, I’m glad this was helpful!

I assumed you’d done it in a formula because what you pasted is very similar to a formula. :slightly_smiling_face: I haven’t used the Twilio block in awhile so I didn’t realize that was the format it takes. Here’s the formula version of what you put in your original message:

'Hi '&{First Name}&' '&{Last Name}&', you have an appointment with '&{Name of doctor}&' on '&{Date of appointment}&'. Please call our offices if you’re running late on 07xxxxxxxxx.'

I didn’t test that so it’s possible I made a typo. If it gives you any errors it’s either because I put a single quotation mark in the wrong place (or forgot one), left out an ampersand, or something like that. Basically, if you just need to make a message that has variables in it you put the static text in ‘single’ or “double” quotation marks and the field names in {curly brackets}. You use & in between each static text and field name (variable) to tell the formula that you want to concatenate (combine) these things. I use the word concatenate because it’s the technical term but also because it’s the name of a formula function that does the same thing in a different way. :slightly_smiling_face:

If you take a look at the Airtable formula field reference you’ll find the CONCATENATE function in the text functions and operators section. I mention this because some people find CONCATENATE easier to work with. You could use it to make your variable message into a formula like this:

CONCATENATE('Hi ', {First Name}, ' ', {Last Name}', you have an appointment with ', {Name of doctor}, ' on ', {Date of appointment}, '. Please call our offices if you’re running late on 07xxxxxxxxx.')

The difference is just that the ampersands become commas, everything is surrounded by (parenthesis), and CONCATENATE is prepended to the whole thing. Functions always work like that in formulas. You type their names in all caps and follow them up with parenthesis: CONCATENATE(). Everything inside the parenthesis are parameters for the function. CONCATENATE happens to have a (virtually) unlimited number of parameters because it’s just combining them all together in a linear fashion. Most functions have a specific number of parameters that need to be specified in their designated position and some functions have no parameters at all.

The formula field reference page provides an overview of everything so it’s a good thing to bookmark. Even though the formula field does a really good job at helping you as you type, it can be hard to remember the name of every function. I’ve written a more in-depth version of this documentation—which is still being edited otherwise I’d share that as well—and have been writing tons of formulas for year. I can’t remember 25% of the names so it’s useful regardless of your experience. But even if you don’t bookmark it you’ll find a link to it in the formula field options when writing your formula. :slightly_smiling_face:

You can do some really interesting and powerful things with formulas so it’s worth learning a little more about there’s anything in Airtable you’re hoping to be able to do but aren’t show how to approach it. The power user’s guide is a great place to start if you want to learn more. The other sections provide a lot of helpful foundation for the ways you’d be able to leverage formulas, but you can start with the last section to learn more about formulas right away.

If you have any specific questions, please feel free to ask. And if there are any resources (e.g. an article, video, etc.) you wish you had that would help you accomplish anything in Airtable, please let me know. I keep track of that stuff and try to create the resources that people need the most.

:slightly_smiling_face:
Adam

[Kieran_Ball]

@Adam_Dachis Again thank you so much! Very helpful. I have successfully set the automation up, thanks to you.

I created the CONCATENATE formula fine but in the end I didn’t need it, or any formula in fact. Twilio and Zapier automatically pull in the records I need in the message template, so no curly brackets needed this time!

I have another question now, which is about the form view. I am setting up a shareable form for colleagues to easily enter patient information (without having to teach them Airtable) which is super useful. However, the ‘phone number’ section is a bit tricky. I am UK based, and therefore the phone numbers are in the 07 format. However, I am using Twilio of course which demands international dialling codes. I have put a placeholder in the entry box asking if the number could be entered in +447777777777 format. This is obviously not an excellent solution, as I have tested it and people still enter their usual 07 format. How do I make it have the dialling code by default or make it mandatory in some way that they type it in the required format?

Thanks Adam!

[Kieran_Ball]

@Adam_Dachis And another one: Going back to your original reply to me, and concerning step 3.

I have a pro Zapier account so creating a third step isn’t a problem. However, I don’t quite get how this should be so difficult? I essentially just need to check the ‘message sent’ field in my Airtable records when it has sent the message. Is this at all possible? Apologies, I know you’ve already touched on this, it’s just unclear.

I click to add a 3rd step, and it has ‘update a record’ as the action, which seems correct - but then I get a bit lost. It has the options to: 1) add a search step 2) choose a record ID or 3) use a custom value (advanced)

Will I have to look at integromat? I just stuck with Zapier as I’m more familiar is all.

Harry