Solved: Ignore Filter with some choices?

TimmyBoy · ‎01-14-2023

Hello,

How can i check from a choice field (sharepoints list) i want to ignore some search results?

I put this code on TEXT inside a label

If(CountRows(
        Filter(
            MyData,
            Nota = Concat(Gallery3_2.Selected,Value & Char(10))
        )
),

"(Results 0)"
)

The Gallery looks like this with checkboxes

The Nota is a choice field in a sharepoint list.

So the (Results) will show how many records has been found without the choices we have selected.

Thank you

WarrenBelz · ‎01-15-2023

@TimmyBoy ,

I have spent about an hour on this on a model, so will give you what I have - it seems to work, but I will have to leave it to you to play with, understand and finish (you may have different references depending on your Gallery Items)

Firstly, you need to initiate a Collection - Screen OnVisible or whenever you want to start the selection again

ClearCollect(
    colItems,
    Table({Value: Blank()})
);
Clear(colItems)

Now on the OnSelect of your checkbox

Clear(colSelect);
If(
   CountRows(
      Filter(
         Gallery3_2.AllItems,
         CheckBoxName.Value
      )
   ) = 0,
   ClearCollect(
      colSelect,
      MyData
   ),
   ForAll(
      Filter(
         Gallery3_2.AllItems,
         CheckBoxName.Value
      ) As aSel,
      Collect(
         colSelect,
         Filter(
            MyData,
            !(aSel.Value in Nota.Value) && !(aSel.Value in colItems.Value)
         )
      );
      Collect(
         colItems,
         {Value: aSel.Value}
      )
   )
)

and your Label

"(Results " & CountRows(colSelect) & ")"

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.

MVP (Business Applications) Visit my blog Practical Power Apps

View solution in original post

WarrenBelz · ‎01-14-2023

Hi @TimmyBoy ,

I think this is what you are trying to do if Nota is a Choice field and those are the choices from Nota in the Gallery

"(Results " & 
If(
   CountRows(
      Filter(
         Gallery3_2.AllItems,
         CheckBoxName.Value
      )
   ) = 0,
   CountRows(MyData),
   CountRows(
      Filter(
         MyData,
         !(Nota.Value in Filter(
            Gallery3_2.AllItems,
            CheckBoxName.Value
         ).Value)
      )
   )
) & ")"

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.

MVP (Business Applications) Visit my blog Practical Power Apps

TimmyBoy · ‎01-14-2023

Hello @WarrenBelz

Thank you, but seems i have an error on the last value

If i remove the last value then i get an error in the nota.value

WarrenBelz · ‎01-14-2023

@TimmyBoy ,

You said Nota was a Choice field - is it single or multi-choice ?

TimmyBoy · ‎01-14-2023

@WarrenBelz

Yes it is a choice field,

I shall keep in mind i will explain it correct in the near future 😉

WarrenBelz · ‎01-14-2023

@TimmyBoy ,

But is it single or multi-select ?

TimmyBoy · ‎01-14-2023

@WarrenBelz

Ooh, sorry, yes multi-select

WarrenBelz · ‎01-14-2023

@TimmyBoy ,

Sorry this is in the WTH (way too hard) basket - I don't believe you can do it as you have a many-to-many relationship and you are looking for the negative (not matching) instead of the positive (matching). The issue is that as you loop through one or the other table (the check boxes or the table field - and this requires a different structure to what I posted) you will get all records every time unless you only select one checkbox as all but the first loop will re-insert the records excluded from the earlier ones due to the fact they do not match the current item being filtered.

TimmyBoy · ‎01-14-2023

@WarrenBelz ,

Okay, can it been done if we select the checkboxes and add them in a collection

And then look for the records we have in there?

Instead working with the CheckBox

If i add this code to the checkbox

ClearCollect(colSearchRequired, ThisItem.Nota)

WarrenBelz · ‎01-15-2023

@TimmyBoy ,

I also had a collection model OnChange of the checkbox, but your problem is the same - I can do a matching many-to-many with something like this (note that .Value is the field in the gallery - yours must be different)

"(Results " & 
If(
   CountRows(
      Filter(
         Gallery3_2.AllItems,
         CheckBoxName.Value
      )
   ) = 0,
   CountRows(MyData),
	Distinct(
      CountRows(
         Ungroup(
            ForAll(
               Filter(
                  Gallery3_2.AllItems,
                  CheckBoxName.Value
               ) As aGal,      
               Filter(
                  MyData,
                  aGal.Value in Nota.Value
               )
            ),
            "Value"
         ),
         ID
      )			
   ) 
) & ")"

but when you loop in the negative (each query on the check boxes is collecting all the items missing rather than present), then you simply "put back" the items excluded in the previous itineration as they do not match the selected box being queried.