Sort number column with null values at bottom

[Dan_Conway]

Hello,

I have a database where I would like to be able to sort the entries ascending or descending numerically. The problem is that not all entries have data in all categories, and the null values are treated as zero and appear at the top when sorting ascending. I would like the null values to always appear at the bottom, regardless of whether I sort ascending or descending.

Is this possible? Any help is appreciated!

[gwynn_kruger]

I don’t believe it’s possible to always place the blank values at the bottom regardless of sort order.

Why do you want them to appear at the bottom? Is there another way of doing what you’re after that doesn’t involve sorting?

[Justin_Barrett]

Welcome to the community, @Dan_Conway! :grinning_face_with_big_eyes: While extra fields aren’t always the best idea, you might be able to get what you want if you add one. Specifically, add a formula field that creates a specific result depending on whether or not the field that you’re sorting by is blank. It could be as simple as:

{Field Name} = BLANK()

That will return 1 for records with that field empty, and 0 for all the rest. Then group by that formula field (which can remain hidden), and set the group order to A–>Z. The sort will then happen inside each group.

Will that work for your needs?

[Dan_Conway]

It’s a database of books for language learners. For the criterion of word count, some people may want to sort with the lowest word count (for novice learners), while others may want to sort by the highest word count (for more advanced learners). But I don’t have word count data on all the books yet, so some entries are blank at this point. I can’t just get rid of those entries, because this is meant to be a list of all the available books.

[Dan_Conway]

I was thinking of some solution like that. But let’s say that I want to sort by author, and every book has data for that. Will it still group them according to the ones missing data for other categories?

[gwynn_kruger]

Justin’s solution seems to do the trick.

Descending sort by author with hidden column grouping:

Here’s the ascending sort showing that the grouping order isn’t changed:

Here’s the grouping column definition:

Just group by that column and then hide it as Justin suggests.

[Justin_Barrett]

When you have both groups and sorting active, grouping happens first, then the records are sorted within each group.

You could always use multiple views: one that sorts by author without any grouping, and another that groups by word count, with books not counted grouped at the bottom using a method like the one that @gwynn_kruger suggested, and the groups sorted by some other field.