SO HERE'S A STRANGE THING. I was working on a scheduled Flow that would loop through the items in a list and send out alerts to groups of Owners and Responders when a task was close to its deadline. Sounds straightforward, right? Being fairly new to Flow, I certainly thought so, and even went as far as to trust Microsoft's recommendations as I was building my Flow application. But it turns out that there were "unexpected benefits" along the way. Notably, all the Owners were receiving duplicate notifications from all the tasks - not what I intended and definitely not what the Owners wanted.
|Here's a screengrab of the list. You can see that I've added a couple of Owners and a couple of Responders for each task. I want each Responder to receive a notification and all appropriate Owners to be CCed on each notification.|
Right, so with my List all ready and populated with Tasks, I set to creating the Flow that would scan the List once a week, then send notifications to all named stakeholders as a task fell due.
|I want my Flow to run once a week, and notify all Responders that the deadline for their task is drawing close.|
|Let's have the Flow run on Wednesday at 13.00.|
|Pretty basic stuff, right ... create a Flow, add a Get Items action. Don't get too comfortable. It starts getting more challenging from here on.|
|Add a condition. We'll need more than one condition, but I'm getting to that.|
|The value we're going to filter on first is the Deadline. As soon as we add that value in the left-hand field, we automatically get an Apply to each wrapper ...|
We set the first parameter to the following formula, like this:
This means take today's date and subtract seven days. We add the formula as an Expression to the condition, saying that if the Task's Deadline is Greater to or Equal to the above value, then act.
|Apply the filter value for Deadline as a calculated Expression ...|
You could also add a "+" in front of the 7, but it's not necessary. Add the parameter, like this:
|Having two Deadline filter values is how we bracket a two week window. Adjust the number of days to change the beginning and end of the trigger time period.|
|Finally, we need a filter value that prevents Task already marked as Complete from triggering a Flow response.|
|And there we are ... all the Condition filters applied.|
I did that by adding a Send Email action to the "If yes" branch of the Condition. But the minute I add the Responder data to the "To" field, helpful ol' Microsoft added an enclosing Apply to each function.
|I was beginning to see a pattern emerging. Every time I added a value from the list, Flow added an Apply to each wrapper ... which I assumed was necessary.|
And when I tested the Flow at this stage, each of the Responders receives an email so, again, that's good and what I intended. But it was when I added the Owners as a CC to the email that the problems started.
Go to the Send an email action and click the link at the bottom of the panel that says, "Show advanced options", and add a dynamic value for the Owners into the CC field.
|I wanted the add the Owners on the notification as a CC, as they didn't have to do anything, except be aware that the notification had gone to the Responders.|
What happens is that the Flow creates several emails for each task as it loops through the Responders and the Owners ... so for Responders A & B and Owners C & D, you get four emails for the same task, like this:
- Responder A + Owner C
- Responder A + Owner D
- Responder B + Owner C
- Responder B + Owner D
... meaning that The Responder is getting two emails for each task and the Owners are getting two emails for each task. And if you had three Owners and Three Responders, then there would nine emails going out, and so on. We definitely don't what that to be happening. We're going to need a different approach.
I don't mind each Responder getting their own email for a given task, but I don't want that to be multiplied by each Owner also getting an email. So what if we roll up all the Owners for a given task into a single string of email addresses? This is a job for Variables ...
First, we have to declare a Variable for Owner Email Addresses. And that has to be done that outside the Apply to each action, like this:
|I added the Initialize variable action after the Get items action, as it has to be listed in the main section of the Flow, outside of any Conditions or Apply to each actions.|
Add the action Initialise variable. The Variable Type is String. Leave the Value field blank. Now, I needed to rearrange the way the Apply to each actions were nested, so I unpicked them (by removing any dependent values in the enclosed Actions) and laid them out like this:
|To rearrange the Apply to each actions, you first need to remove any dynamic values in the enclosed actions. Then you can drag the Apply to each actions to the correct positions and re-instate any dynamic values in the child actions.|
... adding the Append to string variable action that will compile a string of Owners' email addresses for each email as the Flow loops through Apply to each 3.
Really important - do not forget to add the semi-colon after the Owners Email value in the Value field. This is to ensure the string of email addresses are separated correctly.
When we run this Flow, a curious thing happens. All the Owners' email addresses in the group of tasks that qualify under the filter are compiled into an ever-growing string ...
... until by the end of the cycle, all Owners are CCed into the last notification.
I tried a few things to fix this - including changing the order of the Apply to each actions - but nothing seemed to work and I wasn't able to find a solution via Google.
The next day, I had a flash of inspiration ... I added another Set variable action before the Apply to each 3 (that appends and holds the Owner emails), and configured it to give the variable a value of "null". This would clear the Owner variable after each notification is sent, preventing a build-up of Owner email addresses in the CC field of the notification.
|And that seemed to do the trick. No longer were Owner email addresses from previous notifications being copied into subsequent notifications.|
And when I ran a test, there was no more build-up of emails and, as I'd wanted, there was one notification for each Responder with all appropriate Owners for that task CCed on each of the notifications.
There may well be a better way of doing this, but I'm just happy I got a solution that works.
SOME FINAL THOUGHTS
Because Flow can sometimes be a bit difficult to wrangle, here's some extra hints and tips to make this solution better.
Sometimes, when I add an email value to a notification it displays strangely. For example in the below image, you can see that the salutation of the notification displays as "Owners DisplayName" ... but I had actually added the Responders DisplayName, and the notification does show the Responders name on arrival. I don't lose much sleep over this, but I just wondered if anyone else sees this glitch when they use Flow.
|Here's what the final Send an email action in Flow should look like. I had put in the Owner and Responder email values for testing purposes, so I could see what Flow was writing into the emails without actually having to send the emails.|
Next, if you just put the "Deadline" in the body of the notification, the date displays backwards in the final delivered email, like this: 2021/04/14. To get it to display the right way round and in the right format, add the Deadline as an Expression, like this:
You can change the "dd/MM/yyyy" bit to a format that suits you, if you prefer.
You can also see, for testing purposes, I had the Responders Email (mislabelled by Flow here as "Owners Email") and the Owners variable in the body of the notification. This saves me peppering my colleagues with emails while I'm testing. However, it's tricky to add the Responders Email value directly into the body of the notification - Flow doesn't offer me that option. So what I did was first add the Responders Email value as an expression, like this:
On saving, Flow converted it to an actual dynamic value (though it still managed to mislabel it as "Owners Email").
I hope this has helped someone ...