Hi,

Anybody in the mood to help a lurker/noob?

I am migrating a FMP6 database to FMP8.5. I did not develop the
original solution, and I've come across something which concerns me,
but which I cannot think of an easy way to correct.

This solution manages event information with several related dates
(like an opening and a closing date, various deadline dates, etc.) Not
every event uses every type of date, so some date fields are empty for
some events.

Whoever designed the database in the first place apparently came
across a problem where, if you sorted a list view by date, then all
the *empty* date records would show up first. (It's somewhat important
that the list sort from earliest to most recent date, not vice-versa.)

The way he solved this concerns me. As best as I can tell, he created
a custom value list with *thousands* of entries, starting with
1/1/2000 and ending with 12/31/2007... and he sorts against *that*
monstrous value list. This doesn't seem a smart way to do this, and it
will stop working in 2008 unless I paste in a thousand more dates or
so.

No doubt there's a better way to do this. Suggestions?

Thanks,
-Rob Steward

Create a calc field (date result) as:

case(isempty(DateField), date(12,31,2020), DateField)

Sort on this calc. If the date is empty, it will sort to the end
because the calc'd date will be 14 years in the future: 12/31/2020.



robdsteward (AT) gmail (DOT) com wrote:
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg (818) 883-2846
FM Professional Solutions, Inc. Los Angeles

FileMaker 8 Certified Developer
Associate Member, FileMaker Solutions Alliance

Well, it's not a bad workaround. You can easily generate a new list of dates
going forward however far in the future is needed, using Excel or even
FileMaker itself. It has the benefit of not requiring any extra calculation
fields or special considerations when otherwise working with the system.

The classic solution would be a second field which is a calculation:

If( IsEmpty(MyDate); GetAsDate("12/31/4000"); MyDate)

And sorting on that (but displaying the MyDate field). But this requires one
of these "sort fields" for every Date field that could be empty.

Another thing you could do is specify that all date fields have an
auto-enter value of "n/a" (and replace any blanks with that) ... the "n/a"
will sort to the bottom, and because it's auto-entered it will not trigger
the usual "you must enter a date in the format..." error. However, this
could lead to confusion down the road if you don't fastiduously check for
valid dates when referencing that field elsewhere.


<robdsteward (AT) gmail (DOT) com> wrote

Thanks guys. I should have thought of sorting a non-displaying field,
but I think I'd been staring at this project for far too long today.
(I'm in the early stages of just getting my mind around the messy
state this thing is in, so it's *lots* of effort for very little
gain.)

Thanks again!
-Rob Steward

Well, your gain might just be a far better understanding of FileMaker. In
the future letting you solve a problem much quicker.

Keep Well, Ursus
<robdsteward (AT) gmail (DOT) com> schreef in bericht
news:1172125509.403046.247200 (AT) h3g2000cwc (DOT) googlegroups.com...