Jump to content

Sorting dates, ignoring empty fields


Brian Martz
 Share

This topic is 5541 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 3 months later...

This topic is 5541 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.