i. Introduction
ii. Requirements
iii. Prerequisite setup
A. Formatting the Data Source
iv. Building the workflow
A. The trigger
B. Retrieve the Birthdays
C. Evaluating today’s date to see if there will be a birthday tomorrow
D. Sending Email notifications
v. Save and test the workflow
Introduction:
This workflow will remind the welfare team to prepare a greeting card for birthday celebrants a day before their birthday.
You can further customize the workflow to send a direct birthday wish to the celebrant on their birthday.
This workflow is intended to send a reminder to a welfare team or planning committee one day before the celebrant’s birthday. This team can then prepare a surprise package or birthday message for the celebrant.
Requirements:
To execute this project, you will need the following setup:
A work email account with Power Automate or Office 365 license
A data source with names and their birthday – we shall use an Excel sheet for this project.
Formatting the Data source:
• Enter the desired Names and birthday
• Add a column to extract the Month value of the birthday using this expression: =MONTH(B2)
• Add a column to extract the Day value of the birthday using this expression: =DAY(B2)
• Wrap the entire dataset in a table.
Your table should look like this:
Fig 1. A sample Dataset.
• Upload the file to a OneDrive folder. This is important as Excel files on local machines are not accessible via the Excel connector on Power Automate. We can only access Excel files hosted on OneDrive when using the Excel connector on Power Automate.
Now we have all the requirements in place, let’s build out the flow.
Launch Power Automate and Create a new flow.
A. The Trigger: We shall use a scheduled trigger for this flow because we want it to run at a specific time and frequency.
Set the interval to one, and the frequency to ‘day’. This would trigger the flow every day.
Select your time zone and the expected start time for the flow’s go-live date; then specify the hour and minute for the flow’s daily run.
The trigger should look like this:
Fig 2. A sample flow trigger.
B. Retrieve the File containing the Birthdays:
Add an Excel ‘List Rows Present In a Table’ action and specify the file location; pick the file using the file picker, and specify the table name.
The action should look like this:
Fig 3. A sample file retrieval action.
C. Add a condition to check if the Birth Month is the current month and if the Birth Day is the next day:
• Add a ‘condition’ action
For the Month check, we will use an expression to convert the month value retrieved from the Excel sheet into an integer. This is necessary for a smoother comparison handling in Aower Automate.
To achieve this,
• On the left-hand side of the condition, select the ‘month’ field from the dynamic content.
• Hover over this dynamic content and spell out what we refer to as the internal name or code behind the action: items('Apply_to_each_2')?['MONTH'].
Fig 4. Retrieving the internal name of a dynamic content.
Notice that the condition is immediately wrapped inside an ‘apply to each’. This happens because the field selected from the dynamic content references an action that retrieves multiple items (the ‘list rows in a table’ returns multiple items).
• Convert the dynamic content into an integer. We can simply do this by wrapping the internal name of the ‘month’ column in a simple expression:
int(items('Apply_to_each_2')?['MONTH']) – this will be for the left-hand side of the first row in this ‘condition’ block.
• Clear out the ‘month’ field previously selected into the left-hand side of the ‘condition’ and switch over to the expressions tab.
• Paste in the expression formed above thus: int(items('Apply_to_each_2')?['MONTH'])
• Click ‘ok’.
The expression should look like this:
Fig 5. A sample expression to convert the month value into an integer.
• Set the operator to ‘is equal to’
• Set the right-hand side of the condition row to: int(formatDateTime(utcNow(), 'MM'))
• Add a new row to the existing condition and set the logical operator to ‘AND’
• For the Day check, use this expression on the left-hand side, repeating the same steps used to retrieve the internal name of the ‘month’ column and convert it to integer thus: int(items('Apply_to_each_2')?['DAY'])
• Set the operator to ‘is equal to’
• Set the right-hand side of this condition row to: int(formatdatetime(adddays(utcNow(), 1), 'dd'))
The condition should look like this:
Fig 6. A sample condition to evaluate birthdays.
D. Send Email Notification:
For the ‘If Yes’ branch of the condition, add a ‘send an email’ action and configure its properties.
Fig 7. A sample send email action.
An action can be added to the ‘If No’ branch of the condition or left blank. In this case, we shall leave it blank.
The whole condition block should look like this:
Fig 8. A sample condition block.
Save and test the workflow:
Before testing the flow,
• Ensure that you have a birthday that corresponds to tomorrow’s date on your Excel sheet.
• Close out the Excel workbook if it is currently opened as Power Automate will not be able to access the file if it is currently in use.
You should have all greens and receive an email when you test the flow.
Top comments (0)