Unanswered

Filter contents of a combo box based on the selection within another combo box

(0) Share

Report

Posted on by WayneSS

I've been searching the web for a solution to something that's very common. And each solution I come across is slightly different and never quite works for me. So, hopefully if explain everything here, one of you helpful folk with be able to rescue me.

I want to filter employees by their respective departments/teams/sub teams.

I have a data source which is a table created in Dataverse for Teams called, StaffStructures with the following columns:

Staff_Name (text)
Staff_Email (email)
Department (Choices)
Team (Choices)
Sub_Team (Choices)
Managers (Yes/No)

I have four combo boxes:

Filter_Department with Items = Choices('Department (StaffStructures)')
Filter_Team with Items = Choices('Team (StaffStructures)')
Filter_SubTeam with Items = Choices('Sub_Team (StaffStructures)')
Filter_Employee with Items = StaffStructures.Staff_Name

I also have a Gallery which is connected to a different data source, called WaSS_LeaveRequests and within that gallery is a button which shows an employees initials. The Visible property of the button = ThisItem.Name in Filter_Employee.SelectedItems so when an employees name is selected in the Filter_Employee combo box their name will appear in the gallery.

The part I'm struggling with is: I want to filter the employees within Filter_Employee by their respective Departments, Teams and Sub Teams. Any help would be much appreciated.

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (0)

Ami K 15,679 Super User 2024 Season 1 on at

Like (0)

Report

@WayneSS - difficult to visualise what you're trying to do. Could you please share a screenshot of your Gallery so I have better context.

Was this reply helpful? Yes No
WayneSS 61 on at

Like (0)

Report

Thanks for replying Amik, I've attached two screenshots of my drop down boxes (combo boxes). As you'll see, I can show all employees but I'm struggling to filter them by respective departments, teams and sub steams.

Screenshot-02.png

Screenshot-01.png

Was this reply helpful? Yes No
Ami K 15,679 Super User 2024 Season 1 on at

Like (1)

Report
@WayneSS - try applying the below on the Items property of that ComboBox:

Filter( StaffStructures, Len(Filter_Department.Selected.Value) = 0 || Department = Filter_Department.Selected.Value, Len(Filter_Team.Selected.Value) = 0 || Team = Filter_Team.Selected.Value, Len(Filter_SubTeam.Selected.Value) = 0 || Sub_Team = Filter_SubTeam.Selected.Value )

Was this reply helpful? Yes No
WayneSS 61 on at

Like (0)

Report

Thanks Amik, but I'm getting this error:
This error keeps popping up no matter which way I write the code. I'm starting to think there's a bug in Powerapps for Teams.

Was this reply helpful? Yes No
WayneSS 61 on at

Like (0)

Report

I've only just noticed this message when you hover over the code within Items for the combo boxes:

Has anyone seen this before and know what it means? I can't even read the full message as it cuts off!!!

Was this reply helpful? Yes No
WayneSS 61 on at

Like (0)

Report
I'm making progress. I've got the cascading combo boxes working. It was a problem with the data I was using. It only works with TEXT it doesn't like CHOICES, so I had to manually recreate the choice columns and input text values instead (which was annoying). Now @Amik's code works, yay! Thank you Amik.

However, there are still some issues with this method. I have Multi-select enabled, but when filtering results it only shows the most resent option selected and ignores the others. See screenshot below:

UXSD Team is selected, but it's only showing the member of the Solution Design Team.

Another issue is; not all employees are in a Sub Team, so their Sub Team field is blank. I think this is effecting the visibility if the employee within my calendar because when they have a Sub Team they appear, when they don't have a Sub Team they don't appear. This is the code I'm using on the button (employee initials) within my gallery (calendar)
Visible =
ThisItem.Name in Filter_Employee.SelectedItems.Staff_Name
Screenshot of the filter menus

Screenshot of calendar

Was this reply helpful? Yes No
WayneSS 61 on at

Like (0)

Report
I've fixed the issue of only showing he most recent option selected and not all options selected, I tweaked the code from this:
Distinct(Filter(StaffStructures, TeamTxt = Filter_Team.Selected.Value),SubTeam)

to this:
Distinct(Filter(StaffStructures, TeamTxt in Filter_Team.SelectedItems.Value),SubTeam)

Notice '=' changed to 'in' and 'Selected.Value' changed to 'SelectedItems.Value'

So it now looks in all selected items. 😁

Was this reply helpful? Yes No
Ami K 15,679 Super User 2024 Season 1 on at

Like (0)

Report
@WayneSS - you have posted multiple replies here, so I will just respond to what I think is relevant.

The message "this data type is unsupported for evaluation" just means that a complex data type such as Choices type cannot be displayed in the preview pane (it is not an error).

I do not understand what you mean when you say "it only works with TEXT it doesn't like CHOICES". I do not understand what you have done to resolve the problem with your data source, but glad the solution I provided is now working.

Based on that screenshot, if all three ComboBoxes controls have been configured to be multi-select (but the Choice fields in your data source are configured to be single-select), you can use the below:

Filter( StaffStructures, CountRows(Filter_Department.SelectedItems) = 0 || IsBlank(Filter_Department.SelectedItems) || Department in Filter_Department.SelectedItems, CountRows(Filter_Team.SelectedItems) = 0 || IsBlank(Filter_Team.SelectedItems) || Team in Filter_Team.SelectedItems, CountRows(Filter_SubTeam.SelectedItems) = 0 || IsBlank(Filter_SubTeam.SelectedItems) || Sub_Team in Filter_SubTeam.SelectedItems )

Was this reply helpful? Yes No