Cascading dropdown filter

(1) Share

Report

Posted on by NicoPet

Hello,

I would like to ask if there are anyone who knows how i should update my current code (See below). I need it to function so that whenever i have selected "All" in my company dropdown, then my department dropdown needs to only return "All" or "None", whereas right now it does return those two however it also returns all other departments which makes for a very long list. The second part of my code where it filters the departments whenever a specific company is selected is working as intended but its the first part that i would like some advice one.

Thank you!

If(

IsBlank(Dropdown3_1.Selected.Company),

Sort(colDeptSplit, Dept, SortOrder.Descending),

If(

Dropdown3_1.Selected.Company = "All",

Filter(

colDeptSplit,

Dept = "All" || Dept = "None"

),

Ungroup(

Table(

{Data: Table({Dept: "None"})},

{

Data: Sort(

Filter(

colDeptSplit,

Company = Dropdown3_1.Selected.Company

),

Dept,

SortOrder.Ascending

)

}

),

Data

)

)

)

Categories:

Error handling

Building Power Apps

I have the same question (0)

All responses (7)

Answers (0)

Sort by

Suggested answer

Haque 3,653 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @NicoPet

Before everyting, I would request when you paste code - please use "Insert Code Snippet" for better readability.

If I understood correctly, when Dropdown3_1.Selected.Company = "All", seems like you’re still filtering against colDeptSplit, which means it’s pulling every department that matches "All" or "None". Basically, "All" isn’t a department really in the dataset, the filter ends up returning everything plus "None".

Let's try this block

If( IsBlank(Dropdown3_1.Selected.Company), Sort(colDeptSplit, Dept, SortOrder.Descending), If( Dropdown3_1.Selected.Company = "All", Table( {Dept: "All"}, {Dept: "None"} ), Ungroup( Table( {Data: Table({Dept: "None"})}, { Data: Sort( Filter( colDeptSplit, Company = Dropdown3_1.Selected.Company ), Dept, SortOrder.Ascending ) } ), Data ) ) )

Where Table({Dept: "All"}, {Dept: "None"}) - will return only those two values, no reference to colDeptSplit. This will ensures your Department dropdown shows exactly "All" and "None" when the Company dropdown is "All"

Filter(colDeptSplit, Dept = "All" || Dept = "None") ---> Table({Dept:"All"}, {Dept:"None"}) did the trick.

I am sure some clues I tried to give. If these clues help to resolve the issue brought you by here, please don't forget to check the box Does this answer your question? At the same time, I am pretty sure you have liked the response!

Was this reply helpful? Yes No
WarrenBelz 155,838 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @NicoPet,

Firstly, @Haque may be correct here and I am reading this incorrectly - I am assuming that "All" and "None" are actual values in your field Dept. I am also allowing for "None" to be added to your final Table only when there is a value selected in Dropdown3_1 (which seems to be the intent of your code). The other thing suggested is a different condensed structure that may be useful.
With( { _Data: Sort( Filter( colDeptSplit, Len(Dropdown3_1.Selected.Company) = 0 || ( Dropdown3_1.Selected.Company = "All" && (Dept = "All" || Dept = "None") ) || Company = Dropdown3_1.Selected.Company ), Dept, SortOrder.Ascending ) }, If( Len(Dropdown3_1.Selected.Company) = 0, _Data, Table( {Dept: "None"}, _Data ) ) )

Please ✅ 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 answering Yes to Was this reply helpful? or give it a Like ♥
Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No
NicoPet 35 on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz
@Haque

Neither solutions worked, let me try to clarify. If the selection in Company is "All" then it needs to populate the dropdown for Department with "All" and "None" and filter out any other value. If i have selected a specific company in the dropdown, then it needs to filter only departments that are related and add "None" option (as mentioned this part already works as intended).

Was this reply helpful? Yes No
WarrenBelz 155,838 Most Valuable Professional on at

Like
a
(2)

Report
Copy link

Link copied!
Hi @NicoPet,

Can you please clarify "worked" - I ran my code through a model and got the following: -

When nothing selected in the drop-down, all records are displayed. This is my full test dataset with Company on the left, Dept on the right

When All selected in the Drop-down, all records where the Dept was All or None were produced as well as an item at the top with None in the Dept field (I also added it in the Company field for testing).

When another Dept was selected, all records for that Dept as well as the extra item above

What in the above is not to the requirements you posted ?

Please ✅ 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 answering Yes to Was this reply helpful? or give it a Like ♥
Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No
NicoPet 35 on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz

Thank you replying, I think the issue lies in that i dont have a department called "All", the "All" option is a constructed value that i only need if the company selection is All. Currently the code i am using does in fact return "All" however it also return every other department because i have not selected a specific company to filter by

Was this reply helpful? Yes No
WarrenBelz 155,838 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @NicoPet,

OK - I am a little unclear now - if you do not have a value in Dept called All, what exactly do you want returned when All is selected ?

Was this reply helpful? Yes No
WarrenBelz 155,838 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @NicoPet,

A quick follow-up to see if you received the answer you were looking for. Happy to assist further if not.

Please ✅ 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 answering Yes to Was this reply helpful? or give it a Like ♥
Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No