Combining functions to view items in gallery

[mdvantonder]

I have a BrowseScreen that contains a Gallery showing equipment items. I want to apply three types of functions to view these items (from a table called TableName) in the Gallery.

(1) First, from a HomeScreen showing a dashboard view of the items in the Gallery, there are buttons that navigate and filter the items in the Gallery (in BrowseScreen) based on a variable:

Button A > OnSelect: 

Set(statusVar, "A"); Navigate(BrowseScreen);

 Button B > OnSelect:

Set(statusVar, "B"); Navigate(BrowseScreen);

 Button C > OnSelect:

Set(statusVar, " "); Navigate(BrowseScreen);

These buttons navigate and filter the Gallery accordingly: Gallery > Items:

If(IsBlank(statusVar),TableName,Filter(TableName,'EquipStatus' = statusVar))

This works fine on its own.

(2)  Secondly, from the BrowseScreen, the items in the Gallery should be sorted according to a selection from a dropdown based on certain columns from my table:

Gallery > Items:

If(Dropdown1.Selected.Value = "All", TableName, SortByColumns(TableName, Dropdown1.Selected.Value, Ascending))

 This also works fine on its own.

(3) Thirdly, I want to add a search field as well where the user can search for a particular item.

How do I combine all three of these functions in the Gallery's Items property? I tried separating the functions with a ' ; ', but it gives me an error.

[Drrickryp]

Hi @mdvantonder 

Could you please post a screenshot or several screenshots that illustrate your problem?  Are you using SharePoint as the datasource?

[mdvantonder]

Hi @Drrickryp. I am using an Excel table as the data source (TableName).

The first function allows with the selection of a button (Buttons A, B, C, D, 'View all items' in HomeScreen) to filter the items in the Gallery (BrowseScreen), using a variable (statusVar). Below is a (simplified) screenshot of the HomeScreen containing the buttons that Filters the Gallery and Navigates to the BrowseScreen:

Set variable to button selection

BrowseScreen for first function:

The second function should enable the user to sort the Gallery (from the BrowseScreen) according to column names, i.e. Instrument, Make, Expiry Date. This should clear the previous filter if Buttons A-D were selected to filter the items, and sort all items according to their selected column value from the drop down list (currently the column names 'Instrument', 'Make', and 'ExpiryDate'. I do not know how to clear the initial filter function and then running the second sort function. Currently, the following code works to only sort the items per the drop down (so I have to clear the previous function in Gallery>Items):

If(Dropdown1.Selected.Value = "All", TableName, SortByColumns(TableName, Dropdown1.Selected.Value, Ascending))

 And then, thirdly, the Search function should also be included if the user would prefer to search for a specific item, clearing the other two function that filter or sort.

I would like to combine all three options (if possible)? I.e. Filter (through buttons) OR Sort (drop down) OR Search, clearing the other functions if these are selected.

[Drrickryp]

@mdvantonder 

I think that I would first use the buttons to create a single collection using ClearCollect() based on a filter specific to each button, and call it colItems. The collection could also be sorted at this level if you wish. 

ClearCollect(colItems, Sort(Filter(table1, Field = xxx),Field,Ascending);Navigate(scr2,None)

Then when you navigate to the gallery, use the collection in your Items property to drill down by instrument, make or expiration date.

Search(coItems,Textinput1.Text,"Instrument","Make","ExpiryDate")

In this scenario you would only need one gallery.  Each time you went back to the dashboard and pressed another button, the collection would be cleared and recreated based on the filter in the particular button.  

There are a couple of potential problems. First each of your filters can return no more than 2000 items to the collection.  This means File>Settings>Advanced Settings> Item limit can be raised to a maximum of 2000 (500) is default.  Second, Dates are notoriously difficult to work with between Excel and Powerapps so you may run into some problems in searching your expiry dates and Search() only works with text fields so there would have to be some transformation to convert the date to text.    

[mdvantonder]

Hi @Drrickryp Thank you for the suggested solution! I tested your suggestion and it works; however, the results are not entirely as expected. You are right with the buttons being the first function that is executed, navigating from the HomeScreen to the BrowseScreen, only showing the filtered results as per button. However, the three functions must perform independently and not be combined. In essence with Function 1 - Filter, Function 2 - Sort, Function 3 - Search, the following should happen:

Function 1 is executed automatically to navigate from home to browse (filter items per selection, or no filter if View all items were selected), BUT if dropdown (on browse screen) is selected, clear Function 1 and/or 3 and execute Function 2 (sort all items per selection, not filtered items), or if search (on browse screen) is selected, clear Function 1 and/or 2, and execute Function 3 (search through all items for text input, not filtered items).

Is this jumping between functions possible?

[v-bofeng-msft · Accepted solution]

Hi @mdvantonder :

If you need to combine these three functions, then you will face 8 situations.

• Dropdown1.Selected.Value="ALL"   OR  Dropdown1.Selected.Value<>"ALL"
• IsBlank(statusVar)   OR  !IsBlank(statusVar)
• IsBlank(SearchBox.Text)   OR  !IsBlank(SearchBox.Text)

Number of situations that need to be handled:2*2*2=8

You only need to use the if function to separate them and assign a suitable formula for each situation.

I assume the search box is a textinput control(SearchBox).Please try this code:

If(Dropdown1.Selected.Value = "All", 
   If(IsBlank(statusVar)&&IsBlank(SearchBox.Text),/*Case1*/
      TableName,
      IsBlank(statusVar)&&!IsBlank(SearchBox.Text),/*Case2*/
      Filter(TableName,SearchBox.Text=Searchfiled1||SearchBox.Text=Searchfiled2),
      !IsBlank(statusVar)&&!IsBlank(SearchBox.Text),/*Case3*/
      Filter(TableName,'EquipStatus' = statusVar && (SearchBox.Text=Searchfiled1||SearchBox.Text=Searchfiled2)),
      !IsBlank(statusVar)&&IsBlank(SearchBox.Text),/*Case4*/
      Filter(TableName,'EquipStatus' = statusVar)
      ),
   SortByColumns(
         If(IsBlank(statusVar)&&IsBlank(SearchBox.Text),/*Case5*/
            TableName,
            IsBlank(statusVar)&&!IsBlank(SearchBox.Text),/*Case6*/
            Filter(TableName,SearchBox.Text=Searchfiled1||SearchBox.Text=Searchfiled2),
            !IsBlank(statusVar)&&!IsBlank(SearchBox.Text),/*Case7*/
            Filter(TableName,'EquipStatus' = statusVar && (SearchBox.Text=Searchfiled1||SearchBox.Text=Searchfiled2)),
            !IsBlank(statusVar)&&IsBlank(SearchBox.Text),/*Case8*/
            Filter(TableName,'EquipStatus' = statusVar)), 
         Dropdown1.Selected.Value, 
         Ascending
   )
)

In additino,my design of the search function in the above formula is:

Filter(TableName,SearchBox.Text=Searchfiled1||SearchBox.Text=Searchfiled2)

You can also use the Search function. When it and Filter(TableName,'EquipStatus' = statusVar) need to be used at the same time, you can also use the following formula:

Search(Filter(TableName,'EquipStatus' = statusVar),SearchBox.Text,"Searchfiled1","Searchfiled2")

You can freely choose the formula combination and nesting order. If you still have any question, please don't hesitate to let me konw.

Best Regards,

Bof

[mdvantonder]

Thanks @v-bofeng-msft! Using your function does not give me any errors; however, the Search part does not work in the app.
I rechecked everything: the button filtering (with the variable statusVar) and the dropdown combinations work fine, but as soon as I try to search (using text input), all items clear from the gallery. I tried both your nested if-statement function without the Search function, and then including the Search(Filter()) combo.

[v-bofeng-msft]

Hi @mdvantonder :

When you use Search(Filter()):
If the conditions of Search() and Filter() are mutually exclusive, then no results will appear.
If you need the union of Search() and Filter(), using Filter() alone is a better choice than Search(Filter())

Best Regards,

Bof