Static filter inside Gallery on start

[RobertM91]

Hello,

I created a gallery from excel file and I edited to show all the labels I want:

Name

Surname

ID Number

Team

Status

And now i want to make this gallery show only items with certain status without typing it in search box. Whenever any user open the app and enter the list he will only see records with Status "Ready" for example?

Is there any easy newbie friendly way to do that?

[C-Papa]

hi, try changing the items property of the gallery to

Filter([DataSourceName],Status = "Ready") 

where [DataSourceName] is your connected excel file name within powerapps

[RobertM91]

Unfortunately it's not working, this is what happens when I add this line:

[C-Papa]

can you paste the formula in a message and i will adjust it so it works, basically you cant use the filter, sortbycolumn and search in that order. it needs to be re arranged. you will also run into delegation issues using the search function with sharepoint. 

[RobertM91]

Filter([ListaAgentów],Zespół_1 = "Archiwum"); SortByColumns(Search([@ListaAgentów], TextSearchBox1.Text, "NAZWISKO","IMIĘ","TEAM_x0020_MANAGER","ZESPÓŁ"), "NAZWISKO", If(SortDescending1, Descending, Ascending))

Thanks in advance!

[C-Papa]

This should do it, this is still non delegable though, you would need to replace TextSearchBox1.Text in "NAZWISKO" with StartsWith("NAZWISKO",TextSearchBox1.Text) to make it delegable but it depends if this is an issue for you for not.

SortByColumns(Filter([ListaAgentów],Zespół_1 = "Archiwum" && TextSearchBox1.Text in "NAZWISKO" || TextSearchBox1.Text in "IMIĘ" || TextSearchBox1.Text in "TEAM_x0020_MANAGER" || TextSearchBox1.Text in "ZESPÓŁ"), "NAZWISKO", If(SortDescending1, Descending, Ascending))

[RobertM91]

Unfortunately it's still not working, maybe I explained it incorrectly

I have excel file with table in which each row represents 1 person.

There is a status column in which there might be 3 values: Ready, Training, Archive

On start screen i have 3 buttons "Ready" "Training" and "Archive"

When i click on "Ready" Button for example it will move me to gallery that shows only persons that have "Ready" in status column etc.

[C-Papa]

Does the above formula give you the correct results you are expecting to see in the gallery? or is the gallery empty? 

The button is fairly easy to set up. if you have your 3 button and edit the button text to the status value, e.g, button1.text = "Ready". Then with the onselect property add Set(var_selected_status,Self.Text), this same formula can be added to all 3 buttons. then finally you just need to update the gallery items formula based on what the variable var_selected_status contains:

If(var_selected_status = "Ready",
SortByColumns(Filter([ListaAgentów],Zespół_1 = "Archiwum" && TextSearchBox1.Text in "NAZWISKO" || TextSearchBox1.Text in "IMIĘ" || TextSearchBox1.Text in "TEAM_x0020_MANAGER" || TextSearchBox1.Text in "ZESPÓŁ"), "NAZWISKO", If(SortDescending1, Descending, Ascending))

,

If(var_selected_status = "Training",

SortByColumns(Filter([ListaAgentów],Zespół_1 = "Training" && TextSearchBox1.Text in "NAZWISKO" || TextSearchBox1.Text in "IMIĘ" || TextSearchBox1.Text in "TEAM_x0020_MANAGER" || TextSearchBox1.Text in "ZESPÓŁ"), "NAZWISKO", If(SortDescending1, Descending, Ascending))

,

SortByColumns(Filter([ListaAgentów],Zespół_1 = "Archive" && TextSearchBox1.Text in "NAZWISKO" || TextSearchBox1.Text in "IMIĘ" || TextSearchBox1.Text in "TEAM_x0020_MANAGER" || TextSearchBox1.Text in "ZESPÓŁ"), "NAZWISKO", If(SortDescending1, Descending, Ascending))

))

the text in red will need to change to the correct language.

The only reason why this may not work is if the cells in excel are data validation cells, e.g. a list selection box. if they are you will need to change  Zespół_1 to Zespół_1.Value

[v-jefferni · Accepted solution]

Hi @RobertM91 ,

Please try below:

SortByColumns(
              Search(
                     Filter([ListaAgentów],Zespół_1 = "Archiwum"), 
                     TextSearchBox1.Text, 
                     "NAZWISKO","IMIĘ","TEAM_x0020_MANAGER","ZESPÓŁ"
              ),
              "NAZWISKO", 
              If(SortDescending1, Descending, Ascending)
)

You can see that I only use the filter function to replace the original table, which means filter the data source in the beginning and use the filtered result as the data source of other functions.

Hope this helps.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.