Sort gallery numerically when using a switch

[Dioz]

Hey everyone,

I'm currently trying to sort a gallery by different columns using the Sort() function in combination with a switch, in which I define the sorted column. I've done this before and never had problems. In my current project, I'm not able to sort certain columns by numerical order (1,5,11 instead of 1,11,5).

I'm defining the columns in my switch as Value, it just doesn't seem to change anything at all. This is the first time I'm working with Dataverse instead of SharePoint, so I suspect that this might be the reason for the different behavior. The columns are normal Whole Number columns in Dataverse.

I tried sorting without the Switch, using only a single Value column and that worked perfectly fine. I also tried to use two different switch-functions, one for numerical and one for text values, as well as getting rid of everything else but the sort function, but didn't have any luck with that either. 

So the problem really seems to be the switch. Any ideas what I could try?

My gallery:

With(
    {
        Items: Sort(
            Filter(
                Search(
                    AddColumns(
                        AssetBookings,
                        "Asset.Description",
                        Asset.Description,
                        "Asset.Name",
                        Asset.Name
                    ),
                    txt_SearchBooked.Value,
                    "cr78d_name",
                    "Asset.Description",
                    "Asset.Name"
                ),
                Event.Event = eventSelected.Event
            ),
            Switch(
                varSortBy,
                "Name",
                ThisRecord.Asset.Name,
                "Category",
                ThisRecord.Asset.Category,
                "Bundle",
                ThisRecord.Asset.Bundle,
                "Amount",
                Value(ThisRecord.Amount),
                "Stock",
                Value(ThisRecord.Asset.Stock),
                "BillingRate",
                Value(ThisRecord.Asset.BillingRate),
                "Total",
                Value(ThisRecord.BillingRate * ThisRecord.Amount)),
                If(
                    varSortOrder,
                    SortOrder.Ascending,
                    SortOrder.Descending
                )
            
        )
    },
    ForAll(
        Sequence(CountRows(Items)),
        Patch(
            Last(
                FirstN(
                    Items,
                    Value
                )
            ),
            {rowNumber: Value}
        )
    )
)

[Dioz · Accepted solution]

Tried a couple of things and found a workaround that I want to share in case anyone runs into the same problem in the future:

I could swear it's been possible before, but it looks like switch functions can't handle different sorting types automatically, so I just used my entire function twice in an if-function with both sorting types separated. It's not exactly clean, but whatever works.

If(
    varSortBy = "Text1" || varSortBy = "Text2",
    Sort(
        Datasource,
        Switch(
            varSortBy,
            "Text1",
            ThisRecord.Text1,
            "Text2",
            ThisRecord.Text2
        )
    ),
    Sort(
        Datasource,
        Switch(
            varSortBy,
            "Value1",
            ThisRecord.Value1,
            "Value2",
            ThisRecord.Value2
        )
    )
)

[v-jefferni]

Hi @Dioz ,

Thank you for the sharing. Please accept you rely as solution and help others find it.

Best regards,