Power Automate: Field and formula common mistakes

Today I want to focus on the fundamentals, especially on an issue that I see a lot regarding dynamic fields and formulas. So let’s explore two of the most common mistakes when it comes to dynamic fields and formulas, why they happened and how to avoid them.

After you understand the difference, you’ll never make the same mistake again.

Formula vs. Text

Let’s start with a simple example. We have a variable, and we want to check if it contains data or not. For that, we’ll use the length function with the following formula:

length(variables('TEST'))

First, let’s insert the formula in the “dynamic fields” and see what happens.

But since we know the formula is correct, we could copy and paste it directly into the field.

Let’s test both and see what happens. Here’s the Flow:

Here’s the result.

That’s not quite what we’re expecting. But, on the other hand, the formula is the same in both cases, so why is there a difference?

The reason is simple. In the first case, we added the formula to the “dynamic field,” indicating to Power Automate that we are providing a formula. Power Automate will then analyze it and evaluate it when it runs.

But if you provide the formula directly in the field, Power Automate will consider it a random set of characters and compare it directly. So, in the second case, we’re not checking the length of the variable; we’re comparing if the characters “length(variables(‘TEST’))” and equal to zero. Since they are not, we get “false”.

Power Automate does an excellent job highlighting the difference between a string of characters and a formula so be sure to check if the values are in “purple” (formula) and not grey (a string of characters).

I recommend that you double-check your Flows and see if you have any instances of this. It looks basic after you understand it, but I see this happening a lot.

Copy and paste fields.

The second of the list of common mistakes is when you copy and paste fields. For example, let’s say that we have a value in a field, and you want to build a formula with it.

Since we’re copying the value to the letter, the formula should be ok, right? So if that’s the case, why do we get:

To understand that, we need to look closer at what we’re pasting:

@{variables('TEST')}

In the formula above, we only needed to use the variables('TEST') .

The reason is simple. Power Automate is copying it as a “field” and not as a “sequence of characters” when we select the field. So Microsoft decided to copy it  @{...} so that it will understand that it is a field when you press paste. To understand, let’s copy and paste it to a field and see what happens:

Looks good, right? When we copy and paste it to another field because Power Automate added @{}, we know we’re copying a reference to the variable. If we copy it without it,  @{} this will happen.

The difference is clear. We’re now copying a string of characters.

Going back, why does the formula fail? Because when Power Automate evaluates the formula, it doesn’t know what is @{}. So it will fail. You can see this when you pick the value from the list:

As you can see, when you pick, the value is inserted without the @{}. This is used in the UI to make it easier to copy and paste references to values. When you paste something with @{} Power Automate will know that it’s something special that should be parsed differently.

There is a difference between variables('TEST') and @{variables('TEST')}. Think of the first as “I want the variable’s value” and the second as “I have a reference to that variable to use in the UI”.

It’s a good practice to copy and paste a value like the above into a formula. You’ll always be sure that the reference is correct and that you don’t make a mistake trying to write the formula, but don’t forget to remove the @{}. The UI only uses them to understand that the value being pasted is special and should not be taken literally.

Final thoughts

These are two of the most common mistakes, and once you understand why they happened and what’s behind them, I guarantee that you’ll never make them again.

Have a suggestion of your own or disagree with something I said? Leave a comment or interact on Twitter and be sure to check out other Power Automate-related articles here.

Photo by Varvara Grabova on Unsplash

Manuel Gomes

I'm a Project Manager with experience in large projects and companies. I've worked in the past for companies like Bayer, Sybase (now SAP) and I'm currently working for Pestana Hotel Group.

View all posts by Manuel Gomes →

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: