Power Platform Community Forum Thread Details

Our company created a Power App to track time to bill to. The first column of the table is a look up list with all of the different items to bill to. This list has 3 distinct types of data, text, four-digit number, and four-digit number with "S" prefix. The text represents things like VACATION, PTO, TRAINING..., the numbers are the active job numbers and the numbers with prefix are service work for the active jobs. We want the all the text items to be grouped in a specific order at the top of the drop-down list and then the job and job with suffix in sequential order. This is a very custom sort, and I have no idea of how to achieve it. I have manually sorted the source table, but the data still loads in this order.

Example:

PTO

HOLIDAY

TRAINING

OFFICE

Misc - Shop Only

1000

1001

S1001

1002

1003

1004

S1004

...

As you can see in the image below, it is not at all what we want.

Categories:

Building Power Apps

You can use an expression such as this one to sort the items in the order you want:

Sort(
    myColl,
    Switch(
        Lower(Value),
        "pto", -100,
        "holiday", -99,
        "training", -98,
        "office", -97,
        "it", -96,
        "misc - shop only", -95,
        "marketing", -94,
        /* numeric ones */
        With(
            { startsWithS: StartsWith(Value, "S"), numericPart: If(StartsWith(Value, "S"), Mid(Value, 2), Value) },
            IfError(Value(numericPart), /* if not numeric, use a large number */ 99999) +
                If(startsWithS, /* starts with S should come after those without */ 0.5, 0)
        )
    )
)

By returning negative numbers for your custom text data, they would come before the positive ones. And to differentiate the numbers with suffix and those without it, we can add a small increment (in the example above, 0.5) to the numeric value so that something like S100 will come after 100 but before 101.

Another option would be to add "sorting columns" for those three criteria (text / presence of S prefix / numeric value), and then sort based on those new columns, something along the lines of

SortByColumns(
    AddColumns(
        myColl,
        TextOrder,
            Switch(
                Lower(Value),
                "pto", 1,
                "holiday", 2,
                "training", 3,
                "office", 4,
                "it", 5,
                "misc - shop only", 6,
                "marketing", 7,
                999 // those not listed above will come after
            ),
        JobNumber,
            IfError(
                Value(
                    If(StartsWith(Value, "S"), Mid(Value, 2), Value)
                ),
                999999 // non-numerical values will get a large number
            ),
        ServiceOrder,
            If(StartsWith(Value,"S"), 1, 0)
    ),
    "TextOrder", SortOrder.Ascending, // First sort by known values
    "JobNumber", SortOrder.Ascending, // Then by service number
    "ServiceOrder", SortOrder.Ascending // if two SNs are equal, the one with S comes last
)

Both options should accomplish the same task (with negligible performance differences). Some may find the first more readable, some the second.