by The “greater than or equals” or “ge” operator helps us filtering the data based on the criteria of something being “greater” than another. We’ve covered the “greater 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 above 30 years old. To that, we use the expression:
Age ge 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 $2000,5. If we do a “greater than or equals” operator, we get the following expression:
Salary ge 2000.5
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:
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 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 ge 'N'
You’ll get all names that have the first letter in the alphabet after ’N”.
But what happens if I use a lower case letter?
First_x0020_Name ge '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.
Dates
The “greater than or equals” 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 ge '@{formatDateTime(addDays(utcNow(),-1),'yyyy-MM-dd')}'
Here’s what we get:
We’re passing as an ODATA query the following:
Modified gt '2021-05-07'
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.
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.
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 “greater 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 Clark Tibbs on Unsplash
