cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

[SOLVED] - Multi filter and Text search in Gallery

Hi,


I've become blind and hit a stop with my app 😅

I'm not sure I'm doing it the "right way" so please correct me if I'm wrong.

 

I'm trying to filter my gallery based on clicking in one of the tree cirkels (green, yellow or red) OR what's in the Textbox and/or in the comboBox ("Välj Typ" and "Välj kodning")

2020-03-09 15_32_48-P-Vetskap - Kategorisering - Sparad (ej publicerad) - Power Apps.png

 

The cirkels are a group of 1 icon and 1 text field.

 

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Multi filter and Text search in Gallery

Hi @ZowieS ,

The reason why you met this problem is that you "||" between two combo boxes.

To solve this problem, you just need to change it to ",".

Try this:

If(IsBlank(var)||var="All",
   [sourceA],
   var="not all",
   Filter([sourceA],
          If(IsBlank(SearchBox.Text),true,SearchBox.Text in [Column A]||
                                          SearchBox.Text in [Column C]||
                                          SearchBox.Text in [Column D]||
                                          SearchBox.Text in [Column E]||
                                          SearchBox.Text in [Column F]),
          (If(IsEmpty('typ-ComboBox'.SelectedItems),true,[Column C] in 'typ-ComboBox'.SelectedItems.Result ),
          (If(IsEmpty('kodning-ComboBox'.SelectedItems),true,[Column A] in 'kodning-ComboBox'.SelectedItems.Result )
        ),
   Filter([sourceA],
          [Column A]=var)
  )

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

16 REPLIES 16
Highlighted
Super User
Super User

Re: Multi filter and Text search in Gallery

@ZowieS 

What I don't understand about this app is how the each gallery item knows which color circle to display.  Could you please supply that info along with the relevant field names?

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Highlighted
Resolver II
Resolver II

Re: Multi filter and Text search in Gallery

Hi.

Assuming that the selected color is GalleryColors.Selected.text and the search is Input.Text and the other inputs are Input2.Selected.Value and Input3.Selected.Value you can use

Filter(datasource,
   (GalleryColors.Selected.text = "" || Column1= GalleryColors.Selected.text)
   && 
   (Input.Text = "" || Column2 = Input.Text)
   && 
   (Input2.Selected.Value = "" || Column3 = Input2.Selected.Value)
   && 
   (Input3.Selected.Value = "" || Column4 = Input3.Selected.Value)

);

this is just an ideia in case you need to test a filter before using it, you will probably have to take in account the type of data in the filters and provide the proper test Blank(), IsEmpty() etc.

Highlighted
Community Support
Community Support

Re: Multi filter and Text search in Gallery

Hi @ZowieS ,

Could you describe more clearly about

1)what controls that you want to filter based on? 

In your screen shoots, there are 3 buttons, a textomput and 2 combo boxes.

Whether do you want to filter based on all of them or choose some of them? choose which ones?

2)the relationship between the controls?

I need to know the filter rule in details.

For example, if you want to filter based on three buttons and a textinput, you need to tell me their relationship like:

Filter(table,field1=button.var AND field2=textinput.text)      

Filter(table,field1=button.var OR field2=textinput.text)      

3)the data type of the fields that you want to filter based on?

4)what your data source is?

5)the items of the 2 combo boxes?

 

I assume that you want to filter based on 4 text fields(3buttons for one field, textinput for one field, 2 combo boxes for 2 fields), their relationship is AND.

If so, I suggest you try this formula to set the gallery's Items:

Filter(table,If(IsBlank(var),true,field1=var),
             If(IsBlank(textinput1.Text),true,field2=textinput1.Text),
             If(IsEmpty(combo box1.Selected),true,field3=combo box1.Selected.Value),
             If(IsEmpty(combo box2.Selected),true,field4=combo box2.Selected.Value)
       )

set green button's OnSelect:

Set(var,"green")

set yellow button's OnSelect:

Set(var,"yellow")

set red button's OnSelect:

Set(var,"red")

 

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Advocate I
Advocate I

Re: Multi filter and Text search in Gallery

Hi,

 

Thank you @v-yutliu-msft , @ilton & @mdevaney for your reply 🙂

 

I'll try and explain it some more:

 

The database - sourceA

An excel (xlsx) file in One Drive.

Column Name // Contains/Data

Column A = Contains: Red, Yellow or Green
Column B = Contains a date: dd/mm/yyyy

Column C = Contains text: Clients, Server, Network, Datacenter etc

 

The 3 circles

They contain 1 button and 1 text

The text fields
For the text fields (e.g RedNumber) the "Text" filed only has a "CountIf":
Red: CountIf([sourceA].[column A]; column A= "Red")

Yellow: CountIf([sourceA].[column A]; column A= "Yellow")

Green: CountIf([sourceA].[column A]; column A= "Green")

 

The button

Contains nothing except the colour 

 

The Searchbox

Contains nothing

 

"Välj Typ" / typ-ComboBox
Sort and distinct the values in Column C:

Items = Sort(Distinct([sourceA];[Column C]);Result)

 

"Välj kodning" / kodning-ComboBox

Sort and distinct the values in Column C:

Items - Sort(Distinct([sourceA];[Column A]);Result)

 

EOL (under Gallery2)

Shows the EOL, Column B

Text = "EOL: " & ThisItem.eol


2020-03-10 11_21_15-P-Vetskap - Kategorisering - Sparad (ej publicerad) - Power Apps.png

 


What I'm aiming for

 

The 3 circles

When you press one of them, it should filter the Gallery2.

 

The Searchbox  +  "Välj Typ" / typ-ComboBox  +  "Välj kodning" / kodning-ComboBox

Theas three should filter the Gallery2.


The Gallery2

I want the background of each item to show Green, Yellow or Red depending if the date i Column B (EOL) have passed today's date or not.

I think it's better then my circles I had before.

 

Green - Date is equal or more then today +90 days

Yellow - Date is equal or less then today +90 days

Red - Date is today or less

 

I have been googling around and for exempel I've been reading this and this. But I can't get it to work:

 

TemeplFill = If(
ThisItem.eol < Today();
RGBA(255;0;0;1);
If(
ThisItem.eol +90 <= Today();
RGBA(255;0;0;0,5);
RGBA(0;255;0;0,5);
)
)

 

(Colours (RGBA) is not verified if they are correct or not. They are just used for testing purposes)

 

 

Thought/Question? - What is better?

Since you can filter "kodning"/Column C in two different places it might make more sense to remove  "Välj kodning" / kodning-ComboBox and only keeping the circles?

 

Highlighted
Advocate I
Advocate I

Re: Multi filter and Text search in Gallery

A update:

I've change the UI but most of my questions remains 😅 

2020-03-10 15_59_05-P-Vetskap - Kategorisering - Sparad (ej publicerad) - Power Apps.png

The circles should act as filter:
Blue - Reset filter/Show all

Green - Show those who has "Green" value in Column A

Yellow- Show those who has "Yellow" value in Column A

Red- Show those who has "Red" value in Column A

 

Thank you!

Highlighted
Community Support
Community Support

Re: Multi filter and Text search in Gallery

Hi @ZowieS ,

Firstly, could you tell me whether "Välj kodning" / kodning-ComboBox is used to filter column C or ColumnA?

You describe like this:

"Välj kodning" / kodning-ComboBox

Sort and distinct the values in Column C:

Items - Sort(Distinct([sourceA];[Column A]);Result)"

I assume that you use this combo box to filter columnC.

Secondly, could you tell me the relationship between the three circles and "The Searchbox  +  "Välj Typ" / typ-ComboBox  +  "Välj kodning" / kodning-ComboBox"?

If you click the circle, whether you should filter sourceA or filter the sourceA that has been filtered by the searchbox and 2 combo boxes?

Thirdly, which column is the searchbox used to filter? I assume as CoumnD. 

 

Try these:

1)

set green button's OnSelect:

Set(var,"Green")

set yellow button's OnSelect:

Set(var,"Yellow")

set red button's OnSelect:

Set(var,"Red")

set blue button's OnSelect:

 

Set(var,"All")

 

2)set the gallery's Items:
If the circles and (searchbox+combo box) filter rule is standalone:

(filter gallery based on (ColumnC+ColumnD) OR ColumnA)  

If(IsBlank(var),Filter([sourceA], 
                       If(IsBlank(textinput1.Text),true,textinput1.Text in ColumnD),
                       (If(IsEmpty(combo box1.Selected),true,ColumnC=combo box1.Selected.Value))
                        ||
                       (If(IsEmpty(combo box2.Selected),true,ColumnC=combo box2.Selected.Value))
                       ),
   If(var="All",
      [sourceA],
      Filter([sourceA],ColumnA=var)
      )
   )            

If the circles and (searchbox+combo box) filter rule is not standalone:

(filter gallery based on ClumnA+ColumnC+ ColumnD)

Filter([sourceA], If(IsBlank(var)||var="All",true,ColumnA=var),
                  If(IsBlank(textinput1.Text),true,textinput1.Text in ColumnD),
                  (If(IsEmpty(combo box1.Selected),true,ColumnC=combo box1.Selected.Value))
                   ||
                  (If(IsEmpty(combo box2.Selected),true,ColumnC=combo box2.Selected.Value))
       )
  

3)set Column B (EOL)'s TemeplFill inside the gallery:

If(ThisItem.eol <= Today(),
   Red,
   ThisItem.eol<=DateAdd(Today(),90,Days)&&ThisItem.eol > Today(),
   Yellow,
   ThisItem.eol>DateAdd(Today(),90,Days),
   Green
   )

 

 

Bets regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Advocate I
Advocate I

Re: Multi filter and Text search in Gallery

Hi @v-yutliu-msft,


Thank you!
All is working great but the gallery won't show the results from the Searchbox and/or ComboBox😕

I've refreshed the data set and the website but I still don't get ti to work. Any idés?

If(IsBlank(var);Filter([SourceA]; 
                       If(IsBlank(SearchBox.Text);true;SearchBox.Text in [Column C to D]);
                       (If(IsEmpty('typ-ComboBox'.Selected);true;[Column C]='typ-ComboBox'.Selected.Result))
                       );
   If(var="All";
      [SourceA];
      Filter([SourceA];[Column A]=var)
      )
   )   

 

Column Name // Contains/Data

Column A = Contains Text: Red, Yellow or Green
Column B = Contains a date: dd/mm/yyyy

Column C = Contains text: Clients, Server, Network, Datacente
Column D = Contains text: Product name

Column E = Contains text: Product type

Column F = Contains text. Product Owner 

 

 


@v-yutliu-msft wrote:

 

"Välj kodning" / kodning-ComboBox

Sort and distinct the values in Column C:

Items - Sort(Distinct([sourceA];[Column A]);Result)"

I assume that you use this combo box to filter columnC.

 


Yes, you are correct. I meant [Column C] 🙂

 


@v-yutliu-msft wrote:

 

Secondly, could you tell me the relationship between the three circles and "The Searchbox  +  "Välj Typ" / typ-ComboBox  +  "Välj kodning" / kodning-ComboBox"?

If you click the circle, whether you should filter sourceA or filter the sourceA that has been filtered by the searchbox and 2 combo boxes?

Thirdly, which column is the searchbox used to filter? I assume as CoumnD. 

 

 


I would like those 3 to be available to filter the Gallery at the same time (if they have type a text inte the searchbox and/or the have selected 1 or more values in the Comboboxes)

 

Thank you for all your help!

Highlighted
Community Support
Community Support

Re: Multi filter and Text search in Gallery

Hi @ZowieS ,

What does "SearchBox.Text in [Column C to D]" mean?

Do you mean that columnC OR columnD contains the value of SearchBox.Text?

If so, please do not merge field names like this, you need to filter one by one column.

Try this:

If(IsBlank(var);Filter([SourceA]; 
                       If(IsBlank(SearchBox.Text);true;SearchBox.Text in ColumnC||SearchBox.Text in ColumnD);
                       (If(IsEmpty('typ-ComboBox'.Selected);true;ColumnC='typ-ComboBox'.Selected.Result))
                       );
   If(var="All";
      [SourceA];
      Filter([SourceA];[Column A]=var)
      )
   )   

Please notice the filter rule is:

(searchbox in columnC OR columnD) AND columnC='typ-ComboBox'

 

 

 

Best regards,



Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Advocate I
Advocate I

Re: Multi filter and Text search in Gallery

Hi @v-yutliu-msft,

 

Yes, it's OR since I want my SearchBox to search in several columns in the database. Meaning Column A to Column F 🙂

 

It's so strange. It should work but it doesn't😑

Both the SearchBox and the 2 comboboxes don't filter the Gallery.

Example:

Correct result using the circles.  Here I filter only the Green.

2020-03-12 09_19_24-Window.png

 

And here I try and filter for Green with the Combobox ("Välj Typ" / typ-ComboBox)

2020-03-12 09_19_09-Window.png

 

 

If(IsBlank(var);Filter([SourceA]; 
                       If(IsBlank(SearchBox.Text);true;SearchBox.Text in [Column D]||SearchBox.Text in [Column A]||SearchBox.Text in [Column A]||SearchBox.Text in [Column E]||SearchBox.Text in [Column F]);
                       (If(IsEmpty('typ-ComboBox'.Selected);true;[Column C]='typ-ComboBox'.Selected.Result))
                       ||
                       (If(IsEmpty('kodning-ComboBox'.Selected);true;[Column A]='kodning-ComboBox'.Selected.Result))
                       );
   If(var="All";
      [SourceA];
      Filter([SourceA];[Column A]=var)
      )
   )   

 

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Robotic Process Automation

Let's talk about the solution provided by Microsoft for Robotic Process Automation (RPA)

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

Top Solution Authors
Top Kudoed Authors
Users online (6,213)