Grup by date, invalid dates last

[Frederik]

I have a timetable view, where I show customers/tasks by the date of their next appointment.

Some of my data entries don’t have these dates, and so they return with “Invalid date” because the field is blank - this is fine, and works as I would anticipate.

The problem is that when grouping by date, “Invalid date” becomes the FIRST group, and I would like it to be the LAST group - is there a way to change this behaviour?

[Aleksi]

It sounds that the sort order is correctly set already. One opetion is if you add an additional column to your table and then use app formula like IF(ISNOTBLANK([Date]),[Date],“01/01/2030”) and then use that for sorting. The value can be also like “01/01/2000” depending what your goal is.

Though you don’t need to show that field at all in your view.

[Aleksi]

Are you using this for the grouping or sorting?

[Frederik]

I’m using the date for grouping, not sorting, so unfortunately, not showing the column I group by is not an option, as far as I know

[Aleksi]

Does it matter if the group name shows like “01/01/2030” rather than “Invalid date”?

[Frederik]

I would really perfer if it didn’t show some arbitrary date, as the users of the app might view that as a bug, reducing the trust in the reliability of the data processing occurring through AppSheet

[Aleksi]

Do you want to see the date in desc or asc order?

[Frederik]

I would like to show the date groups in ascending order, so that the top group is the earliest date, and dates increase scrolling down, ending with the invalid date group (customers where no date has been agreed upon yet)

[Aleksi]

The only option that I can think of is if you add an additional text column with a formula like…
IF(ISNOTBLANK([DATE]),TEXT([DATE],“yyyy-mm-dd”),“No Date”). Then it will show the “Invalid Date” group in the end but then the date format needs to be in format yyyy-mm-dd because now it’s a text string. It’s not the locale format you are using but that’s the closest one I can think.

[Frederik]

Yeah, I thought it might be something like that…
I might just do that, but it would be awesome to have an option of how to sort empty fields in the future

[Steve]

If you’re going to create a new column, just use the new column to indicate whether the date is valid or not (app formula: ISNOTBLANK([DATE])), the sort by this new column first.

[Aleksi]

That doesn’t help with grouping

[Steve]

Eep! Point to @Aleksi! I’ll shut-up now…