Celebrating anniversaries with Power Automate
A very common requirement is to remind people about anniversaries. In a business this could take the form of birthdays or commencement dates. It could, however, just as easily be any sort of event that happens on a certain date.
Previously, I’ve shown how to:
However, in this case, instead of simply rotating through a list of posts we want to match today’s date to a date on a list and then broadcast the message that corresponds to that date entry.
The starting point for this process is to create a reference list containing the dates and details you wish to share. I recommend that easiest place to do this is in a SharePoint list, as shown above. Of course, this list can contain as much detail and additional columns as you wish, but for this, I’ll keep it simple and just have two fields. It is important that you have at least one column (here Dateoption) that refers to the current year in which that item will be displayed.
For simplicity, I have also configured my date column in the SharePoint list to exclude time and display in standard format as shown above. There is nothing stopping you using Date & Time if you wish, it just makes the filtering a little more complex later in this process.
You’ll then want to create a Power Automate Flow that looks like this:
It all starts with a Recurrence action that will trigger this process once a day like so:
If you select the Show advanced options in this action like so:
You can set an exact time when this Recurrence action will be triggered (say 10am). However, since this example is a daily anniversary, we only need to trigger it at any time during the day.
We now need our process to determine what the current date is and we can do this using the Current time action as shown above.
Next, add the Convert time zone action. There are two reasons for adding this action. Firstly, the Current time action returns today’s date in UTC which may cause issues if you are not in that time zone like me. Thus, I want the current time BUT I want it as a local value (i.e. to reflect the actual time in Sydney, Australia), thus the Source and Destination time zone field settings.
The second reason for the Convert time zone action is so the time value is in the right format for a comparison test later on in the process. Thus, the Format string field should be set to yyyy-MM-dd as shown.
Now, I need to add the Get items action to actually go and look at what is in my SharePoint list.
In this action I enter the Site Address and List name, however I also expand the advanced options to reveal the Filter Query field as shown.
The Filter Query field will limit the items returned by this action to only those that match the filter. Thus, I want the returned items to only be those that match today’s date, which I have correctly formatted and stored in the Converted time action result. Thus, I want to compare the date field from my SharePoint list (here Dateoption) to the Converted time result. It is important to note that I have enclosed Converted time result in single quotes (‘) to convert the value to a string for comparison. It is very important that you do that, otherwise you’ll get errors when your Flow runs.
With the values that the Get Items action returns you’ll need to perform a number of steps. For this you use the Apply to each action as shown above.
In the case of this example, I’m simply going to post the text from the Title field in the SharePoint list that matches today’s date into a chat message as shown above. Again, this action could be anything you want, in fact, I’ll talk about how I use this with Twitter later on. For now the expectation is that if there is a match in the SharePoint list for today’s date, then the text for that entry will appear in a Teams chat message.
We need to do one more thing before we are finished here. As this is an anniversary calendar, we want to increment the current item for today and have it reoccur on the same date next year. To do that we use the Add to time action as shown by adding 12 months to the result date we have determined as shown above.
Before the new date can be added back to the SharePoint list it needs to be formatted correctly. This is achieved using the Compose action as shown using the following expression:
You’ll notice that that date format yyyy-MM-dd is the same as the one we set in Convert time zone action earlier.
All that remains is to use the Update item action to update the item in the SharePoint list with the new date entry just composed. As shown above, the same SharePoint site and list is selected, along with the item ID and Title but the Dateoption field is set to contain our new formatted date output from the previous action.
You can now save your Flow and run a manual test.
If I look at the chat in my Team I see the expected message that matches the item Title field in the original SharePoint list.
Also looking at my original SharePoint list I see that the date of today’s item has been incremented twelve months as shown.
One of the ways that I use this process with Twitter is to regularly post anniversary dates around ANZAC participation from World War One, which are taken from my site ANZACS in France.
The idea is that that the Flow checks this list of dates and then tweets out the text in the Title field if there is a match. Then it increments the PostDate field twelve months ready for next year. You’ll also see that I have added another custom column that records the original date of action just so I can filter and sort easily. Feel free to follow @ANZACSIF to be reminded of these dates.
As I initially mentioned, I believe there are plenty of applications for this type of process in a business. The most commons ones I would suggest are for staff birthday and anniversary reminders. The great thing is that with Power Automate it is easy to modify this process to suit whatever need to have. It also makes it easy to edit the events and more if you need to because all you need to do is modify the SharePoint list that this process uses.
The possibilities are endless thanks to the Power Platform.
Originally published at http://blog.ciaops.com on January 3, 2022.