Max function not working for date field

[MicheleC]

I'm trying to get the most recent date in a particular field, across all my records. The field is Accessions:accession_date. The field is set to Date type. The date is not calculated, it's manually entered into the field when a new record is created (in some cases the field is empty when the accession date is unknown).

I tried Max (Accessions:accession_date) but every time I run the script, I get the date of the record I'm currently sitting on, not the max date of all records. What am I doing wrong?

[David Jondreau]

You're expecting behavior from a function that isn't designed to work the way you want it to.

From help:

"Max(field{;field...})

Parameters

field - any related field, repeating field, or set of non-repeating fields; or an expression that returns a field, repeating field, or set of non-repeating fields.

Parameters in curly braces { } are optional."

One of Filemaker's glaring issues is that many functions don't work across found sets. Instead, you can use a Summary-type field which has an option for finding the Max of a found set.

[Søren Dyhr]

There is no mentioning of a cartesian relation, and are we talking about Max( or the Summary function Maximum. You can have it either way - the cartesian with Max( over the relation, or the summary function which only will give the max for the found set.

--sd

[Søren Dyhr]

One of Filemaker's glaring issues i

Why am I thinking of John Lennon here?:

Life is what happens when you are making other plans

Ehem, this is a database not a spreadsheet! Nowhere else in the RDBMS sphere exist nothing like calc'fields!

--sd

[MicheleC]

Not trying to be dense but I don't understand what's meant by Cartesian, nor by a "summary-type field," and when I search the list of functions I find no mention of a "Maximum" function.

Edited September 16, 2009 by Guest

[comment]

Perhaps it would be easier if you explained what are you actually trying to accomplish by this.

[MicheleC]

Sure -- I have 2000+ records; each one has an accession date. I'm trying to find the most recent accession date across all records. I thought Max was the way to go but apparently not (and now I'm not even sure I understand what Max does LOL!). Thanks --

[comment]

No, I meant why? What do you want to do with it after you have found it?

[MicheleC]

Oh I see. Well, I want to then find the record(s) with that most-recent date, sort them based on a different field (in case there are more than one), get the last one in that sort order, and then display some info from it in a custom dialog.

Maybe I'm making it too hard. I suppose I could simply sort ALL the records by date and then just go to the last one. I thought of that but it seemed overkill to sort everything just to find one record. But maybe that would that be a better approach?

[comment]

It may be a bit tedious, but any other approach would require adding either a summary field or a relationship.

For example, a summary field defined as Maximum of accession_date would return the last accession date in the found set (you'd still have to show all records before getting the value from the field).

OTOH, if you show all records and sort them by accession_date AND the other field, you don't need to do the find.

[MicheleC]

OK, that makes sense. So in trying to make it easier I was actually making it harder! Will take a shot at doing it that way. Thanks.