Sorting dates, ignoring empty fields

[Brian Martz]

I am trying to sort my database in a list view by a date field. But not all the fields are populated and not all of them necessarily will be.

Is there a way to sort the list by date (ascending order) and ignore the empty fields?

[Lee Smith]

Do you need the records that don't have the dates?

If not, you could simply do your find on a range in the date field.

i.e.

>3/1/2006

Or

3/1/2006...3/31/2006

HTH

Lee

[mz123]

Another option is to set a calculation field if you want the empty fields to go to the end of the list (and not at the default of the beginning) and then you can sort on the calculation field (without actually displaying the Calc field)

Let's call it "DateCalc"

DateCalc =

If(DateField = ""; "1/1/2999"; DateField)

...and that way the empty dates go to the end of the date range.

Martha

[Brian Martz]

Lee, that works better than what I was thinking. Thanks for the suggestion.

Brian

[Søren Dyhr]

Please note that I'm fully with Lee, validations ought to have caught the empties

DateCalc =

If(DateField = ""; "1/1/2999"; DateField)

Provided the datefields are stored in textfields, but the advice has it's caveats, such sorting behave a tad more correctly in europe, where the format is DD/MM/YY ...but textfields sorts the opposite direction. So if you attempt's this are you bound to remember the reverse logic in descending being ascending.

Being honest would I not break out of the fieldtype if I were to choose. The idea of putting an incredible large date in the sort field however a good idea. But it ought to be done this way:

Case ( IsEmpty ( DateField );Date ( 1 ; 1 ; 2999 );Datefield )

Now previously have we made measures to mix dates, textfields and numbers, byt means of calcfields and perhaps even scripting to uniform the sortorder for these different kinds. But a clever chap has found a much better way to handle this:

http://www.filemakerpros.com/LessSort.sit (Macs)

http://www.filemakerpros.com/LessSort.zip (Windows)

--sd

[eswanborg]

This is a great suggestion - thanks! I've been trying to solve this for a while now.

Thank you.