Filter must meet both conditions per row

[HFG]

Hi all, 

Something I feel I should be able to do is proving tricky so I'm turning to you all for help. 

I have a set of data something like below. I want to keep the rows in yellow. I want to exclude Apples and Grapes if they are Green, but keep all the other green rows. So I need that the condition to be not equal to Apple or Grapes and colour not equal to Green. Everything I have tried filters out the other rows because of the colour being green. How can I get it to look at both conditions for each row? 

An example of what I have tried :

ClearCollect(

            colFruits
                Filter(
                   'VW.Fruits'
                        , (Name <> "Apple" && Colour<> "Red")
                       &&

                      (Name<> "Grapes" && Colour<> "Red")
)

Thanks

[phipps0218]

Can you try this and then show me what appears in the collection?

You want the collection to include everything in yellow so you should have:

ClearCollect(

            colFruits
                Filter(
                   'VW.Fruits',

Name <> "Apple" && Colour<> "Green" &&

                      Name<> "Grapes" && Colour<> "Green"
)

That will provide you a collection of all those in yellow.

Please Accept as Solution if it solves your question. Or just give it a Thumbs Up if it is helpful as can help others.

LinkedIn: https://www.linkedin.com/in/charlie-phipps-%F0%9F%91%A8%E2%80%8D%F0%9F%92%BB-91338715b/
Subscribe to my YouTube: https://www.youtube.com/channel/UChmFBGU1YKIU91sNMQ7buGg

[TheRobRush · Accepted solution]

ClearCollect(

            colFruits
                Filter(
                   'VW.Fruits', 
                   And(Colour="Green", Or(Name="Apple", Name="Grapes"))=false
                        )
                )

[HFG]

Hi @phipps0218 and @TheRobRush 

Sadly, neither of those solutions have worked. In the first case everything was filtered out because they all are either Apples/Grapes or Green. 

I was hopeful with the second suggestion, but it also filtered out everything. I'll try variations of that though and see if something works. 

Let me know if you have any other ideas.

Thanks!

[TheRobRush]

I built a collection that is the exact one pictured in your example.

Shown below is Test App I put together to display it

On left is the full collection, and I gathered it liek this

in onselect of the button below it

ClearCollect(SampleTable,
         {Name:"Apple", Colour:"Red"},
         {Name:"Apple", Colour:"Green"},
         {Name:"Grapes", Colour:"Red"},
         {Name:"Grapes", Colour:"Green"},
         {Name:"Pear", Colour:"Green"},
         {Name:"Courgette", Colour:"Green"},
         {Name:"Cucumber", Colour:"Green"})

on the right is the narrowed collection, which was collected like so

OnSelect

ClearCollect(FruitTable,
            Filter(SampleTable, And(Colour="Green", Or(Name="Apple", Name="Grapes"))=false))

If that is not working for you same as in my screenshot, there's something wrong with how it's been written when plugged in on your side. (Unless results bring in more than 2k items, or 500 if you haven't chanegd default items count)

I would recommend making another screen in your app and recreating the above so that you can see it in action and get a better understanding  of how it is working for when you apply it to your own data

[TheRobRush]

Can also skip the collect and make it the source of the gallery like so

Filter(SampleTable, And(Colour="Green", Or(Name="Apple", Name="Grapes"))=false)

[TheRobRush]

We can also keep full list in a gallery and put a yellow  box behind the lot with a visible set to

And(ThisItem.Colour="Green", Or(ThisItem.Name="Apple", ThisItem.Name="Grapes"))=false

then we get

[HFG]

Hi @TheRobRush 

Thanks for taking the time to look into this. I copied your code and indeed it works perfectly, but still not working on my data. As you probably guessed, my table is not actually a table about fruits, I used it as an example. My table is actually a SQL view where there is a text column (Name) pulled from a table and a text column (Type) created in the view. The result is being used in a combo box.

At worst I can filter out the rows I don't want using the ID but it's risky if my data changes at some point. 

I'll keep trying!

[HFG · Accepted solution]

@TheRobRush if I first collect the view into a collection and then filter it using your code it works. Weird! 

Thanks for your help!