Sort, Filter, Search Syntax

[tagustin2020]

Hello,

@mdevaney  Matthew Devaney wrote a terrific blog post on sorting columns that I am trying to implement for 3 text columns in my app. I'm having trouble incorporating it into my existing Gallery Items formula and was wondering if I could get a little help nailing down the proper syntax. 

I've attached screenshots of the "before" formula that works, my broken attempt to incorporate Matthew's Switch formula and a color-coded version of how I'm trying to make sense of what goes where when you are trying to Sort, SortbyColumns, Search and Filter a gallery all at once.

Matthew's solution is exactly what I was looking for as I couldn't figure out how to make the chevron's go up and down independently so I would love to figure this out. Thanks in advance for your help. Sometimes I feel like I am never going to get all the PowerApps intricacies down, but it's so cool I have to keep trying.

Regards,

Teresa

[WarrenBelz]

Just a small addition to the great work by @RandyHayes and @mdevaney that may be useful to you in the future (and I see Randy has used a With() statement as well), Matt is correct regarding the 2,000 records, but (for your benefit Theresa), it will return the first 2,000 matching records in the statement after filtering.

I am glad you got this sorted.

[tagustin2020]

Thanks Warren. I really appreciate the help of all 3 of you. I don't have it working yet, but I'll try Randy's latest suggestion and let you know how it all works out. Teresa

[RandyHayes]

And by the way...not trying to steal or crash a thread here.  I just happened to see @tagustin2020 in the list of posts and had worked with her in the past, so I thought I would add some to the conversation.

I felt bad that I have supplied formulas lately that either have some syntax issue or are missing something - only because we end up typing them freehand, without the assistance of the formula editor to correct us as we go @WarrenBelz @mdevaney you know what I mean!).

So in my prior example, I just wanted to emphasize that I wasn't blowing smoke...this approach does work and, in fact, we use it in many apps.  And, I wanted to show that I wasn't coming from left field, just that I kept slipping with the syntax in the freeform stuff.

@tagustin2020 you've got some of the best minds on this thread focused on giving you a solution.  The best is your choice and what fits your design the most.

[tagustin2020]

Thank you Randy. I would be interested in hearing Matthew's thoughts on a second try at the gallery formula since I am basing the technique on his blog post. @mdevaney 

I really appreciate all 3 of you trying to help me out. It is very generous of all of you.

Teresa

[mdevaney]

@tagustin2020 

Looks like my code had an extra section and the quotation-marks became scare-quotes somehow...

Anyways, this is my revised code.

Switch(
    locSortColumn,
    "NeededBy",
    Sort(
        Filter(
            'Tool Design Support Requests',
            StartsWith('Request Summary Title', TxtSearchBox.Text)
            && (ddStatus.Selected.Value = "Status All"
            || 'Request Status'.Value = ddStatus.Selected.Value)
        ),
        NeededBy, 
        If(locSortAscending, Ascending,Descending)
    ),
    "Title",
    Sort(
        Filter(
            'Tool Design Support Requests',
            StartsWith('Request Summary Title', TxtSearchBox.Text)
            && (ddStatus.Selected.Value = "Status All"
            || 'Request Status'.Value = ddStatus.Selected.Value)
        ),
        Title, 
        If(locSortAscending, Ascending,Descending)
    ),
    "ProjectNumber",
    Sort(
        Filter(
            'Tool Design Support Requests',
            StartsWith('Request Summary Title', TxtSearchBox.Text)
            && (ddStatus.Selected.Value = "Status All"
            || 'Request Status'.Value = ddStatus.Selected.Value)
        ),
        ProjectNumber, 
        If(locSortAscending, Ascending,Descending)
    )
)

After we have solved the original problem we might want to start a 2nd thread because I noticed you have couple of questions.  I'm having a little trouble reading the history of this post because there are so many responses, lol.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[tagustin2020]

Hi Matthew,

Thank you for this revised formula. It doesn't throw any errors, but the gallery is completely blank. I will give Randy's suggestion a try and report back to the group. If I can get it going, I'll start a separate inquiry to see if we can get the sorting and filtering on people fields going as well. 

Have a good day!

Teresa

[RandyHayes · Accepted solution]

@tagustin2020 

I like that you are open to trying several different methods!  It's a great way to learn more.

So, you mention sorting on person columns.  So, picking up on the last post that I gave you.  Here is how to achieve that.  Again, this is picking up from the formulas and setup that I last posted.

These are the exact steps that I did to give that functionality.

Add a Label to the Header - called it lblProjManager

Text Property:  "Project Manager"

OnSelect Action:

UpdateContext({locLastFirst:!locLastFirst});
UpdateContext({locSortCol:"projManager"})

NOTE: I also thought it would be nice to sort by first or last name.  So this adds a new variable to the screen called locLastFirst Selecting the Project Manager label will toggle that between last and first.  (I did not go so far as to add any indicator of this to the screen other than what would be displayed in the results - First Last or Last, First

icnSortDirection - added the project manager to the formula:

Switch(locSortCol,
    "reqStatus", lblStatus.X + lblStatus.Width,
    "RequestSummaryTitle", lblRequestSum.X + lblRequestSum.Width,
    "ProjectNumber", lblProjNum.X + lblProjNum.Width,
    "NeededBy", lblNeededBy.X + lblNeededBy.Width,
    "projManager", lblProjManager.X + lblProjManager.Width
)

Noted changes: the last line in the switch statement.

Added a label to the gallery to display the Project Manager name.  For the last and first functionality, I added this formula to the Text property of that label:

If(locLastFirst, 
    With({lclName:Split(ThisItem.ProjectManager.DisplayName, " ")}, 
        Last(lclName).Result & ", " & First(lclName).Result
    ), 
    ThisItem.ProjectManager.DisplayName
)

For the galHome gallery Items Property:

With({galItems:
        AddColumns(
            Filter('Tool Design Support Requests',
                StartsWith('Request Summary Title', txtSearchBox.Text) &&
                (ddStatus.Selected.Value = "Status All" || 'Request Status'.Value = ddStatus.Selected.Value)
            ),
            "reqStatus", 'Request Status'.Value,
            "projManager", If(locLastFirst, With({lclName:Split(ProjectManager.DisplayName, " ")}, Last(lclName).Result & ", " & First(lclName).Result), ProjectManager.DisplayName)
        )
    },

    DropColumns(
        SortByColumns(galItems,    
            locSortCol,
            If(locSortDir, Ascending, Descending)
        ),
        "reqStatus", "projManager"
    )
)

Noted changes: a new column called "projManager" with the formula for the last and first logic.  This is also referenced by the context variable locSortCol, which is set on the lblProjManager label OnSelect.

This column too is dropped so as to not impact any other screens or dependencies you have on the record structure of your Gallery.

Changed the icnRefresh OnSelect formula to the following just to reset the new variable:

Refresh('Tool Design Support Requests'); UpdateContext({locSortCol:Blank(), locSortDir:true, locLastFirst:false})

No other changes were made and here are the results:

 

I was going to add a drop down filter to demonstrate that as well, but I thought I would give this first.

[mdevaney]

@tagustin2020 
Is it possible that the reason you are not seeing any result is because you have not yet clicked on any of the 'sort icons'?  If I am thinking about it correctly, nothing would be showing until you set a value for the variable locSortColumn.

Therefore, I have added an 'else' case to the end of this SWITCH statement. 

Switch(
    locSortColumn,
    "NeededBy",
    Sort(
        Filter(
            'Tool Design Support Requests',
            StartsWith('Request Summary Title', TxtSearchBox.Text)
            && (ddStatus.Selected.Value = "Status All"
            || 'Request Status'.Value = ddStatus.Selected.Value)
        ),
        NeededBy, 
        If(locSortAscending, Ascending,Descending)
    ),
    "Title",
    Sort(
        Filter(
            'Tool Design Support Requests',
            StartsWith('Request Summary Title', TxtSearchBox.Text)
            && (ddStatus.Selected.Value = "Status All"
            || 'Request Status'.Value = ddStatus.Selected.Value)
        ),
        Title, 
        If(locSortAscending, Ascending,Descending)
    ),
    "ProjectNumber",
    Sort(
        Filter(
            'Tool Design Support Requests',
            StartsWith('Request Summary Title', TxtSearchBox.Text)
            && (ddStatus.Selected.Value = "Status All"
            || 'Request Status'.Value = ddStatus.Selected.Value)
        ),
        ProjectNumber, 
        If(locSortAscending, Ascending,Descending)
    ),
    Filter(
        'Tool Design Support Requests',
        StartsWith('Request Summary Title', TxtSearchBox.Text)
        && (ddStatus.Selected.Value = "Status All"
        || 'Request Status'.Value = ddStatus.Selected.Value)
    )
)

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[tagustin2020]

Hi Matt,

Thank you for sending this over. The gallery items are loading and the 3 columns are sorting perfect. Just one small detail I would like to ask about. Currently, when the screen first loads, the Title column isn't sorted in either Ascending or Descending order. In my original formula seen below, The Title column was sorted by Ascending order by default. How can I add this piece to your formula? I tried simply adding the pieces of code I have colored in red to the start and end of the formula, but got a bunch of red squigglies. Where am I going astray?

SortByColumns(

        Filter(

            'Tool Design Support Requests',

            StartsWith(

                'Request Summary Title',

                TxtSearchBox.Text

            ) && (ddStatus.Selected.Value = "Status All" || 'Request Status'.Value = ddStatus.Selected.Value)

        ),

    "Title",

    Ascending

)

[tagustin2020]

Hi Randy,

Thank you so much for conducting this experiment. Unfortunately, adding and dropping columns from a Collection throws a couple of monkey wrenches into the app. While it works well for sorting the gallery, new items do not appear in the gallery when added via my new request form and when I click on the gallery nav arrow to go to my edit screen, an incorrect record flashes on the screen before settling on the right one. 

Since this thread is so long already, I'll submit a separate question in regards to People and Choice column sorting and filters in case you or anyone else knows of any other techniques I could try. I'll have to do that at a later date since there are a couple of other major capabilities I have to add to the app before Wed.

Have a great day!

Teresa