Add "All" Option to a Choices Dropdown List Connected to a SharePoint List That Will Enhance The Filtering of a Gallery

[mkusler]

What I am attempting to do, is add an “All” option to my “ActionDropdownActive” dropdown list, however all I am getting a blank space added to the dropdown, but it doesn’t do anything beyond that. Here is my current code.

ListScreenActive

OnVisible:

ClearCollect(CollectAction, {Value: "All"});

Collect(CollectAction, Choices('Add-Short Issue/Resolution Tracking'.Action))

GalleryActive

Items:

SortByColumns(Search(Filter('Add-Short Issue/Resolution Tracking', Status.Value <> "Closed" && Action.Value = ActionDropdownActive.Selected.Value), TextInputPnActive.Text, "PartNumber"), "Created",Descending)

ActionDropdownActive

Items:

CollectAction

This is what I see...any clue what I am doing wrong?

[RandyHayes]

@mkusler 

Please have a look at this video on adding an "All" to your choices.

I hope this is helpful for you.

[mkusler]

I got the dropdown to work, but I think there are errors in my Gallery Items properties. It still doesn't show anything when  "All" is selected and when I make other selections its a random grouping of "Action" types.

I have never used so many functions before, can you take a look at what I'm doing wrong?

SortByColumns(
Search(
Filter('Add-Short Issue/Resolution Tracking',Status.Value <> "Closed" && Action.Value = ActionDropdownActive.Selected.Value = "All" || Action.Value = ActionDropdownActive.Selected.Value),
TextInputPnActive.Text, "PartNumber"),
"Created",Descending)

Thanks!

[RandyHayes · Accepted solution]

@mkusler 

Your problem is that you are not grouping your AND's and OR's.  When PowerApps tries to read that formula, is it that the first criteria is and'ed with the second and then all of that is or'ed with the third, or is it that the 1st is And'ed with the results of the 2nd and 3rd Or'ed?

Use parenthesis to make it abundantly clear to the app.

I believe you are looking for this:

SortByColumns(
    Search(
        Filter('Add-Short Issue/Resolution Tracking',
            Status.Value <> "Closed" && 
            ((ActionDropdownActive.Selected.Value = "All") || Action.Value = ActionDropdownActive.Selected.Value)
        ),
        TextInputPnActive.Text, "PartNumber"
    ),
    "Created",
    Descending
)

[mkusler]

@RandyHayes 

I see what I did wrong! I guess I need to think back to my old Algebra classes and the order of operations. Having said that, is there any reason one would put a certain function before another? For example, I'm starting with the SortByColumns, but what if I did it last? I know that my code would be structured differently, but are there any other impacts?

[RandyHayes]

@mkusler 

Yes, the order of operations you learned in your prior days is MOST effective in PowerApps!  And you wondered...where in life would I ever need to remember this stuff when you were learning it - now you know! 😂

Actually, your SortByColumns is LAST in your formula.  You have to read your formulas from the inside out in many cases.

Yes, there are impacts depending on how you build your Formula...read on...

So in that formula, the Filter function is First - it returns a Table.  That table is then the first parameter of the Search function.  The Search function then returns a table.  That table is then the first parameter for the SortByColumns function - which is the last thing done in the formula.

It is always best to build the formula always thinking about that process above...what "feed" what.  And then work from the perspective of "narrowing".  Your Filter does the first "big" narrow and will return the smaller amount of records in its table because of the criteria.  That smaller table then goes to the Search function which then narrows it down further - and, because it is working with the Subset of data from the Filter, it is working on much less data to do the search (which is operationally more expensive in terms of performance than the filter).  THEN, the SortByColumns is given an even smaller set of data to sort.  It is last because, there really isn't any sense in sorting records to then narrow them...it would be a waste of sort processing time.

Every need and formula is different, so, the above is not a "hardened process", it has to be evaluated based on every design and requirement.  (example: there are times that you want to narrow on "most recent" items...that involves a sort prior to narrow)

Hopefully that is clear and helpful for you.

Edit: Oh...and thank you so much for the coffee!!  ☕  I do go through a lot of the stuff!

[mkusler]

Thank you for the additional information and context! And....you are most welcome!

[William_Azevedo]

Thank you for that!

[Anonymous]

Thanks for your solution. Here is the same for a SP People Field With ({lclChoices: Sort(ForAll('MySharePointList',ThisRecord.MySharePointPeopleField),DisplayName)}, ForAll( Sequence(CountRows(lclChoices)+1), {Value: If(Value=1 ,"All" ,(Last(FirstN(lclChoices, Value-1)).DisplayName) )} ) )