PowerApps Filter Gallery by Multiple ComboBoxes and Text Input

(0) Share

Report

Posted on by tlevine

230

Hi Community -

I have not been able to find a solution on the board that is able to work in my app. How can I combine the following filters to be used simultaneously in a single gallery?

Filter('[dbo].[Slow_Moving_Raw_Data_V2]', TextInput1.Text in 'Material')

If(!IsBlank(Brand1.Selected.Result),Filter('[dbo].[Slow_Moving_Raw_Data_V2]', true, Brand1.Selected.Result = UDCBrandName),'[dbo].[Slow_Moving_Raw_Data_V2]')

If(!IsBlank(Category1.Selected.Result),Filter('[dbo].[Slow_Moving_Raw_Data_V2]', true, Category1.Selected.Result = Category),'[dbo].[Slow_Moving_Raw_Data_V2]')

If(!IsBlank(Planner1.Selected.Result),Filter('[dbo].[Slow_Moving_Raw_Data_V2]', true, Planner1.Selected.Result = 'Planner Name'),'[dbo].[Slow_Moving_Raw_Data_V2]')

Filter('[dbo].[Slow_Moving_Raw_Data_V2]', Status1.Selected.Value = 'Review Status')

Thanks!

Categories:

Building Power Apps

I have the same question (0)

All responses (13)

Answers (0)

v-monli-msft Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Hi @tlevine ,

First of all, what are the Items in the 3 dropdown controls Brand1, Category1 and Planner1? Did you add a blank value to the options? Because if you didn't especially add a empty value to the options, dropdown controls will always have a value being selected by default.

If you didn't manually add a blank value to the dropdowns, then it is not necessary to add the IsBlank condition.

Regards,

Mona

Was this reply helpful? Yes No
tlevine 230 on at

Like (0)

Report
Copy link

Link copied!

Hi @v-monli-msft -

the values in the drop downs are distinct values from a SQL table:

Sort(Distinct('[dbo].[Slow_Moving_Raw_Data_V2]',Category),Ascending)

Thanks!

Was this reply helpful? Yes No
tlevine 230 on at

Like (0)

Report
Copy link

Link copied!

What syntax should be used to join all of the filters together in one string?

Was this reply helpful? Yes No
tlevine 230 on at

Like (0)

Report
Copy link

Link copied!

@Delid4ve - this is another issue I am facing. Since you were able to help with the last, any thoughts on this?

Was this reply helpful? Yes No
Delid4ve 795 on at

Like (1)

Report
Copy link

Link copied!

I’m not sure what your trying to do here:

If(!IsBlank(Category1.Selected.Result),Filter('[dbo].[Slow_Moving_Raw_Data_V2]', true, Category1.Selected.Result = Category),'[dbo].[Slow_Moving_Raw_Data_V2]')

If not is blank Category selected result then , filter slow moving data.. what’s the true there for??

If(!IsBlank(Category1.Selected.Result),Filter('[dbo].[Slow_Moving_Raw_Data_V2]', Category = Category1.Selected.Result)) - if you need to add an ‘else’ filter it goes between the last two brackets preceded by a comma

Was this reply helpful? Yes No
tlevine 230 on at

Like (0)

Report
Copy link

Link copied!

Hi -

What I am trying to do is to take all of the individual filters I have written and combine it into one filter that will be able to control the gallery. Essentially, I want one of the filters if selected to update the gallery, and then if another is selected to then build off the current filter.

As far as your question around why I use true in the statement, that I am not sure, I just found it somewhere in a solution along the way.

Was this reply helpful? Yes No
Delid4ve 795 on at

Like (1)

Report
Copy link

Link copied!

read the docs on the formulas you want to use so that you understand them (filter is probably the most used filter around so a good understanding of it goes along way)

I would set your gallery to A variable:
NewFilter

Then if you need to evaluate to !blank which I don’t think you need to, your going to have to write a long winded if statement on each of the on change events for each dropdown, otherwise use my previous Posts for cascading Dropdowns

On change events for each dropdown along the lines of:
Set(NewFilter,
Filter('[dbo].[Slow_Moving_Raw_Data_V2]',
TextInput1.Text in 'Material' And Brand1.Selected.Result = UDCBrandName And Category1.Selected.Result = Category And Planner1.Selected.Result = 'Planner Name') And Status1.Selected.Value = 'Review Status')

Was this reply helpful? Yes No
tlevine 230 on at

Like (0)

Report
Copy link

Link copied!

I really like this concept, I am still not getting it to work so I may need to play around with it some more.

To make sure I am understanding, my variable should be set for the gallery items, correct?

Was this reply helpful? Yes No
tlevine 230 on at

Like (0)

Report
Copy link

Link copied!

This filter is working but just making some updates so that each filter will continue to filter the gallery and not treat each individually:

Filter('[dbo].[Slow_Moving_Raw_Data_V2]', IsBlank(TextInput1.Text) Or 'Material'= TextInput1.Text Or IsEmpty(Category1.Selected)=true Or Category in Category1.Selected.Result Or IsEmpty(Planner1.Selected)=true Or 'Planner Name' in Planner1.Selected.Result Or IsEmpty(Brand1.Selected)=true Or UDCBrandName in Brand1.Selected.Result Or IsEmpty(Status1.Selected)=true Or 'Review Status' in Status1.Selected.Value)

Was this reply helpful? Yes No
Delid4ve 795 on at

Like (0)

Report
Copy link

Link copied!

So basically every time you change something ie a dropdown or combo you need to re set the variable so that the gallery updates.
The easiest way is to put the formula in every controls onchange property so when you select new values it changes it.

I don’t use combo boxes and I’m not in front of my pc at the moment so I don’t know how it would handle if a combo has nothing selected, you may need to use the in operator instead of =, and may have to use a nested if statement for isblank. This will be quite a long formula tho so you may be better off using the search function rather than filter

Was this reply helpful? Yes No