In part three, we chose a random (yet meaningful) birthday quote and ran it through a modified approval process, and we also started the process to choose the send-method for our birthday wishes. Today, we'll cover two of the methods and how each one processes the information and actually sends the wish out. Let's get started.
Getting started with eMail
For eMail and mobile (SMS), we won't need either of those to send until the actual day of the birthday, so I've chosen to write all of the relevant information to a different SharePoint list. This will act as a queue for a third flow that will run daily to check for any emails or SMS messages to send out. That part will be covered in another post; today we are just writing the information to the list.
The first step in the eMail path is to check how many eMail addresses we have for this particular contact. If there is more than one eMail address, we prompt the user to choose one; if there is only one, then we move forward with that address.
With our condition step above checking the length() of the eMail array, we can choose to default to the only eMail address if that count is not greater than one.
Note: Remember that "eMail" won't even be presented as an option if there are no addresses available.
To the right, we are adding the relevant details to a second SharePoint list. The "SendDate" is a little weird because I'm grabbing the person's birthday (MM-dd) and adding the current year. This will present problems for birthdays in the first week of each year, so I'll have to go back and clean that up later. For now, here is the expression to do what I did:
concat(formatDateTime(items('Apply_to_each')?['Birthday'],'MM-dd'),'-', formatDateTime(addDays(utcNow(),7),'yyyy'))
Note that the "SendMethodValue" for these are set to "eMail". This is so that the flow that polls this list and sends the messages will know which method to send for each respective wish.
After that, we'll send a confirmation to the user with some details, and then update the original SharePoint Birthday List so that this particular birthday is marked as "Processed".
The actions for those are AtBot's "Send Reply" and SharePoint's "Update Item", being sure to use the Item ID from the Apply to Each's current item (which is the current birthday being processed).
With multiple eMail addresses, I had to clean up the values a bit before I could present them as options to the user. The original email data comes over like:
[\"email@email.com\",\"2ndemail@email.com\"]
By nesting some replace() functions, I am able to get rid of the brackets, quotes, and slashes. I also use replace() to swap out the comma for a carriage return (which is still stored in our variable, varCR).
That whole expression will result in:
email@email.com
2ndemail@email.com
and can be used as the Choice Values in our AtBot action, "Get choice response from user". The full expression looks like this:
replace( replace( replace( replace( items('Apply_to_each')?['eMail'],'[','' ), ']', '' ) ,',',variables('varCR') ),'"',''
)
After that, the steps are the same as when there was a single eMail. We still want to send a confirmation to the user, and then update the Birthday list to reflect "Processed" to keep this birthday from being picked up again the following day.
Phone Number Formatting in Power Automate
Since we're already working on birthdays that will be processed on the day of the event, we'll look at SMS wishes next. These will work similarly in that the details will get written to a separate SharePoint list, which will be polled by an additional flow each day and then processed to send on the day of the birthday. Because Twilio is expecting the recipient's phone number to be formatted as +19495551212, we'll need to spend some time cleaning and formatting the values before adding them to our SP list. Using the same replace() trick from above, I first swap out any parens, dashes, and spaces with blanks (two single quotes with nothing in between):
replace(
replace(
replace(
replace(
replace(
split(
outputs('Mobile'),' > ')[1],'-',''
),'.',''
),'(',''
),')',''
),' ',''
)
Note that the split() is taking our arbitrary delimiter from part three, " > ", and grabbing everything after that, then applying all of the cleaning. To format the number after it has been cleaned, I'll check to make sure it starts with "+1" (without the quotes). International readers will spot right away that this is the country code for Canada and US calls. In future iterations, I'll check the contact's country and add the appropriate code in front. I'm using '+1' to keep things simpler for now:
if(
startsWith(
outputs('Clean_Mobile'),'+'
), outputs('Clean_Mobile'),
if(
startsWith(outputs('Clean_Mobile'),'1'
), concat(
'+',outputs('Clean_Mobile')
),
concat('+1',outputs('Clean_Mobile')
)
)
)
Using IF statements in Power Automate isn't much scarier than using them in Excel. They follow the same format, just some of the expression syntax is a bit different. Here, I am using startsWith() to check the beginning of the phone number. If it begins with a plus sign, I assume all is well and use the cleaned number. Otherwise, I'll check for a '1', and finally, I'll add a '+1' to get the complete and clean number. Here are the steps in the flow:
Both are using Compose actions to manipulate the values.
From there, the steps are the same as either of the email steps save that our SendMethodValue will be set to "SMS" so our future flow will know to process this as a text message.
We will add the row to the new SharePoint list, confirm with the user, and then update the original record as "Processed".
Next Week
Because the physical cards have a couple of challenges, we'll cover those next week.
If you haven't already caught my podcast interview with Mark Smith, check it out by clicking on the image below. I had a great time chatting about my journey to MVP and how I aim to make the Power Platform accessible to all levels of users.
Until then, get in, make mistakes, learn some things, and have fun! Try. Fail. Learn. Repeat.
Comments