OData: Lower Than Operator

The lower than operator is used to filter data that fit within certain criteria, in this case, something being “lower” than another. Having this on numbers or dates is quite straightforward, but how about strings or other types?

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.

We’ve already covered the “equals operator” or “eq operator,” so if you have questions, you can check it here.

Integers or Floats

Let’s start with a simple example of all people younger than 30. To get them, we use the expression:

Age lt 25

We get the expected results.

The same for floats. For example, people that are paid less than $2000,5

Salary lt 2000.5

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

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 lt 'N'

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

But what happens if I use a lower case letter?

First_x0020_Name lt 'm'

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, or at least, ordering that you would not expect.

Dates

The “lower than operator” is handy for dates. Let’s check all items that 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 lt '@{formatDateTime(addDays(utcNow(),-1),'yyyy-MM-dd')}'

Here’s what we get:

We’re passing as an ODATA query the following:

Modified lt '2021-04-30'

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.

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” 15, for example, 15 itself won’t be displayed. Consider the value that you’re inserting the bottom value but not being considered. 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 Sharosh Rajasekher on Unsplash