Easily put blanks at end of ascending sort?

[ESpringer]

It seems FileMaker (maybe because of OS?) always puts blank values at beginning of ascending sort.

I often want a sort order that is forwards (1,2,3,a,b,c) but which leaves the blanks for the end. Say the field is numeric "priority" and the blanks are ones for which it's a low priority for me to even assign a priority... or the field holds contacts and I want portals to show records with contacts before records without...

I can make a work around by either entering ZZZZZ as a default value (very crude, breaks commonsense principles), or by creating a further field which shows 1 if there's a value at all, and then I can do a complex sort order. But that's a drag, too; it doesn't work as a quick table-view column sort, and it clutters up my field definitions.

Surely others have this experience? And an elegant solution?

[Vaughan]

What happens if you set the sort order (in the field storage options) to ASCII?

I usually make a calc field with the 1 in it if the field is empty or not. More often the calc keeps the original value but replaces an empty field with the string "<no entry>" or whatever is similar. Then I just need to work from the calc field and it's a very simple sort.

[ESpringer]

It seems ASCII sort is not available in FM7; Unicode is. At any rate, it doesn't work for numbers...

So you do end up creating a "shadow" field... if *you* resort to that, I'm pretty convinced there's no better solution (and it still doesn't solve the table view problem while preserving live data entry unless column space is wasted on 2 fields). I take it that you use unicode (in FM7) to keep <no entry> from sorting in with the Ns, and numeric values get leading zeros to keep them clean?

Doesn't it seem like the sort of simple thing that could be part of the sort order dialogue? Imagine radio buttons for ascending / descending / custom . Separate checkbox: Reverse placement of blank values?

[The Shadow]

One way: create a value list for the field to sort, using all values. Then sort that field by the value list.

Since blanks are eliminated from the value list, they sort to the bottom.

[ESpringer]

Oh, I had somehow imagined that custom sort order would always involve me in typing out the values in order. But not so!

I'll be curious whether this bogs things down at all, but I've just gotten it to work on a simple case and will keep trying it!

Thanks, Shadow.

[DanBrill]

Sweet trick. Thanks Shadow.

[Vaughan]

"it still doesn't solve the table view problem"

Yes you are right. See, I never use table view for "production" systems, so it is not something that I ever considered.

Having said that, the calc field technique has been made obsolete by Shadow's tip!

Vaughan