Unanswered

Strange behavior with filtering a list over a boolean field

(0) Share

Report

Posted on by licdar87

Hi,

I have a simple Sharepoint list with a number of fields. One of those fields is a Yes/No field called "Managed" (default value: No)

If I try to use this code inside the Items field in a gallery component for my app:

SortByColumns(
 Filter( 
 [@'My List']; 
 
 Or(
 Radio_choice.Selected.Value = "All"
 ;And(Radio_choice.Selected.Value = "Not managed"; ThisRecord.Managed = false) 
 ;And(Radio_choice.Selected.Value = "Managed"; ThisRecord.Managed = true) 
 )
 )
 ;"Managed"
 ;SortOrder.Ascending
 ;"Title"
 ;If(SortDescending1; SortOrder.Descending; SortOrder.Ascending)
)

When I click on the "managed" radio buton option, the filter does not work (it shows records with both false and true values. The other options ("All", "Not managed") work fine.

However, If I make this simple change to the code:

... And(Radio_choice.Selected.Value = "Managed"; ThisRecord.Managed = 1)

It works as expected. The funny part is that the changed code is being correctly reported as a type mismatch error (comparing Number with Boolean), but it still works regardless.

My question is: why does the "wrong" code actually work, instead of the semantically correct one?

Edit: Please note that I can't use the alternative ThisRecord.Managed<>false (which works, by the way) or similar, as it would incur into a delegation warning message.

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

WarrenBelz 153,026 Most Valuable Professional on at

Like (1)

Report
Hi @licdar87 ,

Try this structure

SortByColumns( Filter( [@'My List']; Radio_choice.Selected.Value = "All" || (Radio_choice.Selected.Value = "Not managed" && !Managed) || (Radio_choice.Selected.Value = "Managed" && Managed) ); "Managed"; SortOrder.Ascending; "Title"; If( SortDescending1; SortOrder.Descending; SortOrder.Ascending ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
licdar87 16 on at

Like (0)

Report

Hi, thank you for the answer.

Your solution works, however, it triggers a delegation warning. I'm looking for a fully delegable solution as the list is expected to grow pretty fast with time and I can't risk a loss of performance in the future.

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report

@licdar87

Change your yes/no column to single line of text and hard code your controls ["Yes", "No"]

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report

@licdar87

Change your yes/no column to single line of text and hard code your controls ["Yes", "No"]

Was this reply helpful? Yes No
Verified answer

WarrenBelz 153,026 Most Valuable Professional on at

Like (1)

Report

Hi @licdar87 ,

Your issue is that Boolean fields are only partly delegable (only if used on their own) and also the ! Not(0 filter is not Delegable - assuming you want the combinations in the filter you have, then (refer this blog of mine) stop using Boolean columns (which I never do) and convert to Text "Y/N", "Yes/No" or "True/False" and your queries will always be Delegable (as far as Text can be). As noted you can still use Checkboxes to manage the data.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
licdar87 16 on at

Like (0)

Report

Thanks for the clarification about boolean values. It is baffling getting to know that a boolean value is more troublesome than a string value, but it is what it is I guess.
I will change the field type to string asap.

Thanks again.

Was this reply helpful? Yes No