Answered

Filter(source,field=true) selects rows where field is false ?!

(0) Share

Report

Posted on by R3dKap

1,594

I am creating a SharePoint "application" using custom lists for a customer to manage all its contracts with its employees who work at health care facilities. There are 20 facilities and one head office.

I have the following "Jobs" SharePoint list (the column "Emploi du siège", technical name is scECTREmploiSiege, is true for jobs that are only for the head office of the company):

Image 1.png

So, there are 2 jobs for head office and 7 jobs for other health care facilities.

I have another SharePoint list for storing contracts in which the user needs to specify the facility ("Etablissement") and the job ("Emploi"):

Image 2.png

As you can see on this capture, next to the "Etablissement" label there is a "count=7" label...

This label's Text property is set as follow:

Concatenate("count=";Text(CountRows(VAR_Emplois)))

The OnSelect property of the Etablissement listbox I set the following formula (Emplois is the SharePoint list "Emplois" data source):

ClearCollect(VAR_Emplois;Filter(Emplois;scECTREmploiSiege=true))

So, whenever I choose a value in the Etablissement listbox, it should show me a label "count=2" because there are only 2 rows in the Emplois list for which Emploi du siège is true, but it does exactly the opposite : it selects the rows where Emploi du siège is false...

How is that even possible ? Any explanation ? What have I done wrong ?

Thank for your help

Categories:

General Questions

All responses (2)

Answers (1)

R3dKap 1,594 on at

Like (0)

Report

Re: Filter(source,field=true) selects rows where field is false ?!

Hi @v-xida-msft,

Thank you for your reply and analysis...

Indeed my Emploi du siège column is a Yes/No column...

So I tested your first suggestion (using the "=1" instead of "=true") and it works fine (even though there is a "blue message" saying that the types are incompatible, that is, comparing a boolean with a number).

I chose your first alternative solution over the second, because using "<>false" would then show me a "blue message" about delegation which kind of scared me a little bit... 🙂

Anyway, thanks for your help... I'll keep this trick in mind.

Any idea when this bug will be fixed (which seems to be a relatively annoying one that could probably be easily fixed I guess) ?
Verified answer

v-xida-msft on at

Like (1)

Report
Re: Filter(source,field=true) selects rows where field is false ?!

Hi @R3dKap,

Could you please share a bit more about the "Emploi du siège" column within your Emplois list? Is it a Yes/No type column?

It is a known issue when using the Yes/No type column of a SP list within a PowerApps app. The TRUE or FALSE value would not work with the Yes/No type column of the SP list within an app.

The user @Meneghino has faced similar issue with you, please check the response within the following thread:

https://powerusers.microsoft.com/t5/Connecting-to-Data/Possible-bug-filtering-SharePoint-list-by-yes-no-column-not/td-p/23848

As an alternative solution, you could consider take a try to replace the TRUE or FALSE value with 1 or 0. Please modify the OnSelect property of the Etablissement listbox to following formula:

ClearCollect(VAR_Emplois;Filter(Emplois;scECTREmploiSiege=1)) /* <-- replace TRUE with 1 */

ClearCollect(VAR_Emplois;Filter(Emplois;scECTREmploiSiege=0)) /* <-- replace FALSE with 0 */

In addition, you could also consider take a try to replace the "=" operator with "<>" operator within your formula, please modify the OnSelect property of the Etablissement listbox to following formula:

ClearCollect(VAR_Emplois;Filter(Emplois;scECTREmploiSiege<>false)) /* <-- filter items whose scECTREmploiSiege column value is TRUE */

ClearCollect(VAR_Emplois;Filter(Emplois;scECTREmploiSiege<>true)) /* <-- filter items whose scECTREmploiSiege column value is FALSE */

Best regards,

Kris