Multi-level sorting

AppSheet AppSheet Q&A
1

I need some fiendishly clever sorting. I’ve got a list of names with dates (some names may not have a date). If I sort by date ascending with a 2nd level sort on name ascending I get this:

Name, Date
C,
E,
F,
B, 2025/01/01
A, 2025/01/03
D, 2025/01/04
G, 2025/01/10

This is almost what I want - blank dates at the top, but if it does have a date it must sort descending. Like : IF DATE IS BLANK SORT BY NAME ASCENDING, BUT IF DATE IS NOT BLANK SORT BY DATE DESCENDING. The result should look like this:

Name, Date
C,
E,
F,
G, 2025/01/10
D, 2025/01/04
A, 2025/01/03
B, 2025/01/01

Is this possible?

2

If you are using the Sort property in a view - NO, you cannot sort like this using the standard sorting provided. You might be able to add additional details/columns to force the sort order you want.

If you are trying to sort in an expression, such as for a dropdown, you can divide the LIST into two parts, sort them separately and then use the LIST ADD “+” operator to combine them together.

If you can provide info on where you are trying to Sort we can help figure out a way to get your desired result.

I hope this helps!

3

If you want to do this on a table view, one idea is to add a VC that can be used as a sort key.

TeeSee1_1-1744636581977.png
TeeSee1_1-1744636581977.png434×312 8.65 KB

SORT COLUMN expression

IF(ISBLANK([b]),
COUNT(
  SPLIT(
    ANY(
      SPLIT(
        (" , " & SORT(SELECT(tb w sp[a], ISBLANK([b])),FALSE) & " , "),
        (" , " & [a] & " , ")
      )
    ),
    " , "
  )
),
100000 + COUNT(      /** Adjust 100000 depending on the number of records you have
  SPLIT(
    ANY(
      SPLIT(
        (" , " & SORT(SELECT(tb w sp[b], ISNOTBLANK([b])),TRUE) & " , "),
        (" , " & [b] & " , ")
      )
    ),
    " , "
  )
)
)

This INDEXOF technique is used. (I have not test this expression for performance…)