Unanswered

Named Formula (Record): Issues using named formula Boolean values in a filter function

(0) Share

Report

Posted on by andywweaver

testBoolean = LookUp(
 ToolboxUsers,
 email = User().Email
)

testBoolean is a named formula. It is a record from a data source. It has a Boolean value called:

testBoolean.in_ori

The named formula is used in a gallery with the items below

Filter(
 ToolboxUsers,
 in_ori = testBoolean.in_ori
)

PowerApps recognizes that testBoolean.in_ori is Boolean and it = true (see photo)

testBoolean is a record from the data source ToolboxUsers, so in_ori is found in both and both contain Boolean values. However when the gallery is filtering using testBoolean.in_ori it does not filter properly.

When you change the filtering from:

//THIS
Filter(
 ToolboxUsers,
 in_ori = testBoolean.in_ori
)

//TO THIS
Filter(
 ToolboxUsers,
 in_ori = true
)

The gallery does not filter properly using testBoolean.in_ori even though PowerApps recognizes that testBoolean.in_ori is Boolean and it = true

The gallery filters properly with a true

Categories:

Experimental features

I have the same question (0)

All responses (13)

Answers (0)

Ramspevi 110 on at

Like (0)

Report
Hello @andywweaver !

Filtering for Boolean seems to behave odd sometimes.

When Filtering for Boolean Value in a Collection (LookUp works as well) just use the Column itself as Condition, if it is, as in your case, holding boolean values.

In your case that would be:

Filter(ToolboxUsers, in_ori)

Looks funny but works.

If you want to filter with a variabel:

Filter(ToolboxUsers, in_ori && testBoolean.in_ori)

should work.

Was this reply helpful? Yes No
andywweaver 14 on at

Like (0)

Report

Hey @Ramspevi!

Thank you for your response, let me clarify:

The filter needs to change depending on the user's in_ori status. ToolboxUsers stores a list of all of the app users. The 'user' named formula is a record from 'ToolboxUsers'. If user.in_ori = false then I only want users where in_ori also equals false. It filters dynamically based on the user.

The issue does not lie with my filter formula, it was working properly when 'user' was stored as a variable. The filter stopped working when I converted 'user' to a named formula instead of a variable. With any lookup record from 'ToolboxUsers' as a named formula, the boolean value is not interpreted correctly when used to filter the same data source it came from. However, it is interpreted correctly in every other instance, ie: toggle default in my photos above

I can of course always revert it back to a variable but named formulas is an upcoming feature and I hope to make this a known issue.

Was this reply helpful? Yes No
Ramspevi 110 on at

Like (0)

Report

Thanks for clarifying but I can't reproduce your issue.

I tried myself with various sources and filters and it always works as expected.

Could you check if this happens in the published app as well, and what and if the record has values.

Maybe a stretch and you will have already checked that but will the LookUp retrieve values for every E-Mail?
In this case is your account you are editing in ToolboxUsers?

Was this reply helpful? Yes No
ShantanuP on at

Like (0)

Report

I am unable to repro the issue as well. Can you please share which Studio version that you are using?

Was this reply helpful? Yes No
andywweaver 14 on at

Like (0)

Report
Step 1
Create a test app
Under settings and upcoming features, turn on Named Formulas.
Add a SharePoint data source with a Boolean column title in_ori and add some records and ensure there are both true and false in_ori values
Under App.Formulas, establish a record using the following named formula:

testBooleanRecord = LookUp( DATASOURCE, ID = 1 )

Step 2
Add a gallery with the items set to:

Filter( DATASOURCE, in_ori = testBooleanRecord.in_ori )

The gallery will not filter the data properly when you are filtering the data with a record from the same data source if that record is stored as a named formula.

Step 3
Add a toggle to your screen and set the default value to:

testBooleanRecord.in_ori

Notice the toggle Value defaults properly
Now change the gallery's items to:

Filter( DATASOURCE, in_ori = Toggle.Value )

Notice the gallery filters properly

Step 4
Delete the named formula testBooleanRecord
Set the gallery's items back to:

Filter( DATASOURCE, in_ori = testBooleanRecord.in_ori )

Add a button with the OnSelect set to the following and press the button:

Set( testBooleanRecord, LookUp( DATASOURCE, ID = 1 ) )

The gallery now filters properly.

A Boolean value in a record from a SharePoint Data source when the record is stored as a named formula, is not properly interpreted when the Boolean value is used to filter the same data source that the record came from.

@ShantanuP @Ramspevi Power apps build version is 3.23091.14

Was this reply helpful? Yes No
ShantanuP on at

Like (0)

Report
Does following work for you? This one doesn't have any named formula in it.

Filter( DATASOURCE, in_ori = Lookup(DATASOURCE, SP = 1).in_ori )

Was this reply helpful? Yes No
ShantanuP on at

Like (0)

Report

It looks like a bug but not in named formula as you can see above expression without using named formula also exhibits the same behavior. I will follow up with the team to get this fixed. Thank you for reporting it.

Was this reply helpful? Yes No
ShantanuP on at

Like (0)

Report
Here is a workaround that you can use

Filter( DATASOURCE, in_ori = Value(testBooleanRecord.in_ori) )

Was this reply helpful? Yes No
Ramspevi 110 on at

Like (0)

Report

Ohh wow.

Blame on me or us for not reconizing this. It is not like thats THE first and best known hurdle in the conquest of mastering the sharepoint request API.... Boolean Columns aren't true / false they are 1/0.

Seems like the parsing/interpreting of SP boolean values in PowerApps is inconsistent? Just a guess of a PowerFX / PowerApps enthusiast. Anyhow congratulations on the huge milestone of PowerFX 1.0 going GA!

Was this reply helpful? Yes No
ShantanuP on at

Like (0)

Report

Thanks @Ramspevi . Yes, that's exactly the issue is here. Good guess 🙂

Was this reply helpful? Yes No