Unanswered

Excel filter style of independent dropdown(or combobox)

(0) Share

Report

Posted on by caligame

Hello Powerapps team,

I am trying to build an excel style filter for each drop-down(or combobox) linked with Sharepoint Data. Until the user selects any value from any dropdown, all values should be available to select on any drop-down. But, whenever the user selects data from any drop-down, it will impact other dropdowns and filter out available values. Is there any function or logic would you like to recommend to achieve this?

Please see the example below:

Let's assume we have 3 column data from the SharePoint List

Car	Type	Color
A	Sedan	Red
B	SUV	Red
C	SUV	Yellow
D	Hatchback	Blue
E	Hatchback	Black

User can select any drop-down(or Combobox) in any order from the PowerApps form:

If, user first select the "Color" as "Yellow", then user will only see "SUV" on the Type then "C" only on the Car.
If, user first select the "Color" as "Red", then user will see "Sedan", "SUV" on the Type then depends on the select of Type, user will see either "A"(If user selected Sedan) or "B"(If user selected SUV).
If, user first select the "Type" as "Hatchback", then user will see "D", "E" option if Car is next select.
If, user first select the "Type" as "Hatchback", then user select "Blue"as color, then user will only see "D" option in Car dropdown.

In summary, it is filtered down each time a new dropdown is selected.

I made a dependent drop-down (cascading) but need a new logic to achieve this.

Please advise how I can achieve this.

Thanks!

Categories:

Building Power Apps

All responses (3)

Answers (0)

Sort by

v-bofeng-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @caligame ,

Please try:

1\Set the APP's OnStart property to:

ClearCollect(CarDropdownItems,SPList.Car); ClearCollect(TypeDropdownItems,SPList.Type); ClearCollect(ColorDropdownItems,SPList.Color);

2\Run OnStart

3\Set CarDropdown's Items property to:

CarDropdownItems

4\Set TypeDropdown's Items property to:

TypeDropdownItems

5\Set ColorDropdown's Items property to:

ColorDropdownItems

6\Set theses three dropdown's OnChange property to:

ClearCollect( CarDropdownItems, Filter( SPList, (Type=TypeDropdown.Selected.Type ||IsEmpty(TypeDropdown.Selected))&& (Color =ColorDropdown.Selected.Color||IsEmpty(ColorDropdown.Selected)) ).Car ); ClearCollect( TypeDropdownItems, Filter( SPList, (Car=CarDropdown.Selected.Car ||IsEmpty(CarDropdown.Selected))&& (Color =ColorDropdown.Selected.Color ||IsEmpty(ColorDropdown.Selected)) ).Type ); ClearCollect( ColorDropdownItems, Filter( SPList, (Type=TypeDropdown.Selected.Type||IsEmpty(TypeDropdown.Selected) )&& (Car=CarDropdown.Selected.Car||IsEmpty(CarDropdown.Selected)) ).Color );

Best Regards,

Bof
caligame 38 on at

Like
a
(0)

Report
Copy link

Link copied!

@v-bofeng-msft
If saved data on the SharePoint List as 'Choice' instead of 'Text' will the function will be different?
So my current 'Car', 'Type', 'Color' saved as the 'Choice' column in the SharePoint list.
caligame 38 on at

Like
a
(0)

Report
Copy link

Link copied!
@v-bofeng-msft
Also for the last step, do I need to put each formula separately on each dropdown?
For example,

Set Car's Onchange property to:

ClearCollect( CarDropdownItems, Filter( SPList, (Type=TypeDropdown.Selected.Type ||IsEmpty(TypeDropdown.Selected))&& (Color =ColorDropdown.Selected.Color||IsEmpty(ColorDropdown.Selected)) ).Car );

Set Type's dropdown OnChange property to:

ClearCollect( TypeDropdownItems, Filter( SPList, (Car=CarDropdown.Selected.Car ||IsEmpty(CarDropdown.Selected))&& (Color =ColorDropdown.Selected.Color ||IsEmpty(ColorDropdown.Selected)) ).Type );

Set Color's dropdown Onchange property to:

ClearCollect( ColorDropdownItems, Filter( SPList, (Type=TypeDropdown.Selected.Type||IsEmpty(TypeDropdown.Selected) )&& (Car=CarDropdown.Selected.Car||IsEmpty(CarDropdown.Selected)) ).Color );

Please let me know if my understanding is correct...
I tried both "Put everything together each OnChange property" or "Put each function on separate dropdown Onchange as above" but both don't seem to work. I have no error but the dropdown list does not show me anything.