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

Filter function not recognizing "All" choice when set equal to on Gallery

Hi, All.

 

I have a dropdown populated by a collection that has the choices of All, Active, Terminated, In Process.  For some reason, the first line after the IF is not being recognized ( ddEEStatusFilter.Selected.Result = "All"), but the other choices work for the second part of the If statement (ddEEStatusFilter.Selected.Result <> "All").  This will end up being a much larger conditional statement for each of those options plus more.  I have done this before and do not see any difference in this and my other apps.  Please let me borrow your eyes and brains for a moment.  Any help is appreciated.  Thank you!

SortByColumns(
    Search(
        If(
            ddEEStatusFilter.Selected.Result = "All",
            Filter(
                EmpDB,
                'EE Status'.Value = ddEEStatusFilter.Selected.Result
            ),
            ddEEStatusFilter.Selected.Result <> "All",
            Filter(
                EmpDB,
                'EE Status'.Value = ddEEStatusFilter.Selected.Result
            )
        ),
        txtEmpSearch.Text,
        "Title",
        "First_x0020_Name",
        "Last_x0020_Name"
    ),
    "Last_x0020_Name"
)

 

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@GoGorilla 

No problem.  Sorry to flub it in the first formula.  Glad all is working for you now.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

9 REPLIES 9
RandyHayes
Super User
Super User

@GoGorilla 

If this is a static set of value for the dropdown, then set your Items property of the Dropdown to:

   ["All", "Active", "Terminated", "In Process"]

 

Then remove references to Selected.Result in your formula and change to Selected.Value.

 

I hope this is helpful for you.

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

@RandyHayes The Items property of the dropdown is set to the collection.  The other choices "Active", "Terminated", and "In Process" are working.  In other words, the condition ddEEStatusFilter.Selected.Result <> "All" IS working.  It is when I set it to equal that "All" does not work.

RandyHayes
Super User
Super User

@GoGorilla 

What is your collection definition?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
GoGorilla
Helper III
Helper III

This is how my collection is defined:

ClearCollect(
    collectEmpStatus,
    {Result: "All"}
);
Collect(
    collectEmpStatus,
    Sort(
        Distinct(
            EmpDB,
            EE_x0020_Status.Value
        ),
        Result,
        Ascending
    )
);
RandyHayes
Super User
Super User

@GoGorilla 

I should have focused more on your original formula rather than just the condition statement.  Your problem is you have the exact same filter formula in both conditions.  So, unless you have an "All" in your EE Status column, you will get no results when the selection is "All".  (perhaps this is where you needed to borrow some eyes!)

 

Change your formula to the following:

SortByColumns(
    Search(
        Filter(
            EmpDB,
            (ddEEStatusFilter.Selected.Result = "All" || 'EE Status'.Value = ddEEStatusFilter.Selected.Result)
        ),
        txtEmpSearch.Text,
        "Title",
        "First_x0020_Name",
        "Last_x0020_Name"
    ),
    "Last_x0020_Name"
)

This will give you the correct results.  (Note: try to avoid using two or more filter statements in a formula)

 

Also, you can skip the collection for your ddEEStatusFilter by setting the Items property to:

With({_items: Sort(Distinct(EmpDB, EE_x0020_Status.Value), Result)},
    ForAll(Sequence(CountRows(_items)+1, 0),
        {Result: If(Value=0, "All", Last(FirstN(_items, Value)))}
    )
)

Just in case you'd like to cut down on the collection overhead in your app. 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

The first formula worked great!  I understand it and appreciate your help.  The formula for the Items property for ddEEStatusFilter is throwing an error:

 

GoGorilla_0-1613147728925.png

The Result line is where the error is.  I assume the Record is _items and it is pulling in the place value, e.g. 0,1,2, but where Value is in the FirstN function, a number is expected.  Basically, if Value of EE_x0020_Status is 0 then display "All" else display the first set of records from items that equal the Value (1,2, etc.) and the last record?

RandyHayes
Super User
Super User

@GoGorilla 

Oops, sorry...was typing it freehand.  Should be:

With({_items: Sort(Distinct(EmpDB, EE_x0020_Status.Value), Result)},
    ForAll(Sequence(CountRows(_items)+1, 0),
        {Result: If(Value=0, "All", Last(FirstN(_items, Value)).Result )}
    )
)
_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
GoGorilla
Helper III
Helper III

@RandyHayes Wow! Thank you.  That is something I should have figured out...You are awesome!

RandyHayes
Super User
Super User

@GoGorilla 

No problem.  Sorry to flub it in the first formula.  Glad all is working for you now.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (1,976)