I've tried this out in FileMaker 7 and 8, and I get pretty much the same
behavior.

Create a table, "Entries," with the following:

TransactionDate (date)
EarliestTransaction (calc, global, date result) = Min(TransactionDate)
LatestTransaction (calc, global, date result) = Max(TransactionDate)

As you enter records, you'll see Earliest and Latest return the same value,
the value of the current record. Fair enough; it must be looking at just the
current record's information, not the range of records in the table.

A quick check of the online documentation reports, for Max(), reveals:

....
* a field in matching related records specified by (table::field), whether
or not these records appear in a portal.
<<<

So, to test this out, I create a new field in Entries:

c (calculation, number result, global) = 1

Then I create a new table, "Peek," with the same "c" field and calculation.
I relate the two tables via "c" using the "all records" operator ("X"). Then
I add to Peek these two fields:

EarliestTransaction (calc, global, date result) =
Min(Entries::TransactionDate)
LatestTransaction (calc, global, date result) =
Max(Entries::TransactionDate)

Just to be sure I have the relationship right, I put a portal to Entries
onto the Peek layout. Sure enough, all the TransactionDates are showing in
the portal, but the Earliest and LatestTransaction fields are blank.

Thinking, "Well, I better force FileMaker to update those values," I turn
off the "Do not calculate if empty" option on the Earliest- and
LatestTransaction fields. This now causes the correct values to appear.

However, if I add new dates to the Entries table, the values in
Peek::Earliest/LatestTransaction do not update. I say, "Oh, this is strange.
Doesn't FileMaker care if the related table changes?" So, I add

NumTransactions (calculation, number result) = Count(Entries::c)

This updates now, each time I add a record. I scratch my head and ask myself
what is different. In a flash of inspiration, I change NumTransactions to
global, and now I see it. When a field is global, it doesn't update. When it
is not global, it does.

So here is bug/question #1:
Can anyone explain why a global field shouldn't update in this situation?

Part II
======
Still wanting to have the values of the Earliest and Latest transactions
available to me within Entries, and having learned my lesson about needing a
"related" table in order for Min() and Max() to work across a range of
records, I decide to create a self-join in the Entries table, c to c, all
records. Then I add the following fields to the Entries table:

EarliestTransaction2 (calc, unstored, date result) = Min
(EntriesClone::TransactionDate)
LatestTransaction2 (calc, unstored, date result) = Max
(EntriesClone::TransactionDate)

I'd rather they be global, makes more sense to me that way, but I will
settle for what I can get.

Guess what? They don't update as I enter new information. Oh, I can coax
them into updating by flushing the cache and switching between browse and
layout modes. But they don't update predicatably, not in the fields
themselves, and not in the Data Viewer.

So here is bug/question #2:
Is there a reason why FileMaker should treat self-joins differently than
standard ones?

My final effort to get this to work was to add a third pair of fields to
Entries:

EarliestTransaction3 (calc, unstored, date result) =
Peek::EarliestTransaction
LatestTransaction3 (calc, unstored, date result) = Peek::LatestTransaction

I didn't want to think about the complications that would be introduced if
the Peek table had multiple records, but thankfully, there is just one
record in Peek, and so it's unambiguous what value should be used.
Unfortunately this third method also does update well.

So my ultimate question is:
How would you get the earliest and latest dates in a given field, into two
global variables, into the same table as the dates themselves? And can you
make it happen automatically, without scripting?

Bill Marriott wrote:

A global calc field only updates when a local record's field is
specifically changed and that field is referenced by the global calc.
The NumTransactions calc that you have is unstored and refers to related
values; its change is only initiated by its appearance on the layout or
its specific reference by a script. The global calc, meanwhile, is
treated almost as a stored calc in that its mere appearance on screen
doesn't trigger its update; only a physical change to a referenced local
field can change it; the 'inferred' change of NumTransactions won't do it.

I'm sorry this explanation isn't great. There's a word I've been trying
to think of to describe the character of the NumTransactions field.
'Inferred' isn't exactly right. Suffice it to say that this is not
desirable behavior in my mind, but FileMaker Inc does consider this to
be expected behavior and not a bug. While I was looking forward to
having global calcs in FM7, I have found them for the most part to be
useless.


Agreed

There probably is a reason, but not a good one. I hope they fix it some
day, but I am accepting that this might just be one of those things we
have to live with in FM, that we will forever have to work around.

If you were to somehow refer to a local field in your calc, then the
calc would refresh itself any time that local field was changed. For
example, make EarliestTransaction2 (calc, unstored, date result) =
Min(EntriesClone::TransactionDate) + (case(TransactionDate, 0)
This should force the min() calc to update each time the TransactionDate
field is specifically updated in the local record. Not a perfect
solution for every situation, but it might help you.

I'd also report it as a problem to FileMaker
<http://filemaker.com/company/product/problems.html> because it really
does deserve to be "fixed".

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg (818) 883-2846
FM Pro Solutions Los Angeles, California

FileMaker 7 Certified Developer
Associate Member, FileMaker Solutions Alliance

What if a "summary" field was used?
Michael Paine

Howard Schlossberg wrote: