Filters – combining multiple criteria
Filters use various criteria to “dissect” a data source returning the records required, generally for a gallery, collection or other data gathering exercise.
Simple Filters will generally refer to a Control (drop-down/combo box) where the selected value needs to match a field in the data source.
In this exercise, the model below is based on a simple data source (a list of PC Devices) with two combo box controls and a text input. All affect the output in the gallery below. The list is sorted by Model Name and completely unfiltered looks like this
The first Combo Box is from another list of Manufacturers, with the maker’s name also contained in the Device list.
Manufacturers.TitleThe second is a Distinct list of Device Type from Devices. The Filter is to ensure that the Manufacturer has that type of Device.
Sort(
Distinct(
Filter(
Devices,
ManufacturerName = cbMan.Selected.Title
),
DeviceType
),
Value
)
The third control is a Text input and the controls are as below with the options
Now for some basics – we want to show all Devices from a Manufacturer. I have called the Combo Box cbMan and as its items above refer to the Title of that list, this is needed.
Filter(
Devices,
ManufacturerName = cbMan.Selected.Title
)
Now we want to see only one type of machine, so we look at the second dropdown. Here we use the And() or && connector as we want to filter by both criteria. As the second Combo Box was a Distinct Filter, the output as below is .Result
Filter(
Devices,
ManufacturerName = cbMan.Selected.Title &&
DeviceType = cbType.Selected.Value
)
This produces a much smaller list of all Acer Laptops
Now to the text box. I will avoid the Delegation issues with Search or in Filters here (but the principal is the same as far as the structure goes) and use StartsWith() to look for a particular item.
Filter(
Devices,
ManufacturerName = cbMan.Selected.Title &&
DeviceType = cbType.Selected.Value &&
StartsWith(
Model,
txtName.Text
)
)
So we get all Laptops starting with A
So far fairly straight-forward, but what if we wanted to throw in an Or() statement – what if we wanted all Acer Laptops OR any PC starting with T – note the brackets here – it is important that and and/or combinations are properly bracketed – the filter
Filter(
Devices,
(
ManufacturerName = cbMan.Selected.Title &&
DeviceType = cbType.Selected.Value
) ||
StartsWith(
Model,
txtName.Text
)
)
Produces this (all Acer Laptops plus any other devices from any brand starting with T)
And this (shifting the brackets)
Filter(
Devices,
ManufacturerName = cbMan.Selected.Title &&
(
DeviceType = cbType.Selected.Result ||
StartsWith(
Model,
txtName.Text
)
)
)
Produces this (everything from Acer that is either a laptop OR starts with T).
So the importance of proper structure is paramount.
Now to the more complex bits as currently, if nothing is selected, no results are shown, but generally it is required that ALL records are displayed and then filtered according to the selections. I will start first by a simple filter that will be refined as we go, so starting with the Manufacturer only.
If(
IsBlank(cbMan.Selected.Title),
Devices,
Filter(
Devices,
ManufacturerName = cbMan.Selected.Title
)
)
This works fine and simply presents all records if there is nothing in the Combo Box, but we have three controls to consider, so the “combinations” of the three being blank would produce a large number of combinations and a very bulky filter.
It is here we can take advantage of a true/false statement which in its simplest form takes this structure.
Filter(
Devices,
If(
IsBlank(cbMan.Selected.Title),
true,
ManufacturerName = cbMan.Selected.Title
) &&
If(
IsBlank(cbType.Selected.Value),
true,
DeviceType = cbType.Selected.Value
) &&
If(
IsBlank(txtName.Text),
true,
StartsWith(
Model,
txtName.Text
)
)
)
You will get a Delegation warning here, but that will be cleaned up next. What the code is saying is that if the control is empty, return true (everything is true, so do not filter on that part and show all records, then move on to the next filter).
In the next improvement, I will also include what I use on Combo Boxes as IsBlank() does not always work. Here we take advantage of the shortened true/false inference using the Or() || filter
Filter(
Devices,
(
Len(cbMan.Selected.Title) = 0 ||
ManufacturerName = cbMan.Selected.Title
) &&
(
Len(cbType.Selected.Result) = 0 ||
DeviceType = cbType.Selected.Value
) &&
(
IsBlank(txtName.Text) ||
StartsWith(
Model,
txtName.Text
)
)
)
This is essentially the same statement as the one above, but (for example) this statement: –
Len(cbMan.Selected.Title) = 0 || ManufacturerName = cbMan.Selected.Titleis saying either
- the length of the selected item is zero (it is blank) OR
- what is selected matches the Manufacturer Name
In the first case, this is true, so (as before), no filter is applied. In the second case (the OR), filter as per the match.
Lastly, if you are going to Sort, “wrap” this around the code
Sort(
Filter(
Devices,
(
Len(cbMan.Selected.Title) = 0 ||
ManufacturerName = cbMan.Selected.Title
) &&
(
Len(cbType.Selected.Result) = 0 ||
DeviceType = cbType.Selected.Value
) &&
(
IsBlank(txtName.Text) ||
StartsWith(
Model,
txtName.Text
)
)
),
Model
)
I hope this has been useful to you in understanding filtering of your data.
14 Comments
Benjamin J Donahue
Hello Warren,
This approach to the solution is elegant beyond words. I was using a case-by-case approach, which expands exponentially for each filter, so, 2 cases for 1 filter, 4 cases for 2 filters, 8 cases for 3 filters, and so on.
Long story short, I replaced this, fully funtional code:
If(
And(
IsBlank(ListDD.Selected.Value),
IsBlank(DrugNameInput.Text)
),
Sort(
colMasterList,
Title
),
If(
And(
Not(IsBlank(ListDD.Selected.Value)),
IsBlank(DrugNameInput.Text)
),
Sort(
Filter(
colMasterList,
ListDD.Selected.Value = field_List
),
Title
),
If(
And(
IsBlank(ListDD.Selected.Value),
Not(IsBlank(DrugNameInput.Text))
),
Sort(
Filter(
colMasterList,
DrugNameInput.Text in Title
),
Title
),
If(
And(
Not(IsBlank(ListDD.Selected.Value)),
Not(IsBlank(DrugNameInput.Text))
),
Sort(
Filter(
Filter(
colMasterList,
DrugNameInput.Text in Title
),
ListDD.Selected.Value = field_List
),
Title
)
)
)
)
)
With this fuly functional code:
Sort(
Filter(
colMasterList,
If(
IsBlank(DrugNameInput.Text),
true,
DrugNameInput.Text in Title
) && If(
IsBlank(ListDD.Selected.Value),
true,
ListDD.Selected.Value = field_List
)
),
Title
)
Very very excellent.
Thank You Waren,
BenDonahue (from Power Apps forum, where I found your website)
Warren Belz
Thanks Ben,
You can shorten that a bit further
Sort(Filter(
colMasterList,
(IsBlank(DrugNameInput.Text) || DrugNameInput.Text in Title) &&
(IsBlank(ListDD.Selected.Value) || ListDD.Selected.Value = field_List)
),
Title
)
Ryan Latham
Perfect, simple code. Exactly what I needed, thanks!
Vijay
Thanks for this very clear explanation on filtering. It helped my understanding of this functionality a lot.
Eddie
Cheers mate, reduced my gallery filter from 80 lines of code down to 20 🙂
Johanna Perez
Thanks for this!!! it really helped me.
Akin
Wow, really thank you for this tutorial ! It really helped !
Jd
Hello Warren,
Does this work for Multple value columns as well?
I’m tring to do the same, but my “manufacturers” column is a multiple value column in my “Master List”. I only i want people to select one value.
Warren Belz
Yes it will work for any combination, however you will need to be aware of Delegation issues when you start filtering multi-value columns as you need the in filter, which is not Delegable in SharePoint (if that is what you are using)
Amber
How would I be able to maybe apply to a scenario where I have 2 drop downs, and a text search with “all” as an option on the drop downs? I’d like to be able to search either “all” or either drop down to have some value or the text box.
Current set up is with a single drop down and a text search box :
If(
dpcolKanbanStatus.Selected.Value = “All”,
Search(
HRBI_ReportInventory1,
txtSearch_1.Text,
ShortName,
Description,
Index,
‘HRBI Notes’,
Notes,
‘Report Name’
),
Sort(
Filter(
HRBI_ReportInventory1,
(Kanban.Value = dpcolKanbanStatus.Selected.Value)
),
ShortName,
SortOrder.Ascending
)
)
I have an additional drop down “dpStatus” I’d like to apply with the ALL feature. (List is under delegation limit)
Warren Belz
Not sure exactly what you want here, but this will combine the two functions with “All” as an option in the drop-downs – I hace assume the second field name
Search(
Sort(
Filter(
HRBI_ReportInventory1,
(
dpcolKanbanStatus.Selected.Value ="All" ||
Kanban.Value = dpcolKanbanStatus.Selected.Value
) &&
(
dpStatus.Selected.Value ="All" ||
Status.Value dpStatus.Selected.Value
)
),
ShortName,
SortOrder.Ascending
),
txtSearch_1.Text,
ShortName,
Description,
Index,
‘HRBI Notes’,
Notes,
‘Report Name’
)
Jamar Louis
Hey,
Super nice blog! Maybe stupid question but I’ve been trying lately to not rely on nested conditional statements and give the user to possibility to choose on which column he wants to filter on.
All columns of the SP list are texts, I retrieve the names of columns at start of app then provide them in dropdown, along with a text input.
So far I couldn’t make any filter or search function work that way.
Would you know a way to do this?
Warren Belz
You cannot refer to columns dynamically, so what you are proposing would need to be hard-coded.
vilabet4d
Hi there! I just want to give an enormous thumbs up for the great info you’ve here on this post. I will probably be coming back to your weblog for more soon.