oData filter issue with a boolean field

[raoraheel]

I'm trying to use oData filter on IsSaleProcessingStopped field in SalesOrderHeaderV2 and having a hard time making it run. 

This field is a NoYes enum and i'm trying to filter results where value is Yes. Tried writing it few different ways(eq '1', eq 1, eq true, eq 'true') but keep getting errors. Any suggestions?

An error has occurred. A binary operator with incompatible types was detected. Found operand types 'Edm.Boolean' and 'Microsoft.Dynamics.DataEntities.NoYes' for operator kind 'And'.

[Jcook]

Hi @raoraheel 

Have you tried using a filter array action instead. Sometimes there is some limitations on the actions Odata queries.

If you want you can use the last filter on the Dynamics one and than use the filter array for the first filter you are trying to do.

[v-alzhan-msft]

Hi @raoraheel ,

We couldn't use odata filter on Yes/No type column currently, as is mentioned in the post below:

https://powerusers.microsoft.com/t5/Building-Flows/Filter-a-query-by-Yes-No-column-not-working/td-p/...

And you could yse the Filter Array to achieve the requirement:

Best regards,

Alice   

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[raoraheel]

@v-alzhan-msft please correct me if i'm wrong. Wouldn't that mean that first I will get unfiltered orders and then the filter array will do my filtering? I was hoping to start with first set of filtered orders and then use filter array to drill further down with some conditions. Let me know please. Thanks

[v-alzhan-msft · Accepted solution]

Hi @raoraheel ,

Yes, you could only filter the Boolean field in the Filter Array action.

Best regards,

Alice   

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Tacsi]

You can use IsSaleProcessingStopped ne 'false' the inverse seems to work. 

[Rieke_]

Thanks! This seems to do the trick.

[MaxBarrass]

So that we have the right solution for this, you need to use 1 for true 0 for false.

so in your case it should be IsSaleProcessingStopped eq 1.

[witan]

Thank you @Max -- using 1 or 0 seems to work for me too.  Only thing that did.

Which is so strange, since if you look at your Power Automate "Raw Output" for the sharepoint data grab, it says clearly "true" instead of "1".  

Oh, Microsoft.... 🙃

[SecurityChris]

You are able to crate an ODATA Filter Query for boolean (Yes/No) values. True (Yes) evaluates as '1', False (No) as '0'. In your case the syntax would be:

IsSalesProcessingStopped eq '1' and SalesOrderNumber eq '63723566'

I was trying to achieve something similar and couldn't accept it was impossible to filter on boolean values, or that multiple actions would be required to do so. I found what I was after on Power Automate SharePoint Get Items Filter Query + 12 Examples.

I have used and tested this syntax and the output was as required - only those entries that satisfied both conditions. I then created an HTML table to view the outputs:

Hope this helps almost tweo years later!

[Boneckrh19]

Answering in April of 2023, tested on a brand new flow made today. 

-No numbers ("Found operand types 'Edm.Boolean' and 'Edm.Int32' for operator kind 'Equal'")

-Nothing in single quotes ("Found operand types 'Edm.Boolean' and 'Edm.String' for operator kind 'Equal'")

-Nothing without single quotes ("Could not find a property named True", "Could not find a property named Yes")

Except for lowercase true/false:

yesnofield eq true
yesnofield eq false

Don't add true or false as an expression, just use as typed text. This is the only option that has worked for me today.

[ktitus92]

I have the same experience today (September 2023). type out [field schema name] eq true or  [field schema name] eq false. 

[Jcook]

Hi @ktitus92 

What exactly is the issue?

[ktitus92]

Jcook - I was responding to Boneckrh19 that their solution to use the field name eq true or false is what is working currently. Other suggestions in this post did not work for me, but theirs did. Just wanted to confirm that their method worked. 

[allan_t]

Thankyou, filtering boolean on 1 or 0 worked for me.  Crazy, I wasted time on the intuitive options of Yes/No, yes/no, True/False, true/false 😞