Filtering multiple column headers in PowerApps

[DavesTechTips]

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: 

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.

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

 

Step2. Specify the data source and columns to display.

 

Step3. Create collections to store the search options for the different search drop downs on the screen:onvisible, 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.

 

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! 

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:

BLOG - Multi Column Filter.msapp