Microsoft Flow for Expiration Dates pt. 2

In the previous Flow post, I walked through using Flow to set up reminders for things you need to keep track of, like expiration dates, renewal dates and so forth. In this follow up, I’ll walk through creating a weekly process that emails you a list of  upcoming reminders so you know what’s coming in the future.

Trigger

We’ll start by creating a blank Flow. For our Trigger, we’re going to select a Schedule. Search for “Recurrence” and select that. For this example, I’m going to set the trigger to run on Sunday evenings at 6pm local time. That’s the time I’m starting to think about the coming week, so it’s a good time to remind myself of what’s coming up. For my Flow, I’ve selected the following parameters:

Collect Our Reminders

Next we need to collect our upcoming reminders. If you go back to my earlier post on creating an “in case you missed it” Flow the hard way, we’ll use a simplified version of that same process for collecting data from our Azure data table.

First, we’re going to need a variable to hold our start date. Call it whatever you like and set its type to “String”. For this example, I want to know everything that’s coming up in the next 6 weeks. So I’m going to set the Value to the following expression:

string(addDays(utcNow(), 42))

This creates a date value 6 weeks from to use for our comparison. This gives us our end date.

We’ll also need a variable to store today’s date. This will give us our start date. So create a second string variable and set its value to the following expression:

string(utcNow())

Next let’s add our code to get our Azure table entities. Add a new step and search for “Get entities”. Select the “Azure Table Storage” version. From the Table dropdown, select the reminders table you created in the previous Flow.

For the “Filter Query” parameter, enter “expirationDate le ‘” and then click on your end date variable from the previous step. After that add “’ and expirationDate ge ‘” and then click on your variable for today’s date. At the end add a single quote ‘. Make sure that you have placed a single quote ’ before and after each of the two variables. If you don’t, the “Get entities” query will fail. This will retrieve all entries where the reminder date is between today and six weeks from today. It should look something like:

If you named the date column something else, make sure you use that instead of “expirationDate” above.

Generate Our Email

The last thing we need to do is to generate our email body to be sent. Back up at the top, create a new string variable and name it something like emailBody. Give it a starting value that will be your email introduction. I’m using something to the effect of:

You have the following upcoming reminder and expiration dates:<br/><br/>

Now, back down to the bottom, add an “Apply to each” action. Select the output from the “Get entities” step as the desired output source. Inside the action, we’re going to use the “Append to string variable” action. Click the plus sign inside the “Apply to each” box and search for “Append to string variable”. Select your email body variable as the name and in the Value place the following expression:

concat(items('Apply_to_each')?['expirationDate'], ' -- ', items('Apply_to_each')?['name'], ' --- <a href="', items('Apply_to_each')?['website'], '">more info</a><br/>' )

This will append the details of each entry to the current emailBody value.

(Thanks to Calvin for pointing me at the Append function!)

The last step is the email. At the bottom, after the Apply to each loop, add a new step. Select “Send an email (V2)” as the action and enter a destination email address and subject line. In the body field, add the emailBody variable.

Fin

That’s it! You’re all set to get a weekly email reminding you of what’s coming up. Now, I’m sure you’re saying to yourself that there’s one big glaring issue. And you’re right. When you get your first email with a couple of reminders in it, you may notice that the dates are not in order.

And you’re right. They aren’t. They’ll be in the order that you entered them into the table. Why? Because of yet another hole in the Flow feature set: Sorting data returned from Azure Table Storage. It doesn’t exist in the Get entities action and there’s no way I’ve found to sort data once it’s returned.

Full Flow