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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (4,384)