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

[mdevaney]

@tagustin2020 
OK, I'm back at a computer now.  From what I understand reading your post this code is currently working. 

Filter(
    'Tool Design Support Requests',
    StartsWith('Request Summary Title', TxtSearchBox.Text)
    && (ddStatus.Selected.Value = "Status All"
    || 'Request Status'.Value = ddStatus.Selected.Value)
)

Here's how I would use your FILTER function inside the 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)
    ),
    "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)
    )
)

Let me know if you encounter errors 🙂

---
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."

[WarrenBelz]

Just a very small contribution to @mdevaney ‘s rather inventive solution

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

[mdevaney]

@WarrenBelz 

Just a clarification.  I'm sure you already know... but I'm doing it this way to ensure all records are returned as opposed to only the the first 2,000 records.

[tagustin2020]

Hello,

Thank you all so much for your advice. I know it took some time and I am grateful. I went slow and careful this morning, trying each suggestion one at a time, but unfortunately, none of the 3 approaches are working quite yet. 

Since I am limited to 3 attachments, I am including separate docs for Matthew and Warren's results and a detailed doc of formulas in my app (OnVisible, OnStart, Sorting Icons & Gallery) in a Word doc in case it is helpful for the sake of troubleshooting. Randy, thanks so much for the suggestion, but the app freaked out with 50+ errors so as much as I would love for the solution to be simple, it seems like a more detailed syntax is going to be necessary for this one.

Kind regards,

Teresa

[RandyHayes]

@tagustin2020 

Sorry about that - I was missing a trailing parenthesis in the formula.  Should have been:

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

[mdevaney]

@tagustin2020 
Incompatible type means a text is being compared to a number somewhere in the formula or something similar.  Any highlighted sections of code in red underlines?

[RandyHayes · Accepted solution]

@tagustin2020 

So I took a slightly different approach and thought I would share with you.  I read through your documents and saw what you were doing.  All fine, except personally, when I see repetitive formulas I start to consider maintainability of an App.  The more places you have to go back to and change the same formula over and over, the more chances that you'll either waste a lot of time, or worse, forget one.

When I see an Items formula with multiple filter functions in it, it is one of those maintainability issues again - if you need to change what you return or filter on, you'll have to do it in every spot.

So, I'll share my suggestions for you if you have interest. The original formula I mention (except for the forgetting of the parenthesis) works fine.  Here is some of this demonstrated to you in full detail.

First, the test app screen:

The screen with control names.

OnStart:

ClearCollect(colStatus, {Value:"Status All"}, Choices('Tool Design Support Requests'.'Request Status'))

icnRefresh.OnSelect:

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

ddStatus.Items:

colStatus

OnSelect of labels:

lblStatus:  UpdateContext({locSortCol:"reqStatus"})
lblNeededBy:  UpdateContext({locSortCol:"Needed By"})
lblRequestSummary:  UpdateContext({locSortCol:"Request Summary Status"})
lblProjNum:  UpdateContext({locSortCol:"Project Number"})

icnSortDirection:

Visible property:
    !IsBlank(locSortCol)

OnSelect Action:
    UpdateContext({locSortDir:!locSortDir})

Icon property:
    If(locSortDir, Icon.ChevronUp, Icon.ChevronDown)

X property:
    Switch(locSortCol,
        "reqStatus", lblStatus.X + lblStatus.Width,
        "Request Summary Title", lblRequestSummary.X + lblRequestSummary.Width,
        "Project Number", lblProjNum.X + lblProjNum.Width,
        "Needed By", lblNeededBy.X + lblNeededBy.Width
    )

galHome 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
        )
    },

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

And here it is in action:

 

[tagustin2020]

Wow! Thanks for all of this Matthew. You have gone so above and beyond, I can't believe it. I can't wait to try it out. I'll let you know how it goes. I didn't think there was going to be a way to sort on the Status column given that it is a complex field. My users (the engineers at my company) want to be able to sort and filter by the person columns as well. Is there a similar technique that would enable me to do that? I tried an Add Columns technique, but it broke the app so badly that I had to restore a previous version of the app to straighten everything out. The two main issues were that new request form submissions were not being added to the gallery and when I clicked the gallery nav button to go to a record it was confused and would show a wrong record before finally settling on the correct one. I just mention this because, I'm a little gun shy of trying the "add columns" technique again. I would definitely love to hear if there is an alternative way though!

I realize that I'm expanding the original scope of this request which was to sort on the Title, Needed By and Project Number columns, but now I'm wondering since you have shown how to sort the Status column. Let me know if you want me to open up a separate community forum request for sorting and filtering on people columns. By the way, I'll be working this weekend and Labor Day, but I certainly don't expect you to!

Kind regards,

Teresa

[tagustin2020]

Oops! Wrong name. Thank you Randy. You da man 🙂

[tagustin2020]

Hi Matthew,

I have since restored my original Items property formula to get the app working again. Not sure if this helps, but the Title and Project Number columns are single line of text. The Needed By date comes from a calendar date picker.

Teresa