I am pretty good at remembering birthdays. Where I have trouble sometimes, is giving myself enough time to go buy a card, think of something meaningful to write in it, and then remembering to mail it…all with enough time for it to get there in time. My friends are nodding right now, understanding why they always get a Happy Birthday text, instead.
Naturally, I started contemplating ways to manage this with Power Automate…and I think I’ve come up with something pretty cool.
The entire process begins by checking each day for upcoming birthdays, then sending an SMS message alerting me if anything is seven days out. This will give me enough time to decide what I want to do.
By interacting with Power Automate through SMS, I can decide if I want to send a birthday message via SMS, eMail, or a physical card to either a work or home address if available. This involves a few interesting steps, including a chat-bot, getting birthday quotes from a repository, and an SMS connector. The scope of things expanded pretty quickly as I imagined the journey. Because I’m limited on some of the more technical details, I had to break the conversation in two, separating the notification aspect from the action aspect. Today, we’ll cover the notification part.
This starts with a Recurrence trigger in Power Automate, set to trigger once per day. I anticipated the possibility of having multiple birthdays on a single day, so I also initialized an integer variable to manage a count of birthdays (I pictured a text that said something like, “You have 6 birthdays coming up next week”) and an array variable for me to store the contact details of each person with something to celebrate.
The next bit will set and format a date that is seven days out, then grab all of my contacts who don’t have empty birthdays. That step was a little weird because of how the Outlook connector translated the null in my OData filter. I couldn’t really get it to work the way I wanted, so the consensus was to just look for dates that were greater than a really long time ago. Fellow MVP, Pieter Veenstra, ended up writing an article on it after we talked: “Filter outlook contacts with birthdays only“.
Note that without anything in the “Top Count” field, the connector seems to only return 10 results, hence the really large number there. When I figure out how, I will incorporate the “7 Days Out” part into the OData filter in order to skip the next ‘Condition’ step.
Here, I wanted to show another way to format dates that didn’t utilize an expression. While expressions are fun and can speed things up, I remember being somewhat intimidated by them and felt there might be some folks facing the same ideas. If you use Convert Time Zone and just keep the Source and Destination zones the same, you can take advantage of the “Format string” to change your date format without an expression.
Because I want to work through each of the results that came back from my “Get Contacts that have Birthday Data” step, I have an Apply to each loop that formats each birthday in the MM-dd (note the caps) format to match my “A Week from Today” step, then compares the two to look for a match.
If there is a match, then I up the count of birthdays by one using an “Increment Variable” step, then I used an “Append to Array Variable” step to add the contact details to an array to be used later.
If the dates don’t match, then we just cycle through to the next record, and step outside of the “Apply to each” when complete.
I wanted my notification to sound less robotic, so I knew I would have different wording if there were multiple birthdays or a single birthday. Because of that, my next “Condition” looks at the count of birthdays and will send the correct text accordingly.
The expression I am using to get the name of the person with the single birthday is:
For more details on how to pull elements from an array, check out MVP Elaiza Benitez’ video, How to avoid Apply to Each from appearing. I keep this video close by most of the time as I’m still struggling with array-related expressions.
Sending the SMS notification using Twilio at the end could just as easily be a push notification, an email, or a message inside Microsoft Teams. Again, the flexibility of Power Automate puts the power in your hands and lets you decide how to shape your process.
Get in, make mistakes, learn some things, and have fun! Try. Fail. Learn. Repeat.
About the Author:
Ed Gonzales is a Microsoft Business Applications MVP and Certified Trainer. He has been working in technology for twenty-five years, covering many roles and developing new ones along the way. His journey to bring relationship management (xRM) solutions to his employers has led to several awards and continued growth. Ever the polymath, his current endeavors include aviation science, aquaponics, and fostering orphaned kittens.
Gonzales, E. (2020). Birthday Bot – Part One – Send eMails, SMS, or Physical Cards with Power Automate. Available at: https://www.flyingpolymath.com/post/birthday-bot-part-one [Accessed: 10th April 2020].
Find more great Power Platform content here.