[SOLVED] - Multi filter and Text search in Gallery

[ZowieS]

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")

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

Thank you!

[mdevaney]

@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."

[ilton]

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.

[v-yutliu-msft]

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,

[ZowieS]

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

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?

[ZowieS]

A update:

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

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!

[v-yutliu-msft]

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,

[ZowieS]

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!

[v-yutliu-msft]

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,

[ZowieS]

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.

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

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)
      )
   )