Trying to filter by field on a gallery

[Anonymous]

Hi,

I have created a simple canvas app that is linked to an excel data source, the app lets users create projects and lists the projects on the home screen. I am trying to add a filter to the home screen gallery to remove all projects with the field "Completed". I don't believe its a formula issue as I have tried most syntax combinations but rather I believe I have missed a connection from the button to the gallery.

Any help would be greatly appreciated.

Kind Regards

Andrew

[RezaDorrani]

Hi @Anonymous 

You would have to add all your logic on the Items property of the gallery

Regards,

Reza Dorrani

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[v-yutliu-msft]

Hi @Anonymous ,

Do you want to filter gallery by clicking the button?

The function of Filter will result a table. It's not used to set a button. It's used to set a gallery's Items.

Here's what you should do:

1)Set the button(iconfilter)'s OnSelect:

Set(var1,true)

2)Set the gallery's Items:

SortByColumns(
              Search(
                       If(var1,Filter(gallery1.AllItems,body.text="...."),Table2),   //if you click the button, filter the gallery
                        ....,....
                     ),
               ....,....
              )

Please replace ...  with the data that you use.

Best regards,

Community Support Team _ Phoebe Liu

[Anonymous]

Hi @v-yutliu-msft 

Firstly thank you very much for all of your support.

I have replaced the onselect button with the code you suggested and I have also replaced the BrowseGallery1 Items with the following code:

SortByColumns(Search(If(var1,Filter(BrowseGallery1.AllItems,Body1.Text="completed"),Table2)))

Unfortunately I now have no items showing in the gallery, just a few errors consisting of "invalid number of arguments" and "invalid name"

Below is the original code that was in the BrowseGallery1.items:

SortByColumns(Search(Table2, TextSearchBox1.Text, "Current_x0020_Process","Date","ID_x0020_No"), "Current_x0020_Process", If(SortDescending1, Descending, Ascending))

Again I appreciate your help.

Kind Regards

Andrew

[yashag2255]

HI @Anonymous 

Can you try to update your expressions as:

Filter Icon -> OnSelect -> Set(FilterCompleted, true)

Gallery -> Items ->

SortByColumns(Search(If(FilterCompleted,Filter(Table2,StatusColumnName="completed"),Table2), TextSearchBox1.Text, "Current_x0020_Process","Date","ID_x0020_No"), "Current_x0020_Process", If(SortDescending1, Descending, Ascending))

Here, we are setting up a variable to show the completed values. Also, please replace the highlighted part with the name of column which holds the status value.

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[Anonymous]

Hi @yashag2255,

I have done as requested but the filter does not seem to filter the "completed" as desired.

Screenshots of before and after button press attached.

Also would need to unfilter (or revert) on repress.

Kind Regards

[yashag2255]

Hi @Anonymous 

I think the blank gallery is due to blank text input for search. Can you try to set the configuration as below:

Screen -> OnVisible ->  Set(FilterCompleted, false)

Filter Icon -> On Select ->  Set(FilterCompleted, !FilterCompleted)

Gallery -> Items -> SortByColumns(If(IsBlank(TextSearchBox1.Text),If(FilterCompleted,Filter(Table2,StatusColumnName="completed"),Table2), Search(If(FilterCompleted,Filter(Table2,StatusColumnName="completed"),Table2), TextSearchBox1.Text, "Current_x0020_Process","Date","ID_x0020_No")),"Current_x0020_Process",If(SortDescending1, Descending, Ascending))

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[Anonymous]

Hi @yashag2255,

Thanks for the reply.

I will attached 2 screenshots to show you what happens on first press and second press.

Essentially first press filters everything and shows nothing in the gallery and second press reverts.

Obviously we are trying to filter only "completed" items, any help would be greatly appreciated.

Kind Regards

[Anonymous · Accepted solution]

@yashag2255 

These are the current formulas:
Gallery Items:

SortByColumns(If(IsBlank(TextSearchBox1.Text),If(FilterCompleted,Filter(Table2,"Current_x0020_Process"="completed"),Table2),Search(If(FilterCompleted,Filter(Table2,"Current_x0020_Process"="completed"),Table2),TextSearchBox1.Text,"Current_x0020_Process","Date","ID_x0020_No")),"Current_x0020_Process",If(SortDescending1,Descending,Ascending))

Filter On.Select:

Set(FilterCompleted,!FilterCompleted)

KIind Regards

[yashag2255]

Hi @Anonymous 

Can you try to modify your formula as:

SortByColumns(If(IsBlank(TextSearchBox1.Text),If(FilterCompleted,Filter(Table2,"Current_x0020_Process"="Completed"),Table2),Search(If(FilterCompleted,Filter(Table2,"Current_x0020_Process"="Completed"),Table2),TextSearchBox1.Text,"Current_x0020_Process","Date","ID_x0020_No")),"Current_x0020_Process",If(SortDescending1,Descending,Ascending))

The expression are case sensitive, so maybe that is the reason you are not getting data for Completed Status.

P.S. Accidentally marked as a solution. Please continue to post any follow ups and mark the response that helped you as a solution. 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!