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:
Overview:
- Create a gallery or data table
- Specify the data source and relevant columns
- Create collections to store the search options
- Create drop down boxes at the top of the columns for the user to select from.
- Add the filter formula for the gallery or data table.items
- Other considerations
- 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:
Comments
-
Filtering multiple column headers in PowerApps
hello
is there any way of filtering the table headers them selves not just the content?Product ID
Tom mike josh Camera 1
2 100 5 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 ..
Name Product Quantity tom camera 1 2 mike Camera 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? -
Filtering multiple column headers in PowerApps
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!
-
Filtering multiple column headers in PowerApps
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)
-
Filtering multiple column headers in PowerApps
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
-
Filtering multiple column headers in PowerApps
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?
-
Filtering multiple column headers in PowerApps
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!
*This post is locked for comments