Answered

Filter table using multiple selections in ComboBox

(0) Share

Report

Posted on by CAH2035

193

Hello,

I have a ComboBox with multiple selection of years (2018, 2019 & 2020) which come from Distinct values in my "Spending Report" table.

I am trying to filter a gallery to show projects that had spending during the selected year or years. This is my current code that does not work, since FY_ComboBox.Selected.Result is a table of values.

Filter(
 ProjectList,
 ProjectNum in Filter(
 SpendingReport,
 FYNum = FY_ComboBox.Selected.Result,
 ).ProjectValue
)

What I want my script to do is:

1. Filter SpendingReport where column FYNum is any of the selected values in FY_ComboBox

2. Show me gallery items where ProjectNum exists in the column ProjectValue of Spending Report that has been filtered.

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

eka24 20,923 on at

Like (0)

Report

Watch this video from Reza. Its solves it the 11th munites onwards.

If you like this post, give a thumbs up. Where it solved your issue, Mark as a solution

Was this reply helpful? Yes No
CAH2035 193 on at

Like (0)

Report

Could you send me the link to the video again, it was not in the post.

Was this reply helpful? Yes No
eka24 20,923 on at

Like (0)

Report

Sorry about that
https://youtu.be/44j2VRbdWjk

Was this reply helpful? Yes No
CAH2035 193 on at

Like (0)

Report

Thank you,

I see that Reza is controlling the visible property of all the controls in the gallery to determine if it appears in the gallery or not. This seems like a roundabout way to achieve the goal, and I personally don't see how the entire record hides and not just the controls for the record.

Is there a way to accomplish my goal that utilizes the Items property of the gallery? (Show records that appear in SpendingReport when it is filtered by all selected combobox values).

I am also trying to do this with two combo boxes (year and month) which I realize might make it more difficult.

Was this reply helpful? Yes No
eka24 20,923 on at

Like (0)

Report

Thanks

Was this reply helpful? Yes No
Verified answer

Mr-Dang-MSFT on at

Like (1)

Report
Hi @cah2035,

Thanks for clearly stating your goals and formatting your formula. It helps a lot.

Let's achieve one goal at a time and combine it afterwards.

(Side note, I think you have an extra comma in your original formula if this happens to be the underlying issue)

1. Filter SpendingReport where column FYNum is any of the selected values in FY_ComboBox

Filter( SpendingReport, FYNum in FY_ComboBox.SelectedItems.Result )

This means, "Filter the SpendingReport where the FYNum appears in the text of the items selected in FY_ComboBox."

2. Show me gallery items where ProjectNum exists in the column ProjectValue of Spending Report that has been filtered.

Filter( ProjectList, ProjectNum in Filter( SpendingReport, FYNum in FY_ComboBox.SelectedItems.Result ).ProjectValue )

This is identical to your original formula but I changed Selected to SelectedItems and removed the comma at the end of the line.

All of this will work, but it is definitely far from performant.

This is one filter nested into another one and will multiply the thinking that needs to be done

Both filters use the in operator which is more work than "equals", StartsWith, or EndsWith

Was this reply helpful? Yes No
CAH2035 193 on at

Like (0)

Report
Thank you! This worked perfectly. This was my first time using the in operator but I see now how it works (and why using =FYNum would not work).

For anyone else looking for this solution, below is the code I used when I needed to filter for both FYNum and FYPeriod, I just added a similar logical test on a new line and it worked as expected.

Filter( ProjectList, ProjectNum in Filter( SpendingReport, FYNum in FY_ComboBox.SelectedItems.Result, FYPeriod in Period_ComboBox.SelectedItems.Result ).ProjectValue )

Furthermore, my next step was to have a second gallery showing the items in SpendingReport for the selected now-filtered projects in ProjectList. At first I just changed around the formula above to filter SpendingReport instead of ProjectList on line 2 but this just gave me all items in SpendingReport. I realized that I did not need a nested filter this time since it was acting on the same table, so I used the code below which worked.

Filter( SpendingReport, FYNum in FY_ComboBox.SelectedItems.Result, FYPeriod in Period_ComboBox.SelectedItems.Result, ProjectValue = Value(Gallery_ProjectList.Selected.ProjectNum) )

Was this reply helpful? Yes No