by Sometimes having one operator that provides us with all the data that we need is hard, so it’s useful to aggregate several in the same request. That’s where the “and operator” comes into play. It aggregates two or more where all of them need to be true for the returned data. They are evaluated simultaneously, so we’re sure that the data fits all the criteria. 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 called “Sabrina” and last name “Myers.” Let’s build the expression based on those conditions.
First_x0020_Name eq 'Sabrina' and Last_x0020_Name eq 'Myers'
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 multiply types of data in the same request. For example, let’s do a query for 5 years of experience and only for employees.
Experience_x0020__x0028_Years_x0 eq 5 and Employee eq 1
Here’s 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.
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.
Combine with care
Although you don’t have a defined limit for “and 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.
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 Teslariu Mihai on Unsplash
