Sorting a Gallery (like excel columns) by Date

[AdamH]

Hi All,

I wanted to know how to sort a gallery based on date fields. 

As you can see from the image, I'm using the gallery in an 'excel' type of way to display data within the records and I thought about adding a sort button at the top of all date columns (1 to 4) and when selected, the gallery sorts by nearest date. 

Item Code I am using for the gallery at the moment is:

AddColumns(Filter('NC MST Tracker',Grade.Value = "NQP" Or Grade.Value = "NQP+6"),"nqphours",nqphoursGlobalVariable)

[sopatte]

Sort and SortByColumns functions in Power Apps - Power Apps | Microsoft Docs

SortByColumns sorts by one or more columns given a column name which is passed as text. I've never tried this but I believe one could pass a variable with the column name, thereby making the sort column change dynamically.

So,

SortByColumns(
    AddColumns(
        Filter(
            'NC MST Tracker',
            Grade.Value = "NQP" Or Grade.Value = "NQP+6"
        ),
        "nqphours",
        nqphoursGlobalVariable
    ),
    MySortColumn,
    MySortOrder
)

  
And for your button at the top of your column,

If( MySortColumn = "ThisColumnName",  
    // change "ThisColumnName" to match each column's actual name
    Set(MySortOrder,  // toggle sort order
        If(MySortOrder = SortOrder.Descending,
            SortOrder.Ascending,
            SortOrder.Descending
        )
    ),
    Set(MySortColumn,"ThisColumnName");
    Set(MySortOrder,SortOrder.Ascending) // default sort order
)

This isn't quite like what Excel would do because Excel would take the existing sorted table and resort it based on the new sort parameter, whereas this solution takes the order returned by Filter. One could more closely simulate that behavior by storing the table in a variable (or collection), then on each click of a sort button, taking the table in the variable and resorting that. However that comes with some other side effects, like that the data is updated dynamically if the remote data store changes. This could be a feature, not a bug. It just depends on what you want.

[AdamH]

@sopatte 

Thanks for your message.

Along side your advice I have also researched some more and started to find some video's from April Dunnam, one of which was almost covering my needs - https://www.youtube.com/watch?v=8bNCBe2wzYI 

However it seems that the additional sort icons ( 2, 3 and 4) seem to be additionally sorting the data based on the first sort icon (1) which is not what I was looking for. I was looking for a complete sort (re-sort) based a specific column alone when I select it's sort icon.

The code I have put together for the gallery is this:

If(
    !IsBlank(varStartMonthSort),
    SortByColumns(
        AddColumns(
            Filter(
                'NC MST Tracker',
                Grade.Value = "NQP" Or Grade.Value = "NQP+6"
            ),
            "nqphours",
            nqphoursGlobalVariable
        ),
        "NQPInitialMeeting",
        If(
            varStartMonthSort = true,
            Ascending,
            Descending
        ),
        "OData__x0036_MonthNQPReviewDate",
        If(
            var6MonthSort = true,
            Ascending,
            Descending
        ),
        "OData__x0031_2MonthNQPReviewDate",
        If(
            var12MonthSort = true,
            Ascending,
            Descending
        ),
        "OData__x0031_8MonthNQPReviewDate",
        If(
            var18MonthSort = true,
            Ascending,
            Descending
        )
    ),
    'NC MST Tracker'
)

And the button that sits above each column on the gallery is:

UpdateContext({varStartMonthSort:!varStartMonthSort})

[AdamH]

I've attempted to use a switch for this instead almost have it, but I'm having an issue with this part of the code, anyone able to assist with this?

AddColumns(
    Filter(
        'NC MST Tracker',
        Grade.Value = "NQP" Or Grade.Value = "NQP+6"
    ),
    "nqphours", nqphoursGlobalVariable), Switch(SortColumnPref,"Title",SortByColumns('NC MST Tracker',SortColumnName, If(SortDecendingName, Ascending,Descending)))