Schedule weekly email with quote details and hyperlink | Part 1

In todays article I am going to explain a specific requirement and show you how we can use power automate to satisfy this particular requirement. A few months ago I had to demonstrate a scenario where sales people needed to get a reminder for outstanding quotes they needed to follow up on. The sales people would create quotes on a daily bases, but not all quotes would be completed ‘all the way’ and might need follow up on a later date. The requirement here was to send a weekly reminder email to sales managers, listing only the quotes from which the date in the out of the box ‘effective to’ column (system name ‘effectiveto‘) fell within the week.
This is probably not the hardest thing to do, although it is a little tricky with the date/time fields. (I will show you how I got the dates only so it would be more accurate when comparing the dates with the quotes in Dataverse) The harder thing was the need to have a clickable link to the quotes embedded in the email, so that managers could simply click the link directly from the email to open the quote in Dynamics 365 Sales. In this scenario I am using quotes, but obviously you can use this functionality for any table. Let’s begin!

Trigger

The first thing you’ll need to do is select the type of flow to create, which in this scenario is a scheduled cloud flow. When you select this type of flow, you’ll notice that a window will open, where you can configure the start date and time of the flow, and how often it needs to be repeated. In my case I will run this every Friday at 9AM so that folks can spend their Friday afternoon following up on quotes. You can also add a name for the flow on this page, if you don’t, the system will generate a name for you.

Once you click the ‘Create’ button on the bottom the flow will open and you’ll notice the ‘Recurrence’ trigger has already been created. It also shows the recurrence settings; in my case it shows ‘Runs on Friday every week’. There is also an edit link which you can click if you need to change the recurrence or start time. The next step I will add is the ‘Get future time‘ step. I want to get the date that is 1 week from when the flow runs, so I can use that to filter the quotes later. Click on the ‘+New step’ button’ and search for the ‘Get future time’ step. For the interval I choose ‘8’ and for the time unit I choose ‘Days’. The reason I am choosing 8 days, and not 1 week because I noticed after playing around with this, that 1 week is not giving me all the data that I need. For example if I want to get all quotes with dates between 01/05/2023 and a week later, which is 1/12/2023, using the 1 week interval didn’t bring in the quotes that are dated on 1/12/2023. Using 8 days fixed this issue for me.
The next step I need to create is a ‘Compose’ action. I will use this to only bring back the date value, and strip away the time portion of the data. (The reason for this is so that I get more accurate filtering. I don’t want to exclude quotes because of the time component that is also stored in the column.) I renamed the compose action to ‘Future Date’ to make it easier for me to identify the step. I didn’t know how to write the expression from the top of my head, but the new ‘Format data by examples’ preview feature helped me with this! Click inside the ‘Inputs’ field of the ‘Compose’ step, then click on the ‘Expression’ tab of the window that pops up and paste the below expression in:

formatDateTime(parseDateTime(split(body(‘Get_future_time’), ‘.’)[0]), ‘yyyy-MM-dd’, ‘en-US’)

In the next step I need to get the current date. There is no ‘Current Date’ action, so instead I will get the current time, which also includes the current date. Then I will do the same thing I did with the ‘Get future time’ step, where I will strip out the time portion of the value and will only store the date portion.
After the ‘Current time’ step I add another ‘Compose’ step which I rename to ‘Current Date’. I will use the ‘Format data by examples’ preview feature again, which give me the following expression:

formatDateTime(parseDateTime(split(body(‘Current_time’), ‘.’)[0]), ‘yyyy-MM-dd’, ‘en-US’)

In the next step I want to get a filtered list of the quotes where the dates in the ‘effectiveto’ column falls within the next 7 dates from the date the flow runs. Click on ‘+New step’ and search for the ‘List records’ action. The table name is ‘Quotes’ and in the ‘Filter rows’ field I used the output from the ‘Future Date’ step and the ‘Current Date’ step. You can also copy and paste the below:

effectiveto le ‘@{outputs(‘Future_Date’)}’ and effectiveto ge ‘@{outputs(‘Current_Date’)}’

The filtering shows that we are looking for quotes where the data in the ‘effectiveto’ column is less or equal (le) to the date in the ‘Future Date’ step, and the ‘effectiveto’ column is greater than or equal to (ge) the date in the ‘Current Date’ step. You’ll also notice that I am expanding the query to include the owner, so that I can put the owner name in the HTML table later. This is also the place to add sorting if that’s needed, for example you might want to sort the data descending on the ‘effectiveto’ column, in that case you would enter effectiveto desc in the sort by field.

I hope you enjoyed reading the first part of this article! Click here to read the second part of this article, where I finish the Power Automate flow! Below is the video if you prefer a different way to consume this knowledge! You can also subscribe here to get notified when new articles are published, so you’ll never miss another post!