@eddiecheng It's best practice to filter the list of items first before you loop through them. Currently, the Get Items action in your flow isn't using a filter query. If there is a way you can reduce the overall number of items returned with this action—I'd highly recommend you do that with a filter query.
Instead of looping through all items in your SP list and running a condition check to see if the To HR-3 date matches today, you should use a ODATA filter query to filter those items first. I would recommend creating a brand new flow so that you can follow along and run tests to better understand the flow.
Convert Time Zone
Whenever you are using dates and times in your flow—it's best practice to use a Convert Time Zone action. In the base time field, insert the utcNow() expression.
Source Time Zone: Coordinated Universal Time
Destination Time Zone: Local Time Zone
Format String: Round Trip
Get Items
You will need to use the internal column name of your To HR-3 column. Keep in mind that the internal column name may not always match the name displayed in your Sharepoint list. If you aren't sure how to get the Internal Column name, you can refer to this section of one of my YT Tutorials.
In between the single quotes, you'll need to insert the date from the Convert Time Zone action. However, you'll need to format the date. Use the formatDateTime() expression.
Click on the Dynamic Content tab and insert the converted time output.
Add a comma and single quote.s Between the single quotes enter a date time pattern. In your case use:
yyyy-MM-dd
Return a Count
Whenever I use a Filter Query in a Get Items action, I always like to return the count of items returned in a Compose action. This is helpful when building a flow and can also be used to troubleshoot your flow.
Insert a Compose action. Add an Expression. Use the length() function.
Select the Dynamic content tab and insert the value dynamic content from the Get Items action into the length() function.
Run a test. Review the output of the Compose action to ensure that the number of items returned is what you are expecting.
Condition Check
Add a Condition action to your flow. If items have returned (aka there is number stored in the Compose action—that is not equal to 0), add the rest of your actions to the Yes branch. If not, do nothing.
... more to come
In the meantime you might be interested in these tutorials:
How to Send a SINGLE EMAIL ✉️ with multiple SharePoint list items | Build THIS Power Automate Flow
IN THIS VIDEO:
✅ How to send multiple list items in a single email with a Power Automate Flow
✅ How to create a dynamic date range
✅ How to use the Convert Time Zone action
✅ How to use a Filter Query in the Get Items action
✅ How to count number of items in an array
✅ How to use the Select action to extract a users display name and email address
✅ How to create a unique list of email addresses
✅ How to use the Create HTML Table action
✅ How to customize the HTML Table with CSS styles
✅ How to use the Send an email (V2) action
✅ How to use the Append to String Variable action
✅ How to create a custom list of items for an email
✅ How to use the Send an email (V2) action
✅ How to display singular or plural text based on the number of items returned
---
Send Emails Based on a 📆 Date Column in SharePoint with Microsoft Power Automate
In this Microsoft Power Automate tutorial, I’ll show you how to build a flow that will send a Happy Birthday email to a user based on a date column in a SharePoint list. The SharePoint list also contains a column with a Manager’s name which we’ll use to send a three-day and day of reminder to the user’s manager.
This automation will use the Filter Array action to filter out all SharePoint list items where the user’s birthday is today or in three days. This flow can apply to a variety of scenarios such as:
📅 Student Birthdays
📅 Project Due Dates
📅 Contract/Membership Renewals
📅 License Expirations
📅 Client Anniversaries
IN THIS VIDEO:
✅ How to Send an Email based on a Date Column in SharePoint
✅ Using the Recurrence Trigger in Power Automate
✅How to Use the Filter Array Action with multiple conditions
✅ How to Get Dynamic Content from a Filter Array Action
✅How to Get a Date Three Days from Today
✅ How to Create a Dynamic Date Based on utcNow()
✅ How to Return a Count of Items
✅ How to initialize and set a variable
✅ How to use the Send an Email (V2) action
✅ How to send test emails
Hope this helps!
If I helped you solve your problem—please mark my post as a solution
✅.
Consider giving me a
👍 if you liked my response!
👉 Level up your Power Automate skills by checking out my tutorials on
YouTube👉 Tips and Tricks on
TikTok and
Instagram
