by Data aggregation is essential when performing requests for data, and we’ve already covered the And Operator that helps with that. On the other end of the spectrum, we have the “Or Operator” that aggregates the data where only one condition needs to be true for the data to be 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.
We’ve already covered the “equals operator” or “eq operator,” so if you have questions, you can check it here.
Let’s fetch everyone 5 or 10 years of experience. This could be people that are due to a promotion, for example. We don’t want people that have less or more than that. Let’s build the expression based on those conditions.
Experience_x0020__x0028_Years_x0 eq 5 or Experience_x0020__x0028_Years_x0 eq 10
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:
The result is only possible because we have both conditions at play here. If we have either condition independently, we will get a lot of data back from SharePoint, and then we would need to filter it. This way, SharePoint only returns the data we’re interested in.
Combine types
You can combine different types of data in the same request. For example, let’s query people with less than 5 years of experience or younger than 30.
Experience_x0020__x0028_Years_x0 lt 5 or Age lt 30
Here’s the result:
I want to highlight the “Alisa Hunt” record. Notice that we’re using the “Lower Than Operator.” This record shows up only because of the Experience and not the Age. The “Lower Than Operator” defines the max value not included, so please keep this in mind when building your expressions.
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 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.
Combine with care
Although you don’t have a defined limit for “or operators,” keep them to as low as you can. The reason is not performance, although many expressions will impact performance for sure, but more 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.
Another reason to keep things minimal is that the “or operator” only needs one value to be true, so you’ll have a lot of data that you need to parse.
Only include the data you need.
There’s always the temptation of fetching a bit more data and then filter it in your App. We want our apps to run as fast as possible, and getting the data from one service to another impacts the performance of any app. Networks and servers are “moody,” and things may be slow once in a while. So having the least data possible helps a lot in keeping things running smoothly. Also, the server usually is quite efficient filtering data, so add more operators to your expression but only get the data you need.
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 Possessed Photography on Unsplash
