Static filter inside Gallery on start

Like (0) Share

Report

Posted on 2 Sep 2021 10:29:26 by 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?

Categories:

General Questions

All responses (8)

Answers (1)

Verified answer

v-jefferni on 09 Sep 2021 at 08:57:17

Like (0)

Report
Re: Static filter inside Gallery on start

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.
C-Papa 1,836 on 02 Sep 2021 at 12:20:31

Like (0)

Report

Re: Static filter inside Gallery on start

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
RobertM91 14 on 02 Sep 2021 at 11:34:50

Like (0)

Report

Re: Static filter inside Gallery on start

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 1,836 on 02 Sep 2021 at 11:19:55

Like (0)

Report

Re: Static filter inside Gallery on start

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 14 on 02 Sep 2021 at 11:14:30

Like (0)

Report

Re: Static filter inside Gallery on start

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 1,836 on 02 Sep 2021 at 11:11:35

Like (0)

Report

Re: Static filter inside Gallery on start

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 14 on 02 Sep 2021 at 11:00:30

Like (0)

Report

Re: Static filter inside Gallery on start

Unfortunately it's not working, this is what happens when I add this line:
C-Papa 1,836 on 02 Sep 2021 at 10:42:19

Like (0)

Report

Re: Static filter inside Gallery on start

hi, try changing the items property of the gallery to

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

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