ORDERBY() not working as expected in Template due to blank values?

AppSheet AppSheet Q&A
1

I have a Slice with row condition… OR([MyDate] > “4/18/2020”, AND(ISBLANK([MyDate]), ISNOTBLANK([MyText])))

The above condition returns some rows that have a MyDate value and some that have blank MyDate.

I have a Google Doc template to produce a table using… Start: ORDERBY(SliceName[_ComputedKey], [MyDate])

In the resulting table, the proper records are returned, but not in the order I expect. I expected the records with MyDate values to be ordered by that date and all other records to be grouped together above or below. Instead, the order seems random:
4/30/20
(blank)
4/24/20
4/30/20
(blank)
…
…

If I change the ORDERBY to [MyText], it orders by that text as expected.

If I change the Slice’s row condition to just [MyDate] > “4/18/2020” (eliminating the blank date records) and use ORDERBY with [MyDate], then the report template orders the records properly by date.

2

Hmm! That’s unexpected indeed! I have no explanation, but I can suggest a workaround:

ORDERBY(SliceName[_ComputedKey], ISBLANK([MyDate]), TRUE, [MyDate])
1 Like
3

Thanks for the workaround idea, Steve. Explicitly sorting first by the blank status does indeed yield the desired result. I’ll just have to remember in the future to avoid “cleaning up” that code if I spot it and question the necessity of the multiple sort conditions.

1 Like