OData: Not Equals Operator

We’ve already covered the “equals” operator but let’s look at its counterpart. The “not equals” operator. It provides a useful filter of data that we don’t want to fetch from the server. Where the “equals” we get what we want, here we exclude what we don’t want, parsing out the data exceptions. Its also quite useful to find incorrect results. You can define a “equals” expression where you define what the data “should be” and then use the “not equals” operator to find data that is incorrect, for example.

There are many areas in the Power Platform and more that the OData Equals Operator is useful. Still, I’ll use Power Automate as an example to demonstrate its usage.

Usage

We need to know that the equals are represented as “ne” in the OData syntax. Let’s check an example using SharePoint Lists and Power Automate.

To test it, I’ve created a list of fake people with some random data.

Let’s use Power Automate as our testing tool to fetch information using the “not equals” operator.

Strings

Let’s say that we want to fetch everyone called “David.” We know that the column is called “First Name,” so let’s build our expression:

First_x0020_Name ne 'David'

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:

As we can see, we don’t get any rows with “David.” The result will return the value “First_x0020_Name,” so this way, you’re sure that you’re filtering using the correct value.

Integers or Floats

The same works for Integers of Floats. Let’s try to figure out who’s not 29. To do that, here’s the expression.

Age ne 29

Using the same strategy as before:

Here’s the result:

Notice that we’re not using quotes here since we don’t need them.

How about a number column that is a “Currency” in SharePoint. SharePoint stores the value as a number, so you can tell us the same expression as before, and you’re fine.

Boolean fields

In the case of boolean comparisons, you need to use the 1 or 0 notation. 1 for true and 0 for false. Here’s the expression for the Employee column:

Employee ne 0

Don’t try to use “true” or “false” or any other notation because it won’t work. Here’s the test case:

And 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.

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.

If possible, prefer the “equals” operator.

People tend to think in a “positive” way. Something is equaled to something. Querying for something that “isn’t” something is a shift of mindset, so use it only when it makes sense. Prefer the equals operator to get the data that you want. It’s a lot more work to filter the data that we don’t want than to filter only the data we want.

Often the “not equals” operator is used to remove exceptions. Be careful with this since more exceptions may come that are not considered in your expression and result in invalid data.

Use it when it makes “sense.”

Depending on how your columns are named, you may want to use the “not equals” operator instead of the “equals.” For example, if a column is named “Is Consultant” and you want to get the employees, you can use the “equals” to false, but it makes more sense to read it as “is a consultant not equals to true.” The result is the same, but how the expression is written makes it much clear its intention.

It’s ideal for cleaning data.

I’ve mentioned above, but the “not equals” operator is ideal to find edge cases or information that is not correct. You can create expressions that find cases where it returns something; we need to clean the data. For example, a column with a pre-defined list of values and has a value that is not on that list should be flagged. This is only possible with a “not equals” operator since you’re looking for something that should not be there.

It’s ideal for hiding data.

Another amazing usage for it is to hide data that you don’t want to see. You can have a column for soft-deletion called “Is Deleted.” Having the data that is “not deleted” is quite useful and makes data recovery easier. Use it with care since if you forget to filter the data in your code, you can display data that should not be there.

Combine with care

You can combine multiple equal operators but keep them to a minimum. Keeping expressions as small as possible is a good practice. But the main reason is 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.

Back to the OData Reference.

Photo by Arie Wubben on Unsplash