Date Calc: Find nearest date in future

[fraank]

Hi there

I´m pretty new to filemaker and I´m searching for a date function that will display the nearest date compared to the current system date ?

I have a few date fields (date 1 ... date 50). Now the calculation date field should show me which of the date fields is coming up first and closest to the current date ?

Is there a function for that?

Thanks a lot,

fraank

[comment]

I have a few date fields (date 1 ... date 50).

Do you mean a record has 50 date fields? That doesn't sound like a good method for anything, let alone your current question.

[fraank]

Yes, that is what I meant. 1 Record that has max. 50 date fields to compare. There might be about 20 records later on.

[comment]

As I said, that's not a good method in general. I am not sure what this is about, but usually you would have 50 (or rather any number of) related records in another table. For example, a table of Projects and a table of Milestones, related by ProjectID. Then you can get the upcoming milestone by defining another relationship as:

Projects::ProjectID = Milestones 2::ProjectID

AND

Projects::cToday < Milestones 2::Date

where cToday is an unstored calculation = Get(Currentdate). By sorting the related milestones by Date, ascending, the first related record will be the upcoming milestone.