cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
marcy_pi
Helper III
Helper III

Gallery filtering wrong with dropdown boxes + collections

Hello All, 

 

I have created 2 collections: colPartNumber and colMonth in OnVisible (Screen). I wanted to use them in filter boxes to see option "All". Filters should show option "All" and single distinct record from gallery in dropdown boxes. My gallery's source is excel file. I have no errors on formulas but gallery is filtering wrong.

 

On Dropdown1 where I have collection of all part numbers ("All") and distinct part number I can see correct information so showing me data but does not work on gallery so gallery is not filtering at all - nothing happens when I press part number or "All". 

 

On Dropdown2_4 where I have collection of all months ("All") and distinct month I cannot see anything when press dropdown box only blank lines. I press any blank line and it's working for instance I pressed second blank line and gallery filters through 1st month but for some reason doesn't work with "All" option as it should be the first line. 

 

Code for collections (Screen -> OnVisible):

ClearCollect(colPartNumber, {Result:"All"}, Distinct(Table10, 'PART NUMBER'));
ClearCollect(colMonth, {Result:"All"}, Distinct(Table10, month1));
Collect(colPartNumber, Distinct(Table10, 'PART NUMBER'));
Collect(colMonth, Distinct(Table10, month1))

 

Code for Gallery (Items):

If(
Dropdown1.Selected.Result="All" &&
Dropdown2_4.Selected.Result="All",
Filter(Table10, 'PART NUMBER'=Dropdown1.Selected.Result),
Filter(Table10, month1=Dropdown2_4.Selected.Result)
)

 

Codes for Dropdown boxes (Items):

colPartNumber

colMonth

 

Any ideas anyone, please? 

Thank You!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
jcfDaniel
Super User
Super User

This should do the trick (replace the names of the controls):

 

If(
    Dropdown_PartNumber.SelectedText.Value = "All" && Dropdown_Month.SelectedText.Value = "All",
    Table,
    If(
        Dropdown_PartNumber.SelectedText.Value <> "All"  && Dropdown_Month.SelectedText.Value = "All",
        Filter( Table, PartNumber = Dropdown_PartNumber.SelectedText.Value ),
        If(
            Dropdown_PartNumber.SelectedText.Value <> "All"  && Dropdown_Month.SelectedText.Value <> "All",
            Filter( Table, PartNumber = Dropdown_PartNumber.SelectedText.Value && Month = Dropdown_Month.SelectedText.Value ),
            Filter( Table, Month = Dropdown_Month.SelectedText.Value )
        )
    )
)

Regards. JD.

View solution in original post

17 REPLIES 17
anibra
Resolver II
Resolver II

Hi @marcy_pi ,

if you want to show all the results of you table you don't need a filter. 

If(
Dropdown1.Selected.Result="All" &&
Dropdown2_4.Selected.Result="All",
Table10
)

 

marcy_pi
Helper III
Helper III

Thank You but it still doesn't work on my gallery. 

anibra
Resolver II
Resolver II

Are you getting any error?

marcy_pi
Helper III
Helper III

No there is no error, I just can't see any data in my gallery now. I can see options in Dropdown1, still blank lines in Dropdown2_4. Nothing in gallery. 

anibra
Resolver II
Resolver II

In screen onVisible you need only Clearcollect()

Code for collections (Screen -> OnVisible):

ClearCollect(colPartNumber, {Result:"All"}, Distinct(Table10, 'PART NUMBER'));
ClearCollect(colMonth, {Result:"All"}, Distinct(Table10, month1));

marcy_pi
Helper III
Helper III

Ok thank You now the option "All" is working but when I choose any other distinct value from Dropdowns different than "All" it just shows me nothing in the gallery - blank. 

anibra
Resolver II
Resolver II

Than the items of the gallery should look like this:

 

If(
Dropdown1.Selected.Result="All" &&
Dropdown2_4.Selected.Result="All",
Table10,
Dropdown1.Selected.Result <>"All",
Filter(Table10, 'PART NUMBER'=Dropdown1.Selected.'PART NUMBER'),
Dropdown2_4.Selected.Result<>"All",
Filter(Table10, month1=Dropdown2_4.Selected.month1)
)

jcfDaniel
Super User
Super User

Hi, try Dropdown1.SelectedText instead of Dropdown1.Selected

 

Also, test these statements in labels, to make sure they return what you expect.


Regards. JD.

Thank You! It's working almost ok... 

 

Dropdown1 - when I choose any option it filters gallery fine. 

Dropdown2_4 - when I choose any month it's working but only when Dropdown1 in selected on "All". If I pick any other number in Dropdown1 like P611252 and lets say month number 3, it will show me this below (so it won't show me all lines for month 3 but it will show me all lines for month 1 and 2): 

marcy_pi_0-1654607466015.png

 

...also as You can see the values for month are there but not visible... while I have Visibility turned on and the text colour is black on white background. 

 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,638)