Answered

Filter Gallery with Multiple Conditions avoid Delegation

(1) Share

Report

Posted on by StephenGW

583

I need to filter a gallery using multiple controls independently and together. One constant filter is I only want records with an entry date. Then I need to be able to filter that with a start date picker an end date picker and a combobox. All need to work independently and together. So if nothing the start date then everything with an entry date before and after but if I add a start date then only thing equal to or greater. Same with the end date and if both are selected then in between the two. Also if the CB is empty then no filter on the category column but if selected then it filters based on that also with the date pickers. Finally to top that all off I'm trying to avoid delegation functions. Can anyone help with this?

Categories:

Building Power Apps

I have the same question (0)

All responses (19)

Answers (1)

abc 123 784 Moderator on at

Like (1)

Report

I use multiple collections to amass data for gallery display. Breaking it down to simple steps allow for debugging and helps avoid Delegation squigglies.

The first collection gets data from the list.

ClearCollect(colMyData, Filter(...whaterver));

The second collection tacks on sortable columns and calculations needed for grid functionality, including home grown security.

ClearCollect(colMyDataWithSortFields,

AddColumns(colMyData,

YearMthSort, 'Year-Month'.Value,

YearMthNext, Value(Left('Year-Month'.Value,4))+1 & Mid('Year-Month'.Value, 5),

Year, Left('Year-Month'.Value, 4),

SiteSort, Site.Value,

ChemicalSort, Chemical.Value,

AmountEmittedSort, Text('Amount Emitted'),

StatusSort, 'Workflow Status'.Value,

CanEdit, If(gcfAdminSI, "Yes", "No"),

CanAdmin, If(gcfAdminSI, "Yes", "No"),

ItemDeleted, false,

ItemEdited, false

)

);

Was this reply helpful? Yes No
StephenGW 583 on at

Like (1)

Report

ABC,

Won't a collection only work up to 2000 rows? Why the "security", what benefit does that add?

Was this reply helpful? Yes No
WarrenBelz 153,084 Most Valuable Professional on at

Like (1)

Report

Hi StephenGW,

All of that should be possible in a Delegable query in most data sources except possibly Excel (which I recall you are using - please confirm this).

Please share your control and field names/types and the logic you require.

Regarding your other comment on the 2,000 record Data Row Limit, the issue is performance, especially on mobile devices as local data needs to be stored and processed by the device, rather than (with a Delegable query) the data source server.

Was this reply helpful? Yes No
StephenGW 583 on at

Like (1)

Report

Hello again Warren,

I was able to make the SP list work for my needs with some minor modification, so I am now working in SP list. The controls are simply (I know it is bad practice but...) DatePicker1, DatePicker2, and ComboBox1. This app will likely only be used on a desktop. With all controls blank I need to have the gallery only filter results with my entry date column filled in so List, EntryDate <> Blank(). DatePicker1 is the start date = Empty = any dates, SelectedDate >=. DatePicker2 is the end date so again empty = any date, SelectedDate <=. If both are selected then the dates will need to be >=DatePicker1 AND <=DatePicker2. Also the CB is Category so empty = all categories but Selected.Value (I use With() to make the list since I am also using Distinct) = only the selected Category. All 3 need to filter or not filter together. So if one or all 3 are empty or selected then they all perform their individual filtering or not.

Was this reply helpful? Yes No
Suggested answer

Michael E. Gernaey 53,433 Super User 2025 Season 2 on at

Like (1)

Report

ouch my eyeballs..

I'm gonna let Warren finish this one... i think I'm blind.

please use carriage returns hehe

Was this reply helpful? Yes No
StephenGW 583 on at

Like (1)

Report

FLMike,

Lol, sorry. There are only bits and pieces of the code in there but yes, I am bad about using the returns.

With all controls blank I need to have the gallery only filter results with my entry date column filled in so List, EntryDate <> Blank().

DatePicker1 is the start date = Empty = any dates, SelectedDate >=. DatePicker2 is the end date so again empty = any date, SelectedDate <=. If both are selected then the dates will need to be >=DatePicker1 AND <=DatePicker2.

Also the CB is Category so empty = all categories but Selected.Value (I use With() to make the list since I am also using Distinct) = only the selected Category.

All 3 need to filter or not filter together. So if one or all 3 are empty or selected then they all perform their individual filtering or not.

Better broken out into pieces?

Was this reply helpful? Yes No
WarrenBelz 153,084 Most Valuable Professional on at

Like (1)

Report
Hi StephenGW ,

Would be something like
Filter( SPList, ( Value(DatePicker1.SelectedDate) < 1 || EntryDate >= DatePicker1.SelectedDate ) && ( Value(DatePicker2.SelectedDate) < 1 || SelectedDate >= DatePicker2.SelectedDate ) && ( Len(ComboBox1.Selected.Value) = 0 || Category.Value = ComboBox1.Selected.Value ) )

I have assumed that Category is a Choice column - also note that SelectedDate is a bad name for a field as it is a reserved word in Power Apps and can lead to ambiguity. Note you can use IsBlank() on all those controls most of the time except if they are populated and then cleared by the user - what I have works all of the time.

Lastly, I note your comment on using With() - it is import that you realise that while you can run a Delegable query inside this statement (and I use it extensively), the output that you then have available to apply the Distinct filter to is limited to your Data Row Limit. You will not however receive any Delegation warning.

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps Buy me a coffee

Was this reply helpful? Yes No
StephenGW 583 on at

Like (1)

Report

@WarrenBelz

Been a way for a while. I miscommunicated some probably. SelectedDate is not a column I was just using that in short Both date pickers are looking at the same column Date Ran. Ant the Category column is just text not a choice column called Maintenance Performed. I plugged in my list and column names where they should go in your code and it is giving me a couple errors. I can type them all out if you need? Here is your code with my list and columns plugged in.

Was this reply helpful? Yes No
WarrenBelz 153,084 Most Valuable Professional on at

Like (1)

Report

You have the wrong bracket type for the second bracket for a start.

Was this reply helpful? Yes No
StephenGW 583 on at

Like (1)

Report

Oh shoot! Ok I fixed that, and it does not appear to be filtering the way I need. It should never show anything in the gallery where 'Date of Maintenance' is blank, and it is showing many. That is the constant I need. Anything with a blank in 'Date of Maintenance' should always be filtered out and that is not happening. Also, it appears that when I choose something in the DatePickers and the CB it is not adding that filter. If I select a date in DatePicker1 it should filter to only things >= that date and it is showing blanks. When I select a date in DatePicker1_1 it should only include dates <= that date. If both are selected, then it should be equal to or in between the dates. If neither are selected it should show everything that is not blank in 'Date of Maintenance'. If CB is blank, it should show everything meeting the Date criteria and when something is selected in the CB is should only show things meeting that criteria. As always, I appreciate your time to respond.

Gallery Items:
Filter(
'Tube Mill Data',
(
Value(DatePicker1.SelectedDate) > 1 ||
'Date of Maintenance' >= DatePicker1.SelectedDate
) &&
(
Value(DatePicker1_1.SelectedDate) > 1 ||
'Date of Maintenance' >= DatePicker1_1.SelectedDate
) &&
(
Len(ComboBox1.Selected.Value) = 0 ||
'Maintenance Performed' = ComboBox1.Selected.Value
)
)

Was this reply helpful? Yes No