Sort() not sorting Numbers correctly with a Switch() Statement

Like (1) Share

Report

Posted on 18 Feb 2022 14:37:53 by tryingPwer

Hi,

I am trying to have dynamic sorting, with complex types which is why SortByColumn() is not working.

My code basically is this:

Sort('Data';
Switch(lastSortFilter;
 "xy";'xy.DisplayName;
 "ID"; ID;
 "Title";Title;
 "Status";Status.Value;
 "abc";'abc'.Value;
)
 ; If(SortVar; Descending; Ascending))

The problem is, it is only working as text sort an breaks the sorting of the number columns. For Example ID gets sortet like this:

But ID is not a text column. If I change my Code to this:

Sort('Data';
ID
 ; If(SortVar; Descending; Ascending))

It sorts the way it should be. So 1, 2, [...], 10, 11.

So why is the Switch() function breaking the sort function and is there any workaround?

Thanks for any help

Categories:

General Questions

Using Apps Using Formulas

All responses (1)

Answers (0)

timl 35,437 Super User 2025 Season 2 on 25 Feb 2022 at 11:21:36

Like (0)

Report
Re: Sort() not sorting Numbers correctly with a Switch() Statement

Hi @tryingPwer

The return value from Switch must always return a value with a consistant data type - in this case, a string.

A workaround would be to format the id values with preceeding spaces using the Text function (eg, 00001, 00002, 00003) etc, and the sort should then work as expected.

Sort('Data'; Switch(lastSortFilter; "xy";'xy.DisplayName; "ID"; Text(ID;"00000#"); "Title";Title; "Status";Status.Value; "abc";'abc'.Value; ); If(SortVar; Descending; Ascending) )