September 24, 2021

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'
If you’re wondering why the “x0020” URLs don’t support spaces so they need to be encoded to be able to access them. SharePoint does this encoding in the backend, but if we want to reference the table directly, we need to use the encoded version since it will also include the OData expression n the URL.

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
True or false values need to be sent as 1 or 0 respectively. Also, for the numeric type, although it’s stored internally as 5.0, you can use 5. Please be aware that if the value has a decimal component, you need to specify it. You can’t do the query above and hope to get 5.1 and 5.5, for example.

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.

Pro Tip:
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.

Am I missing something? Leave a comment or interact on Twitter. Let’s work together to make this reference as complete as we can

Back to the OData Reference.

Photo by Teslariu Mihai on Unsplash

Manuel Gomes

I'm a Project Manager with experience in large projects and companies. I've worked in the past for companies like Bayer, Sybase (now SAP) and I'm currently working for Pestana Hotel Group.

View all posts by Manuel Gomes →

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: