water flowing

Microsoft Flow and Azure Logic Apps Quick Formula Expression Guide

Microsoft Flow and Azure Logic Apps are powerful tools for automation, with tons of connectors and the things that you need to do work. Microsoft Flow in particular makes the design of a workflow easy with a rich design experience – except when it comes to expressions. You’re expected to know some relatively arcane formula expression rules to make your steps work together. I’ve gathered up the things that I felt like are the most important for writing expressions into this quick guide. Let’s get started with some basic math.

Arithmetic

If you want to get tripped up in a hurry, try to write your basic math statements with the operators that we’ve come to know and love: + – * /. They’ll make your expression fail – unless you swap them for their function call equivalents:

+ add()
sub()
* mul()
/ div()

The parameters to each of the functions occur in the order you would expect. Thus, you can swap:

A*B/C+D with…

add(div(mul(variables(‘A’), variables(‘B’)), variables(‘C’), variables(‘D’))

It’s not super easy to read, but once you get the pattern, you can create even more complex statements. For now, ignore the variables() function, we’ll come back to that in a bit.

Nulls

With basic math out of the way, next it’s important to know how to deal with nulls. Dealing with nulls is two pieces. First, separating a property with a question mark (?) instead of a period (.) will automatically handle nulls for you – return a null in the end. This greatly reduces the amount of testing that you must do to ensure that you don’t accidentally reference a null value.

The corollary to this is what do you do to get a default when a value is null. Here we use the manipulation function coalesce(), which returns the first non-null object. So, if I were to do:

coalesce(variables(‘A’), variables(‘B’), ‘Default’)

I’d get A if it is non-null. If A is null, I’ll get B if it’s non-null. If both A and B are null, this will return the string ‘Default’. If you’re wanting to get a zero instead of a null, you can wrap the result in a float() or int() conversion, which will return zero when the value is null.

One Thing About Strings

There are many string functions, but one that’s particularly useful and necessary. That’s the concat() – short for “concatenate” – function. It takes two or more parameters and concatenates them into one large string. If I wanted to create an ODATA compatible filter, I might write:

concat(‘SalesProID eq ‘, variables(‘A’))

If I needed to put a single quote around the values, I’d use the escape syntax, which is two single quotes together. If I wanted to filter by OrderType, the command might look something like:

Concat(‘OrderType eq ”’, variables(‘A’), ””)

That provides a string that has the quotes needed around the value.

Getting Values

That’s good basics, but how do you get the values from other parts of your Flow into your equation? You’ve already seen one approach. That is to use variables() to fetch one of the variables you’ve defined in your flow. To do this, you simply include the variable you want to use in single quotes like this:

variables(‘MyVariableNameHere’)

This will return the value of the variable. However, it’s more likely that you want to get a variable from another step. Here, we start with the values from the trigger that are fetched with triggerBody(). If I were fetching the ID of the SharePoint item that triggered the flow (assuming that’s our trigger), I’d write:

triggerBody()?[‘ID’]

This would return the ID of the item or null. If I had another step that fetched a different item, or had some other output, I could use the body() function with the parameter, which is the name of the step. So, if the step was named “Quote” and I wanted the symbol property, I could write:

body(‘Quote’)?[‘Symbol’]

There’s one caveat here. If the step has spaces in its name, then you must replace those with underscores. To get the Quote property from the Quote of the Day step, you’d write:

body(‘Quote_of_the_Day’)?[‘Quote’]

One other common condition is when you’re working on a set of items in a For Each step. If you’re in a For Each step, you can get the values from the current item with item(). If I wanted to get the ID of the current item, I’d write:

item()?[‘ID’]

If you’re inside nested For Each you can refer to the specific item, you want to pull from by using the function items() and specifying the name of the For Each. If I had a name of a For Each Step called ‘Each Item’ inside of a For Each Step called ‘Each Order’, I could write an expression to get the order number and item number in a string like this:

concat(‘Order Number :’, items(‘Each_Order’)?[‘OrderNumber’], ‘ Item Number :’, items(‘Each_Item’)?[‘ItemNumber’])

If you want to see the full list of functions, you can go to https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-workflow-definition-language — for warning, there are lots of methods listed there, but you’ll have to search for exactly what you’re looking for.