Trouble filtering yes/no field in a ComboBox

[efroseroc]

Hello everyone thank you for visiting my post,

I have a SharePoint List (ListS) similar to this, 

id	Supplier	City	ActiveContract
1	Victor	NY	Yes
2	Sam	MIA	No

I want to filter all the suppliers with an active contract by name,

The code in my ComboBox is the following:

Filter(
ListS;
ActiveContract=true
)
.Supplier

I've checked some suppliers and for some reason I'm getting suppliers with an inactive contract, and not even one active.

Any feedback is greatly appreciated.

[WarrenBelz]

Hi @efroseroc ,

Your formula looks valid, however the true/false Boolean filter is not Delegable, so how big is your list?

Also probably asking an obvious question, but is the ActiveContract field in SharePoint a Yes/No field or a Choice field with the options Yes and No?

It is actually better to have a plain text field in SharePoint and hard-code the Yes/No in a drop-down in PowerApps as these are then delegable (Text) filters.

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.

[v-qiaqi-msft]

Hi@ efroseroc,

Actually, it is an known issue with “Yes/No” field in SP List, please check the following blog for more details:

https://rezadorrani.com/index.php/2019/04/14/powerapps-filter-sharepoint-yes-no-column-bug/

Based on the issue you mentioned, I have a test on my side.

Combining with your description, "ActiveContract" is a Yes/No type and it went wrong when you want to filter “Yes” items.

Try to modify your formula like this:

Filter(ListS;ActiveContract <> false)

If there is any doubt, contact me at your convenience.

Best Regards,

Qi Qiao

[WarrenBelz]

@v-qiaqi-msft ,

A little confused - two non-delegable Filters here - Booleen and Not().

I note your bug comment (which I will take on board).

[efroseroc]

Hi @v-qiaqi-msft thank you for the reply

I´ve tried with your suggestion but I get a delegation warning, checking the values I found that I got both active and inactive suppliers.

The list has 550 records, in powerapps I´ve set the limit to 2000.

I also want the user to be able to search for the supplier by name in the app.

Is there a way to work around this?

Best regards

@efroseroc 

[efroseroc]

Hi @WarrenBelz thank you for your reply,

The field ActiveContract is a Yes/No field.

As of now the list has 550 records, I´ve set the limit to 2000 in the app

About your suggestion, to do that do I have to make a calculated field to translate the Yes/no to text in the Sharepoint?

Best regards,

@efroseroc 

[WarrenBelz]

Hi @efroseroc ,

Calculated columns are not delegable either.

As Choice columns are also not delegable, as mentioned I normally do a plain text field in SharePoint and have a Yes/No drop-down hard-coded in PowerApps.

Increasing the delegation limit will not get rid of the warning and if the list grows, it will suddenly stop working as designed. I have always found it good practice to eliminate all Delegation issues with Variable or source data management.

Happy to help further.

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.

[efroseroc]

Hi @WarrenBelz 

Thank you for clarifying, Can you please elaborate on the solution? 

[WarrenBelz · Accepted solution]

Hi @efroseroc ,

The simple answer with Yes/No columns in SharePoint is that if you want to filter lists on their value in the future, do one of two things.

• Change them to a Single Line of Text and hard-code ["Yes","No"] as the Items of a drop-down in Power Apps.
• Have a status text column updated if necessary on control/s changing. You can do this with a Variable set at the OnChange of the control/s and resetting the other control.

I do a bit of both.

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.

[efroseroc]

Hi @WarrenBelz 

So to summarize the process would be something like this,

1. Go to the SharePoint and change the field type to a Single line of text

2. Hard-Code the Yes-No in a dropdown

3. Filter the list based on the previous dropdown.

Is this correct?

Can I skip the second Item since I always want to filter the records with true value?

• Have a status text column updated if necessary on control/s changing. You can do this with a Variable set at the OnChange of the control/s and resetting the other control.

---