Unanswered

Stuck at Level One of Multi Level Filter

(0) Share

Report

Posted on by forbudt4u

264

Hello, for context, I have submitted a couple threads (REF: https://powerusers.microsoft.com/t5/Building-Power-Apps/Collection-Build-Time/m-p/875524#M278063) with this Asset Management app I'm working with and I've gotten as far as I can go with a Multi-Level Filter I'm trying to design. Thanks to @RandyHayes' suggestions I have completely taken apart my app and redesigned a large portion of it, which has sped it up a LOT without the use of collections for the most part! Well, now that I have my admin portal humming along, I can't seem to get this filter working past the first level/selection in a series of 5 dropdown selections. Regarding my dropdowns, to avoid circular references, I had to created a variable that captured the selected values rather than using the dropdown itself, and you'll see that in my code.

Rather than explain everything, I'll let my screencaps talk. In a nutshell, during the first of any filters, the filter works fine after hitting Apply Filter.... regardless of which one I choose. However, once I attempt to filter it even further, it will not apply the filters. My Clear Filters button works and returns the gallery to it's original state.

Filter Issue.png

Here is the code I'm currently using for the Gallery. I know it has something to do with this, but being a rookie at this, I cannot wrap my head around a solution (and I've tried many things).

If(FilterCLEARBTN=true And PendingCheckbox_1.Value=false,

Sort(
 Sort(
 Sort(MasterGallery.AllItems, 
 ManufacturerBK,Ascending),
 Holder,Ascending),
CategoryBK,Ascending),

If(!IsBlank(_FilterCategoryDD),
Sort(
 Sort(
 Sort(
 Filter(MasterGallery.AllItems, 
 CategoryBK=_FilterCategoryDD),
 ManufacturerBK,Ascending),
 Holder,Ascending),
CategoryBK,Ascending)
,
If(!IsBlank(_FilterHolderDD),
Sort(
 Sort(
 Sort(
 Filter(MasterGallery.AllItems, 
 Holder=_FilterHolderDD),
 ManufacturerBK,Ascending),
 Holder,Ascending),
CategoryBK,Ascending)
,
If(!IsBlank(_FilterManufacturerDD),
Sort(
 Sort(
 Sort(
 Filter(MasterGallery.AllItems, 
 ManufacturerBK=_FilterManufacturerDD),
 ManufacturerBK,Ascending),
 Holder,Ascending),
CategoryBK,Ascending)
,
If(!IsBlank(_FilterModelDD),
Sort(
 Sort(
 Sort(
 Filter(MasterGallery.AllItems, 
 ModelBK=_FilterModelDD),
 ManufacturerBK,Ascending),
 Holder,Ascending),
CategoryBK,Ascending)
,
If(!IsBlank(_FilterStatusDD),
Sort(
 Sort(
 Sort(
 Filter(MasterGallery.AllItems, 
 Status.Value=_FilterStatusDD),
 ManufacturerBK,Ascending),
 Holder,Ascending),
CategoryBK,Ascending)
 )
 )
 )
 )
 )
)

NOTES: The FilterCLEARBTN is the variable I'm using to control applying and clearing the filters (as in my trigger). This is also just the first part to my code as I also need to have a second section that account for the PendingCheckbox_1 being true.

Any help you guys could give would be awesome... thanks!!

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (0)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@forbudt4u

I can help you with this, but can you let me know what all of the Items properties and control names are for your dropdown?

Also, for each of them, what would be the "default" value of them, blank or a particular selection?

Was this reply helpful? Yes No
forbudt4u 264 on at

Like
a
(0)

Report
Copy link

Link copied!

@RandyHayes... sure thing!

All defaults are set to blank (nothing entered in the default field).

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@forbudt4u

I am not sure then in your formula what the _names were - ex. _FilterCategoryDD

Also not sure where PendingCheckBox_1 came into the formula as you had not else condition to account for that...and thus your formula would have returned no results.

Your formula on the Gallery should be the following:

SortByColumns( Filter(MasterGallery.AllItems, (IsBlank(FilterCategoryDD.Selected.Result || CategoryBK = FilterCategoryDD.Selected.Result) && (IsBlank(FilterHolderDD.Selected.Result) || Holder = FilterHolderDD.Selected.Result) && (IsBlank(FilterManufacturerDD.Selected.Result) || ManufacturerBK = FilterManufacturerDD.Selected.Result) && (IsBlank(FilterModelDD.Selected.Result) || ModelBK=FilterModelDD.Selected.Result) && (IsBlank(FilterStatusDD.Selected.Result) || Status.Value=FilterStatusDD.Selected.Result) ), "ManufacturerBK", Ascending, "Holder", Ascending, "CategoryBK", Ascending )

BUT, I am slightly confused as to what you are applying this to. I would have assumed that MasterGallery was the gallery you showed in the pictures. In which case, you can't have the Items property refer to itself.

Was this reply helpful? Yes No
forbudt4u 264 on at

Like
a
(0)

Report
Copy link

Link copied!

@RandyHayes . First off, the formula provided is not working as it's telling I have a circular reference, the IsBlank has invalid arguments, and that the Columns at the end of the code are literal values and don't reference the input table.

Answers to your Questions:

_Names?
If you go back to my screencaps and the explanation in my original post I explain what the _names are... they are variables I set for the individual dropdown OnChange properties because I was already originally getting the circular reference errors in previous attempts at doing this myself... this was my best attempt at combatting this issue. I also tried just using the source columns (MasterGallery) as the list in my dropdowns, however, when I do this it shows ALL available choices in the columns and doesn't update the selections after a filter is applied.

PendingCheckBox_1?
Also mentioned in the original post, this is a checkbox that is selected by the Admin so any Pending Status' show at the top of the list if they want to view it that way (this is useful when they are checking assets into our tool cribs). I was attempting to break down my formula into chunks, so the code I provided was just the first half of what I need and why I used and IF Statement, because the else clause would have to deal with the Pending On Top Checkbox being selected (or true).

MasterGallery?
My MasterGallery is a gallery for standard users that already has the data loaded that I need. I'm just using it for admin purposes so I can do some advanced patching with the data outside the standard user screen. So, this gallery I'm using (SearchGallery_2) is on the Admin Portal Screen.

At the end of the day, I have the gallery I'm working with, and I just need the following...

1. It sorts correctly regardless of filters applied or boxes checked.
2. I can select one or multiple filter option and the gallery displays the results when applying the filters.
3. After each filter is applied, all dropdowns update the selections based on what's currently filtered out in the Gallery.
4. The Gallery goes back to it's original state when the filters are cleared.

My original code did everything above EXCEPT applying any filter past the first filter (and the PendingCheckBox_1 else clause). Did I mention I'm a rookie at this?😭🙉

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(2)

Report
Copy link

Link copied!

@forbudt4u

Yes...that is what I mean when I said "In which case, you can't have the Items property refer to itself."

You will get a circular reference. BUT, this is the only thing you provided to start with from your formula.

You don't need the variables. So there is no need to address them.

Sorry, missed the part of the PendingCheckBox. But, in your original formula it was part of the original If statement and there was not "else" to provide any different sorting. So we should incorporate that into the formula. In general, there should be no If statements in an Items property. So, if you could provide more of how you were handling that from your original formula, that would be good.

So...getting to the end of your day (and mine too 😉😞

1. Sorting was not accounted for in your original formula except for repetitive nested sorts. Do you want more flexibility over the sorting, or is the static sort on the three columns you had sufficient?

2. This is the intention of what we are doing. But, I need to know the datasource that we are applying this to. Your Gallery AllItems is not going to ever work as it is circular.

3. Ah...here lies a rub. You want your filters to be dynamic...this changes some things. Not a problem, just a different method.

4. This part is no problem.

No problem rookie...you're in good hands!

Let me know the datasource that you are applying this to and also the formula you had in place for the pending checkbox and I will get you to the next step.

Was this reply helpful? Yes No
forbudt4u 264 on at

Like
a
(0)

Report
Copy link

Link copied!
@RandyHayes, okay, seems Microsoft had a little bit of an outage right at the end of the day. Anyways, I'm going to try to get everything you need.

1. SearchGallery_2 is the name of the gallery I'm attempting to filter in my Admin Portal (Screen).
The original datasource is a SharePoint list and the code is below
This code monitors both a text entry field (SearchTextBox_1) and a Scan Button (SearchPopUpBarcodeScanLbl_1). Of course the Scan Button is pretty straight forward as it's a single number or blank. But, the SearchTextBox entry field filters based on quite a few columns.
If(PendingCheckbox_1.Value=false, Sort( Sort( Sort(FirstN(Filter('All Asset List', !(IsBlank(SearchTextBox_1.Text) && IsBlank(SearchPopupBarcodeScanLbl_1.Text)) && (IsBlank(SearchPopupBarcodeScanLbl_1.Text) || 'ScanTag#'=SearchPopupBarcodeScanLbl_1.Text) && (IsBlank(SearchTextBox_1.Text) || StartsWith('Transfer Status', SearchTextBox_1.Text) || StartsWith('ScanTag#', SearchTextBox_1.Text) || StartsWith(Holder, SearchTextBox_1.Text) || StartsWith(CategoryBK, SearchTextBox_1.Text) || StartsWith(ManufacturerBK, SearchTextBox_1.Text) || StartsWith('Serial#', SearchTextBox_1.Text))) ,2000),ManufacturerBK,Ascending),Holder,Ascending),CategoryBK,Ascending), Sort( Sort( Sort( Sort(FirstN(Filter('All Asset List', !(IsBlank(SearchTextBox_1.Text) && IsBlank(SearchPopupBarcodeScanLbl_1.Text)) && (IsBlank(SearchPopupBarcodeScanLbl_1.Text) || 'ScanTag#'=SearchPopupBarcodeScanLbl_1.Text) && (IsBlank(SearchTextBox_1.Text) || StartsWith('ScanTag#', SearchTextBox_1.Text) || StartsWith(Holder, SearchTextBox_1.Text) || StartsWith(CategoryBK, SearchTextBox_1.Text) || StartsWith(ManufacturerBK, SearchTextBox_1.Text) || StartsWith('Serial#', SearchTextBox_1.Text))) ,2000),ManufacturerBK,Ascending),Holder,Ascending),CategoryBK,Ascending),'Transfer Status',Descending) )
2. Sorting is pretty static across the board in every gallery I have in the app, so that will not change. The only caveat is Pending Transfer checkbox in the Admin Portal.
3. Yes, I think this is why I'm having such a rough time with this filter because it is dynamic... but it's what we need for our organization to be more efficient in a remote/field environment, primarily on mobile devices.
4. I would prefer to click the Apply Filter Button rather than the filter be a "live" result, that way it gives people a chance to undo a selection if it was by mistake.

I think that's everything as my datasource and all of the controls are listed in the code, and you already have my dropdown control info (I will of course need to know what to place in the items property, but I'm sure it will be directly from the datasource). Thanks for your guidance/assistance.

Was this reply helpful? Yes No
forbudt4u 264 on at

Like
a
(0)

Report
Copy link

Link copied!

@RandyHayes

Hi Randy, It's been a few days since I posted the answers to your questions, and I thought I would follow up. I hope all is well!

Was this reply helpful? Yes No