Twilio - Automatic Appointment Reminders 1 day in advance

[Taylor_BHC]

Apologies if this is not the right board.

Currently I am trying to setup a Flow that scans a Calendar for upcoming Appointments (1 day in advance) and sends an SMS message (via Twilio - currently configured with no issues) to the person that appointment is for.

Our external scheduling software can be imported into an Outlook Calendar. It generates a unique Number (called an MR #) assigned only to that person that is included in the body of the event once imported.

If I create a sharepoint list that includes their mobile number and and their MR # in separate columns, how would I configure the Flow to conditionally search the Body of the upcoming for that MR #, if that MR # matches the MR # in the Sharepoint list, send an SMS via Twilio to the Mobile Number associated with the MR #. 

Or in other terms I want the Flow to look like:

[When an Event is Upcoming - 1440minutes look ahead] - if [Event Body] CONTAINS [ANY MR# from Sharepoint List] then Send a Twilio SMS to [Mobile Number Associated with MR #]

I hope this is not confusing to understand. It has been plaguing me for weeks!

[v-yamao-msft · Accepted solution]

Hi @Taylor_BHC,

How does the event body look like? What’s the column type of the MR# in SharePoint list?

I have made a simple flow for your scenario, please take it for a reference.

The event body contains just a MR#, and the MR# in the SharePoint list is of type Single line of Text.

Note: To get the unique MR# in the event body, make sure the MR# is always placed in the same place so that you could extract them from the body properly. And let's say that the MR# are in the same length.

And for testing, I used the trigger “When an event is added, updated or deleted”, you could change it to the trigger “When an event is coming soon” on your side.

Then use the action Html to text to convert the email body to plain text.

In action Compose, use the following function:

length(body('Html_to_text'))

In action Compose1, use the following function:

add(indexOf(body('Html_to_text'),'MR#:'),4)

In action Compose2, use the following function:

substring(body('Html_to_text'),outputs('Compose_2'),sub(outputs('Compose'),outputs('Compose_2')))

Then Get items from SharePoint list.

Add a Condition to check if the items from the list contains the unique number returned from Compose3.

If yes, send Text message(SMS).

Best regards,

Mabel Mao

[Taylor_BHC]

This is very helpful. 

The MR # in the body looks like "Client Appointment with MRN XXXX" (MRN is always a 4 digit Number). The Column type for the MR # in the list is a Single Line of Text.