cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Post Prodigy
Post Prodigy

Sort, Filter, Search Syntax

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

3 ACCEPTED SOLUTIONS

Accepted Solutions

@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:

RandyHayes_0-1599333210900.png

The screen with control names.

RandyHayes_1-1599333367570.png

 

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:

TeresaApp.gif

 

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

View solution in original post

@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:

TeresaApp2.gif

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

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

View solution in original post

@tagustin2020 
One final adjustment should do the trick.  Thank you for your prompt responses!

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)
    ),
    Sort(
        Filter(
            'Tool Design Support Requests',
            StartsWith('Request Summary Title', TxtSearchBox.Text)
            && (ddStatus.Selected.Value = "Status All"
            || 'Request Status'.Value = ddStatus.Selected.Value)
        ),
    Title,
    Ascending
    )
)

 

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

View solution in original post

36 REPLIES 36
Super User III
Super User III

Hi @tagustin2020 ,

Please post any code also as Text as it helps correct it.

You are using a table value at the top of the Switch statement and then text values as criteria, so this will not work.

When you post the text, can you please explain what you are trying to do.

Super User III
Super User III

@tagustin2020 
Can you please post your current code inside the forums?  That way I can do an experiment without having to re-write anything.

Screenshots are great... and I wish more people did screenshots!  In this case having the code in text format would be handy too.

 

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

@mdevaney posted a couple of seconds after me - I will leave you with him.

Super User III
Super User III

@tagustin2020 

It looks like you are just trying to sort by different columns based on the value of locSortColumn.

It also appears that the text value in that variable is the same as the column name, so you should just be able to go with this:

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)
)

 

A lot of good minds on this one for you, so hopefully this is helpful.

 

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

Hi Matthew,

 

Here is the BEFORE formula that is verified to be working (doesn't include your Switch/Sort formula syntax yet):

 

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

)

 

Here is what I tried to do. It threw something like 60 errors so epic fail - lol.

 

SortByColumns(

Switch(

        Filter(

            'Tool Design Support Requests',

            StartsWith(

                'Request Summary Title',

                TxtSearchBox.Text

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

        ),

locSortColumn,

”NeededBy”, Sort(‘Tool Design Support Requests’, NeededBy, If(locSortAscending, Ascending,Descending)),

”Title”, Sort(‘Tool Design Support Requests’, Title, If(locSortAscending, Ascending,Descending)),

”ProjectNumber”, Sort(‘Tool Design Support Requests’, ProjectNumber, If(locSortAscending, Ascending,Descending)),

‘Tool Design Support Requests’)

    "Title",

    Ascending

)

 

I don't know how to put formulas into light gray boxes in these forum postings. Can you educate me on how to do that?

 

Thanks,

Teresa

Thanks for the suggestion Randy. I appreciate you pitching in! Let's see what Matthew comes back with as well on this one since I'm basing the effort on his post. Kind regards, Teresa

@tagustin2020 

Perfect.  I will respond in a few hours once I’m back at the computer.  Thank you so much for providing the code and I’m super excited you want to implement the sorting controls!

Thank you Matthew. I really appreciate your help and all of your blog posts. I find them very interesting and helpful. I just used a portion of your data validation blog for a date picker in my new request form today. Please keep those great ideas coming. I really like the clever solutions and attractive designs you come up with.

 

Kind regards,

Teresa

Microsoft
Microsoft

@mdevaney @RandyHayes @WarrenBelz 

Thank you for your responses to the forum. Your answers keep the forum active. Thank you for your continued support.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (11,074)