Bug? Filter (and Lookup) from SharePoint List (with Text & Yes/No Column) doesn't work when flag=yes

[kennyng]

I have a yes/no column (Private) and when I do a lookup for either Private = true or Private = false, then it works.

Private = true

Private = false

However, when I include another condition, then it doesn't work properly. 

Here is a text condition with Private = false (works properly)

When the condition is true (Private = true), i get the same result (Doesn't work properly)

However, when I set the condition to Private <> false, then i can get the properly result but i get a delegation warning.

[rubin_boer · Accepted solution]

hi @kennyng please try to evaluate the private column then check which in those starts with 4

Filter(yourSource, private = true, StartsWith(yourColumn,"4")

Hope it helps

[kennyng]

Thanks for the quick reply and this works. 

So when specifying multiple condition, i should always use comma separated conditions rather than specifying the "And" condition (At least for delegation)?  I suppose it doesn't matter if it's a non-delegate function in which Power Apps will evaluate.

[kennyng]

And also, what I really want to lookup a value in SharePoint.  And in the lookup, it doesn't allow me to specify multiple formula. 

I guess one workaround is to use a filter first - something like this?

LookUp(
        Filter(
            'BAM Securities Watch List',
            DataCardValue41_1.Text = Title, 
            Private = true
        ),
        DataCardValue41_1.Text = Title,
        Private
    ) = true,

[timl · Accepted solution]

@kennyng 

For info, there actually used to be a bug that affected the filtering of SharePoint yes/no columns.

To verify if this bug still exists, if you use the following syntax to lookup the first row where private=true and title starts with "4", does that work as expected?

LookUp('BAM Securities Watch List',
       StartsWith(Title, "4") && Private             
)

In answer to your question above, First/Filter is what you could use:

First(
   Filter('BAM Securities Watch List',
           DataCardValue41_1.Text = Title, 
           Private = true
   )
)

[kennyng]

Oh when I use this syntax below

StartsWith(Title, "4") && Private

 Then it works. 

Ideally MS should fix the other syntax (Private = true) or else, it will be quite annoying to figure out what's wrong. 

Thanks @timl  & @rubin_boer !!!

Kenny

[kennyng]

Just one comment - when I use this syntax (&& Private), I get a delegation warning.   But if i do this (&& Private = true), then it looks like it's using delegation.  Any idea why?

[timl]

Hi @kennyng 

I'm not sure what the answer is to this - I think this must be a manifestation of the bug which occurs when you combine yes/no criteria with the && operator.