cancel
Showing results for 
Search instead for 
Did you mean: 
DavesTechTips

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 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.

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 @DavesTechTips

 

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

For anyone else that may struggle getting the following to work, replace all instances of .Value with .Result

 

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)

 

Hi Hi @Dawidvh

 

I know this is an old post but hoping someone can help me out. I am trying to get this to work on my app. The problem I think I am running in to is that I have a column (which I am filtering by) which could be null. I am trying to use the substitute function to say if -All- is selected, show everything including the rows with null values. 

I have also thought I could do a 'assigned' and 'unassigned' tag, and do it that way, but I hit the same problem - I need 'unassigned' to be the nulls. And Ideally, I'd still want the 'all' option to show everything in the table. 

 

This is what I've tried:

Substitute(BusinessOwnerDD_ItemListing.Selected.BusinessOwner, "-All-",Empty) in BusinessOwner

along with null in place of empty, which is not liked either. Have tried an IF but can't get that to work.. any help would be greatly appreciated!

 

Thanks in advance! 

 

hello 

is there any way of filtering the table headers them selves not just the content? 

Product ID

Tommikejosh

Camera 1

21005

 

i want the gallery to filter the source, by the header text of user().fullname  etc 

 

rather than moving the data so that i have ..

NameProductQuantity
tom camera 12
mikeCamera 1 100 

 

this i can filter, Items= filter(source, Name = User().fullname. 

but with a big team it get diffcult managing the data aspect  and create waaaay too many rows to bring through the app. 


if there a function that im not seeing to filter the headers?