cancel
Showing results for 
Search instead for 
Did you mean: 

Filtering multiple column headers in PowerApps

Making sure that users have access to the right information at the right time is imperative to the success of any web\mobile application. At the same time, we have to avoid having too many design elements, different screens or view options as this might be confusing to the user as well as impact system performance.

 

This article assumes a basic understanding of PowerApps, galleries\data tables, data sources, filters and collections.

 

The following is an example of what can be built by following this article: 

Example.png

 

Overview:

  1. Create a gallery or data table
  2. Specify the data source and relevant columns
  3. Create collections to store the search options
  4. Create drop down boxes at the top of the columns for the user to select from.
  5. Add the filter formula for the gallery or data table.items
  6. Other considerations
  7. References

 

Step1.  Create a gallery or data table to your data.

As part of this article, we have created a collection for testing purposes, to trigger screen1.onstart.

ClearCollect(testdatasource,
{title:"Test123",department:"Operations",requester:"Dawid van Heerden",responsibleUser:"Jack"},
{title:"Test456",department:"Sales",requester:"Joe",responsibleUser:"Dawid van Heerden"},
{title:"Test789",department:"Finance",requester:"Jack",responsibleUser:"Mike"}
)

**If you are recreating this, please keep in mind that you would first have to restart the app for the screen1.onstart to trigger and populate the collection with data.

 

To make sure that the collection has data, go to View\Collections to see your handy work.

View Collections.png

 

Collection Sample.png

 

Now we are ready to add the data table or gallery.

 Insert data table.png

 

Step2. Specify the data source and columns to display.

 Specify data source.png

 

Step3. Create collections to store the search options for the different search drop downs on the screenSmiley Surprisednvisible, or refresh button.onselect.

 

**If you are using a SQL view, it does not support automatic datasource refreshes at the moment.

**Again please remember to restart if you are using the screen1.onvisable

 

screen1.onvisible or refresh button.onselect=
ClearCollect(SearchOptions_Department, {Result:"-All-"});
Collect(SearchOptions_Department,Sort(Distinct(testdatasource,department),Result,Ascending));

ClearCollect(SearchOptions_Requester, {Result:"-All-"},{Result:"-Me-"});
Collect(SearchOptions_Requester,Sort(Distinct(testdatasource,requester),Result,Ascending));

ClearCollect(SearchOptions_Responsible, {Result:"-All-"},{Result:"-Me-"});
Collect(SearchOptions_Responsible,Sort(Distinct(testdatasource,responsibleUser),Result,Ascending))

The following line from the above manually adds the "-All-" and "-Me-" options to the collection of options, which could later be used in the filter formula. Options for "-Today-" or "-My Team-" are other ideas that could be added as additional options.

ClearCollect(SearchOptions_Requester, {Result:"-All-"},{Result:"-Me-"})

Step4. Create drop down boxes at the top of the columns for the user to select from. You might also want to include a normal text input for the various other fields in the data source.

Specify Drop Down Collections.png 

 

Step5. Add the filter formula for the gallery or data table.items

The following can be changed according to your exact requirements, but in it's most basic form, this will filter the data based on your selection. You might have to select the columns to display after you change the data source settings from a collection to custom one.

 

table.items=

Sort(
    Filter(testdatasource,
        Screen1_Search_Text.Text in title,
        Substitute(Screen1_Search_Department.Selected.Value,"-All-","") in department,
        Substitute(Substitute(Screen1_Search_Requester.Selected.Value,"-All-",""),"-Me-",User().FullName) in requester,
        Substitute(Substitute(Screen1_Search_Responsible_User.Selected.Value,"-All-",""),"-Me-",User().FullName) in responsibleUser
    ),
title, Descending)

Here we can see the data showing correctly for the selection "-Me-" under the requester column. Nice! Smiley Happy

showing the correct data.png

 

 

Other considerations:

-Filtering with blank data (two double quotes) returns all the data, which is why the substitute on "-All-" to blank works well.

-If you are getting blue exclamations while writing filter formulas, it means that delegation can't happen to the data source, and your browser or device will be busier than it should be. See below reference article on delegation.

-If formulas get ugly, paste the formula into visual studio code and change text to C# (free with reference below) to make sense of it all

 

References:

Delegation: https://docs.microsoft.com/en-us/powerapps/delegation-overview

Data Filter: https://docs.microsoft.com/en-us/powerapps/functions/function-filter-lookup

VS Code: https://code.visualstudio.com/download

 

Example of VS Code:

VSCode.png

 

Comments

Hi @Dawidvh

 

This is a super helpful article and I thank you for posting it.

 

As I developed some apps (and my skills) I realized I needed a more logical and repeatable method to address the common need to have many filter values all working together on a gallery/table, each with an "All" option to effectively clear a selected filter easily.

 

This appears to be an excellent design pattern that allows for the use of a nice block of intuitive code and good performance as well.

 

Thanks!

Hello,

 

I am reusing your code :

Sort(
    Filter(DG_tb_Main;S1_Search_WF.Text in WindFarm_Name;
    Substitute(S1_DropDown_Status.Selected.Value;"-All-";"") in Status.Value;
    Substitute(S1_DropDown_Country.Selected.Value;"-All-";"") in Country);
    Substitute(S1_DropDown_DataSet.Selected.Value;"-All-";"") in Data_Set_Status.Value;
    Substitute(S1_DropDown_WindOne.Selected.Value;"-All-";"") in SCADA_Status.Value;
Commissioning_Start_Date;Ascending)

 

and I get an error saying that "Invalid number of arguments, 4, expected between 2 and 3).

When I remove two conditions, it works fine.

Any help?

Ufff, I solved :

Sort(
    Filter(DG_tb_Main;S1_Search_WF.Text in WindFarm_Name;
    Substitute(S1_DropDown_Status.Selected.Value;"-All-";"") in Status.Value And Substitute(S1_DropDown_Country.Selected.Value;"-All-";"") in Country;
    Substitute(S1_DropDown_DataSet.Selected.Value;"-All-";"") in Data_Set_Status.Value And Substitute(S1_DropDown_WindOne.Selected.Value;"-All-";"") in SCADA_Status.Value);
Commissioning_Start_Date;Ascending)

 

Thnks anyway

Meet Our Blog Authors