December 20, 2024

Checking for equality is essential when using operators. For example, if we want to filter data, we usually use one or more equals operator(s) to have only the values we want. The Power Platform uses it extensively, so that’s why we’re using it as our testing platform. In our case, we’ll use Power Automate but the concepts will translate nicely to other parts of the Power Platform or even other products outside Microsoft’s ecosystem. OData is not something specific for the Power Platform, so you can learn here and then apply it anywhere.

Usage

We need to know that the equals are represented as “eq” in the OData syntax. Let’s check an example using SharePoint Lists and Power Automate.

To test it, I’ve created a list of fake people with some random data.

Let’s use Power Automate as our testing tool to fetch information using the equals operator.

Strings

Let’s say that we want to fetch everyone called “David.” We know that the column is called “First Name,” so let’s build our expression:

First_x0020_Name eq 'David'
If you’re wondering why the “x0020” it’s because URLs don’t support spaces so they need to be encoded to be able to access them. SharePoint does this encoding in the backend when you insert a column with spaces, but if we want to reference the column directly, we need to use the encoded version since it will also include the URL’s OData expression. Please note that “x0020” isn’t the “official” encoding for “space”. The official is “%20” so the one above is specific for SharePoint. 

Here’s the test Power Automate. We use a table to see the results quickly and check if our filter is working.

Here’s the result:

As we can see, we get 2 rows where the name is “David,” and as you can see, the result will return the value “First_x0020_Name,” so this way, you’re sure that you’re filtering using the correct value.

Integers or Floats

The same works for Integers of Floats. Let’s try to figure out who’s 29. To do that, here’s the expression.

Age eq 29

Using the same strategy as before:

Here’s the result:

Notice that we’re not using quotes here since we don’t need them.

How about a number column that is a “Currency” in SharePoint. SharePoint stores the value as a number, so you can tell us the same expression as before, and you’re fine.

Boolean fields

In the case of boolean comparisons, you need to use the 1 or 0 notation. 1 for true and 0 for false. Here’s the expression for the Employee column:

Employee eq 0

Don’t try to use “true” or “false” or any other notation because it won’t work. Here’s the test case:

And the result:

Limitations

The main thing to keep in mind is the name of the fields that you’re querying. Since you can use this strategy for multiple sources, you should be careful and check before the names are returned.

Pro Tip:
A nice way to check the names is to do a search and limit to one record. Then you’ll see in the result what are the names of the fields and be sure that you’re using the correct ones.

Besides that, we have a limit of URL sizes. The limit is quite high, being the max size of an URL is 2000 characters. But since we’re can have multiple components in an URL, we should be careful not to hit that limit. I’m putting this here for the sake of completion because 2000 characters is a lot. If you’re hitting this value, the OData queries are not your problem for sure.

Recommendations

Here are some things to keep in mind.

Combine with care

You can combine multiple equals operator(s) but keep them to a minimum. Keeping expressions as small as possible is a good practice. But the main reason is debugging. If something comes from the source that you don’t expect, you’ll have a hard time knowing what conditions are the correct ones, so keep things simple to debug things easily.

Always add a comment.

If you can, regardless of where you use the expressions, try to add a comment indicating what you are trying to do. Do this before you do the expression so that you think before you do. It’s a good practice to map intention with action so that, if something goes wrong, you can check either the initial information or the expression itself.

Am I missing something? Leave a comment or interact on Twitter. Let’s work together to make this reference as complete as we can

Back to the OData Reference.

Photo by Tolga Ulkan on Unsplash

Manuel Gomes

I have 18 years of experience in automation, project management, and development. In addition to that, I have been writing for this website for over 3 years now, providing readers with valuable insights and information. I hope my expertise allows me to create compelling, informative content that resonates with the audience.

View all posts by Manuel Gomes →

Leave a Reply

Your email address will not be published. Required fields are marked *

Mastodon