For our scenario, I’ll have a set of approvers that need to acknowledge via Flow approval when files are added to a document library. After adding the “When a file is created (properties only)” trigger, I’ll add two “Initialize variable” actions. One will initialize a boolean approvalDone variable to false, and the other will set up my list of approvers. I’ll explain why I need these two variables in more detail a bit later in the post.

Next I’ll add the “Start an approval action” and hook up the inputs. I’ll take the list of approvers from my variable, and reference the link to the file that was just uploaded. 

Now that I have the approval action card configured, I need to set up the parallel branch — the actions I want to happen while the approval is in progress. I’ll hover the mouse ABOVE the “Start an approval card” and click the + sign, find “Add a parallel branch”, and select “Add an action”.

The Flow designer will now fork the execution arrow into two branches, placing the approval card on the left and bringing up the connector/action dialog on the right. This is to help us visualize that both forks will execute simultaneously. 

Before we can start adding the reminder logic on the right side of the fork, I need to add one more action on the left after the approval. Remember that we initialized a boolean approvalDone variable? After the “Start an approval” action, I’ll add a “Set variable” to update this variable to true — this will be used to “signal” to the other branch that the approval is completed, and no more notification reminders need to be sent. (Without doing this, we might continue to send mails indefinitely!)

Now we can focus on the reminder aspect. I don’t want to remind the approvers as soon as the approval is created (they have the Flow actionable approval email for that), so I’ll add a “Delay” action to give them a whole day to approve. Once the day is up, I now want to loop until the approval is complete. I’ll use “Do Until”, where the exit condition is the approvalDone variable becoming true. The variable will be checked at the start of every loop iteration, and once the approval is complete and variable set to true, it won’t execute another iteration. (Note that based on how long you expect the approval to be active, and how often you want to send reminders, you may need to adjust the limits on the “Do Until” to make sure it gives you enough retries). 

The first action we’ll do in the loop is to send the email, so I’ve set up a basic “Send an email” using Office 365 that includes the link to the file and a friendly reminder message. I can use the approversList variable to make sure that the email is sent to the same set of people to which the approval is assigned. After the email is sent, I want to wait again before sending it again. I’ll Delay for another day, but you could just as easily do something custom — send more frequent reminders after a while, cc their manager after some number of reminders, check if the day is a holiday/weekend, etc.)

Because the loop will check the condition before executing, if the approvers respond during any of the “Delay” actions, the condition will be checked again before sending either the first or subsequent reminder emails. This avoids sending reminders for approvals that are already complete.

If you wish to play around with this technique, I exported the sample flow so you can import it into your environment. It can be downloaded here. 

Parallel branches are a powerful approach to extend a single Flow with business logic/behavior that needs to run concurrently, without splitting logic across multiple Flows. 

The post Intermediate | Flow of the week: Approval reminders using parallel branches appeared first on Microsoft Power Platform Blog.

The example scenario is as follows: Let’s say that you have a Flow that will send short messages to customers, but you want the subject, body, etc. to be localized in the customer’s preferred language. One solution would be to have one Flow for each locale, and each would be customized to a language. The user (or logic) that triggers the process must figure out the right language for each customer, and then run the right Flow. That’s a.) a nightmare to maintain (propagating changes to each copy, etc.) and b.) no fun, and not blog-worthy. Instead, I’ll show you a technique that will let you maintain one simple Flow that easily scales to the addition of new localized messages, new languages, and provides a single place for the logic to be defined/updated in case of requirement changes.

To set the scene, I have set up a simple SharePoint list that will act as my data source — a list of customer contacts with email address and the preferred language:

I’ll use the “For a selected item” trigger to kick off this Flow for a specific list item at a time. After the trigger, I’ll fetch the full list item with a Get item action to fetch the customer contact email address and language column value. 

One way of solving this would be to switch on the language, and then initialize a variable for each string that I want to use in the email. Then I could embed references to each variable as needed in the Send an email action. The downsides of this approach are:

1. ​​Adding a new language means extending the switch statement with a new case statement. More languages mean a more difficult flow to read and maintain (and not to mention scroll through). Headache.

2. Adding a new localized string means going to each and every case statement in the switch and updating them. More updates, more chances of typos/errors. Also a headache.

Instead, we can use an object variable to define our “lookup table” that will let us map all languages to a set of strings in a single action. Then we can use a Designer expression to read from the object to select a set of strings all at once. The JSON data I will use in the action looks like the following: 

{
    "en-US": {
        "greeting": "Hello",
        "message": "Thank you"
    },
    "fr-FR": {
        "greeting": "Bonjour",
        "message": "Merci"
    },
    "de-DE": {
        "greeting": "Hallo",
        "message": "Danke"
    }
}

After the table itself is initialized, I can initialize a new variable to store the set of strings to use. I’ll use the SharePoint column value to choose the right set of strings to use in the email. I’ll use the expression editer to enter my expression that will read from the body (output) of the Get item action, take the Language column value, and access the corresponding table property:

variables('localizedStringTable')?[body('Get_item')?['Language']]

Let’s break this expression apart and build it back up to see what’s going on:

• variables('localizedStringTable') — this references to table object variable we just initialized
• variables('localizedStringTable')?[ ... ] — this reads a property off of the variable object. The “?” signals that the property might not be found. We’ll pass in the language value into the [ … ] to look up the property (another object), corresponding to the language.
• ​body('Get_item)?['Language'] — this reads the Language property off of the output object of the Get item action.

​When this expression is evaluated as part of the variable initialization, it will grab the sub-object off of the table variable and assign it. Now we have one object (with properties like greeting and message) with all the right strings in one place.

But what if the Language for the customer is not found in our table of languages and strings? Right now the flow will fail (or worst case, just send an empty email!). Can we somehow fallback to a default set of strings? Can we do it elegantly? 

First we’ll add to the JSON definition to define a “default” set of strings (in this case we’ll duplicate the English strings):

“default”: {
    “greeting”: “Hello”,
    “message”: “Thank you”
}

If the table doesn’t contain the user’s preferred language, we should read this default set of strings, ensuring that we always populate an email. We could first try to read the language from the table (or use a contains expression to check before reading), but it is simpler to use coalesce. The coalesce function takes a dynamic set of parameters, and returns the first (in the left-to-right sense), parameter that is non-null. We’ll pass in the above variable reference expression as the first parameter — if it is found (non-null), coalesce will return it. If it is not found, coalesce will check the second parameter, which will be our “default” lookup:

coalesce(variables('localizedStringTable')?[body('Get_item')?['Language']], variables('localizedStringTable')['default'])

Now that we have an object with all of our necessary strings selected, we can compose the email. In various locations within the Send an email card, we can embed expressions to reference the message and greeting properties:

• variables('localizedStrings')?['message']
• variables('localizedStrings')?['greeting']

The post Advanced | Flow of the Week: Localize messages used in Flows using JSON object variables appeared first on Microsoft Power Platform Blog.

I started from the basic template linked above, so I won’t focus too much on the trigger and actions to save the event. For now, I want to implement the following criteria for syncing the event between calendars:

• The subject contains “MSSL” (Microsoft Soccer League!). I always want my soccer games sent to my personal calendar.
• The event starts at or before 10am
• The event starts at or after 5pm
• The body of the event contains a special identifier (“@@HOME”) that I can use to force an event to be synced even if it doesn’t match any of the other criteria.

To start, the events come through the Office 365 connector in the UTC timezone, but I want to reference them in my local time zone. I will use the “Convert time zone” action to do this. (This action is documented in more detail here). Because my criteria above only cares about the time of the event, I will use a custom format string “HHmm” to output the hour and minute of the start time in a form that can be parsed by int(). For example, an event starting at 6:30pm would be output as 1830. 

The basic “Condition” designer card only supports a single condition, not a complex AND or OR. In order to combine our above criteria, we will need to switch it into Advanced mode to enter a full WDL expression. Instead of authoring the full condition expression by hand, I’ll add each individual criteria as its own condition, and then use the Advanced editor to copy the expressions out. The condition branches don’t need to be hooked up to anything yet, since I will remove them once extracting the expressions. Here’s the condition I added for one of the time checks:

And here’s the advanced editor view of the same Condition:

We end up with the following expressions after adding all conditions and copying out of the advanced mode:

• @contains(triggerBody()?['Body'], '@@HOME')
• @contains(triggerBody()?['Subject'], 'MSSL')
• @lessOrEquals(int(body('Convert_time_zone')), 1000)
• @greaterOrEquals(int(body('Convert_time_zone')), 1700)

We’ll use the WDL or() function to combine these expressions. This function takes two expressions, and returns true if either or both are true. We’ll need to nest the expressions through multiple or()s since we have more than two expressions. Note that only the outer or() needs to be prefixed with the @ character.

@or(
    contains(triggerBody()?['Body'], '@@HOME'),
    or(
        contains(triggerBody()?['Subject'], 'MSSL'),
        or(
            lessOrEquals(int(body('Convert_time_zone')), 1000),
            greaterOrEquals(int(body('Convert_time_zone')), 1700)
        )
    )
)

Note: I’ve written it across multiple lines for readability and easier matching of parenthesis, but it will ultimately need to be entered in without line breaks like so:

@or(contains(triggerBody()?['Body'], '@@HOME'), or(contains(triggerBody()?['Subject'], 'MSSL'), or(lessOrEquals(int(body('Convert_time_zone')), 1000), greaterOrEquals(int(body('Convert_time_zone')), 1700))))

On the “If yes” branch of the single condition, I’ll use the Create an event action to sync the event to my personal calendar. Because I have a similar Flow set up to sync personal events to my work calendar, I need to take some extra steps to avoid an infinite loop caused by one Flow creating an event on a calendar that triggers a different Flow, syncing it back and forth. To do this I’ll add a special string in the body of the new event: Synced with Flow! 

I’ve added another condition right after the trigger to skip any event that contains that string. I could embed this with an and() around the existing expression that we created above, but I’ll show another technique — using the Terminate action to stop a Flow run. By using Terminate in the “If yes” branch, I don’t need to embed the remainder of the flow inside the “If no” branch. This technique can be used to keep the level of nested conditions to a reasonable level. 

That’s all it takes! With complex expressions and the Terminate action, you can reduce the number of redundant actions, simplify conditions, and eliminate levels of actions nested below the Yes/No branches of Conditions. 

The post Advanced | Flow of the Week: Selective calendar event sync using Condition advanced editor mode and Boolean expressions appeared first on Microsoft Power Platform Blog.

– Iterate over a collection of SharePoint items and track a running total of one of the list’s fields using the Initialize Variable and Increment Variable actions. 

– Track the SharePoint list item that has the maximum value for one of the list’s fields using the Initialize Variable and Set Variable actions.

– Store objects inside a variable and reference their properties in expressions.

Here is the list that I have set up on SharePoint. My goals will be to calculate a sum of the Capacity field across all items, and track which city has the highest Capacity. 

To start, I will set up a When an item is created or modified trigger so that my Flow runs when I edit or add a new item. I will also add a Get items action because my flow needs access to the full set of properties on each item.

The basic algorithm for tracking the sum and maximum will be:

1. Initialize an integer variable to 0.
2. Initialize an object variable with some simple JSON to track the list item that has the highest Capacity
3. Loop over each item
   • Increment the variable tracking the sum of capacities.
   • Check the current item’s capacity value against the current highest-capacity item. If the current item’s value is greater than the stored item, replace the stored item

Initializing the integer is easy, I only need to add an Initialize Variable action, select “Integer” from the type menu, and set the initial value to 0.

Initializing the object is only slightly more complex. I added another Initialize Variable action. Object variables in Flow can be represented using JSON (JavaScript Object Notation), a common format for representing objects and their properties. The objects fetched from SharePoint Get items are conveniently also represented as JSON, so we need to initialize our variable to something that “looks” similar. I will assign a default Title property and an initial Capacity of -1. (This simplifies the Apply to each action, as we will see shortly.) I could use two variables to track the maximum capacity and the title/ID of the item with the maximum value, but where’s the fun in that?  I’d also have to potentially re-query the item from SharePoint if I only stored the ID.

Now for the iteration — I can select the value output of the Get items to process each list item in a loop. As I mentioned above, I first want to increment the SumOfAllCapacity integer variable. I can do that with an expression that references the current item’s Capacity property. (As the field type from SharePoint is actually decimal number, I need to convert it to a whole number using int()). 

Okay, the summation is handled, but now for tracking the maximum. I can add a Condition inside the Apply to each that will compare the current item’s capacity against the Capacity property of our object variable. If it is greater than the current stored value (the If yes fork of the Condition), I will use Set Variable to overwrite the stored object with the current Sharepoint list item. If no, I will leave the variable alone, as the current stored object has a Capacity greater than the current item. Remember how I initialized the Capacity property to -1 above? This allows the check against the stored value to succeed the first time through the loop (assuming our Capacity list field on SharePoint restricts to values >= 0). If I didn’t initialize to a value, the Flow would have to be more complex to handle the first item comparison. 

That’s it! I’ve added a simple Send an email action to send me the results. I’ve embedded the SumOfAllCapacity variable to show the total sum of all list items, and the item link, Title, and Capacity fields from the item stored in our object variable at the end of the iteration. A more real-life example might use the results to send a nicer report, Start an Approval to authorize rebalancing capacity elsewhere, etc… The Send an email action goes outside of the loop, since we only want to execute it once the sum and maximum have been calculated. I’ve added some incredibly simple HTML tags to allow me to link to the SharePoint item directly. Here you can see how I reference the Capacity, Title, and {Link} properties of our MaxCapacity complex object with the following syntax in the Expression window: variables('MaxCapacity')['Capacity'].

Let’s take a look at the Flow Run history for this flow when it ran against my list. There were 3 items at the time of the change that triggered the flow: Seattle with 15, Redmond with 5, and Bellevue with 28. As expected, the Apply to each ran 3 times, and we can see the SumOfAllCapacity variable incrementing appropriately. Here’s iteration 3 where we add the 3rd item (Bellevue, 28) to achieve the correct answer of 48.

The run history also shows us that on iteration 1 and 3 we take the If yes branch of the condition, but on the second we didn’t. That is because on the first pass we overwrite the -1 initial value with the Seattle record (15 > -1), on the second pass Redmond’s capacity of 5 is less than 15, and on the third and final item, Bellevue’s 28 causes us to replace Seattle.

As expected, the email arrives with the expected results:

That’s it! As always, please let us know if you have any questions, suggestions, or feedback about Flow. Feel free to post below in the comments, on the Flow Community page, or on Twitter.

The post Advanced | Flow of The Week: Calculate running totals and tracking maximum values in Flow appeared first on Microsoft Power Platform Blog.