OData: Lower Than or Equals Operator

The “lower than or equals” or “le” operator helps us filtering the data based on criteria of something being “lower” than another. We’ve covered the “lower than” in the past, but this one has a small difference. The value that you include is also provided in the data that is returned.

As always, we’ll use Power Automate and SharePoint to test, but the concepts can be applied in other areas, even outside Microsoft’s platform.

Usage

To test and demonstrate the syntax, we’ll use a list of random data with fake people.

Let’s check how it works in multiply data types.

Integers or Floats

Let’s start with a simple example of all people below 25. To that, we use the expression:

Age le 25

I ordered the results so that we can see that the “25” is included.

We get the expected results.

The same for floats. For example, people that are paid more than $3,047

Salary le 3047

For the counties or locales that represent decimal points with “,” you need to use “.” in your operators; otherwise, you’ll get an error.

Here’s the result:

As you can see, the number 3047 is included in the set of values returned. Nothing too exciting. Let’s check something a little more complex.

Strings

Strings are a good case for this kind of validation. There are situations where it’s useful to sort a name and check the correct “order” for it. For this, you should use the “Order By” section, but I want to alert you to some cases.

What happens if I use the following expression:

First_x0020_Name le 'B'

You’ll get all names that have the first letter in the alphabet before ’B”.

But what happens if I use a lower case letter?

First_x0020_Name le 'b'

You get the same result. Please don’t jump to conclusions by thinking that the comparison is case-insensitive. In this case, it works because all names are upper-case, but if you have special characters, for example, you may encounter some strange cases.

I want you to notice something interesting with the data. We want every name below the letter “B.” One would think that the names with B like “Brad” would be included since we mentioned that the “lower or equals” operator would include all values, including the value we define. But “Brad” is upper than “B” because of all the other characters that come after the “B.” So if you want to get all names that start with A and B, you need to define them as follows:

First_x0020_Name le 'C'

It’s counterintuitive, but B is the limit, not everything with B and something else.

Dates

The “lower than or equals” operator is handy for dates. Let’s check all items that were updated since yesterday. To do that, we need to use the addDays function and the formatDateTime function to format the date, so it’s comparable.

Modified le '@{formatDateTime(addDays(utcNow(),-1),'yyyy-MM-dd')}'

Here’s what we get:

We’re passing as an ODATA query the following:

Modified le '2021-05-14'

The formula that contains the format date-time is to build that date dynamically before sending the request. Also, keep in mind that the “@{“ and the “}” are only here so that you copy and paste it to the field; if you’re building it in the expression field, copy the text above without those characters.

Let’s start with a simple example:

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.

Here’s how to be sure that the value that you’re importing is correct.

You can then copy and paste the names.

URL size

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.

Use it only for numbers or dates.

There’s really no reason to use it outside these two cases. There may be edge cases for all the other types, but be aware when you’re trying to use them. You will have to consider other constraints that you may not be aware of from the beginning. For example, if you want to sort, don’t do it manually.

Know what to expect

Not a limitation but something you need to understand. If you’re searching for values “lower than or equals” 15, for example, 15 itself will be displayed. Consider the value that you’re inserting the bottom value that you want to include. Many people make this mistake, and then some data is left out because of that.

Name it correctly

Naming actions is super important so that you know what you’re doing. Don’t leave the action with the default “Get items” value. You won’t get any value from it. If you’re using something else that Power Automate, please name your functions or equivalent with something meaningful. It will save you a lot of time later in debugging.

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. Also, when you’re scanning your Flow to understand what it’s doing, having a comment will help you understand what is being done without looking at the expression. If you’re not familiar with the SharePoint List structure, for example, you’ll at least know what is being done.

Back to the OData Reference.

Photo by Digital Front on Unsplash