Welcome. My name is Fenton Jones, and I'm a FileMaker database designer and consultant, based in San Diego, CA. If you're in the area, drop me a line. For FileMaker developers, I have a previous free tutorial series, FM 101. You can read the articles online, or download the entire series as a FileMaker file. Most articles are linked to an integrated example file, included in the download. This is a new article, which you can download right here.

This article answers the question, "How do I compare the value in a field to the most recent previous one entered?" (or highest, or lowest).

In this case, we're dealing with dates, so we are interested in comparing the current date entry to the most recent date entered before. The date data (say 10 times fast) is in a "line items" file, with entries for several different people (or IDs). We want the dates for a particular person. Furthermore, we wish to use this data in calculations and Finds, so we would like it to be able to be stored.

Files
Almost everything is in the line items file, "Peoples_Dates." The only field in the "Persons" file is an auto-entered serial ID. There is a Portal on the layout, based on the relationship from the SerialID to a key field in People_Dates (which is just a regular text field).

The fields from that file are in the portal, for data entry. The relationship "allows creation of related records" so you can type into the last row of the portal to enter a new date. (There is a method to allow you to type into the top row for data entry, but that's another article. I'm trying to keep it simple here.)

Self-Relationship
The first thing we will do is create a self-relationship on the Persons ID in the Peoples_Dates file, "self_Person_FK" (FK is for "foreign key"). Since we want the most recent date, no matter when it was entered, we will check the Sort checkbox and choose the Date field to sort on.

Last Date Field
Next create a field for the most recent date (not counting the current entry). It's a date field, using the "auto-enter" by calculation checkbox in the Options. This will allow it to be stored. The calculation uses the little-known "Last" function, which is listed under "Repeating" functions. That placing is a left-over from pre-version 3, but it also works with relationships. The formula is: Last (self_Person_FK::Date_). This will put the most recent date for that person into the DateLast field.

Timing
But, you may notice, the current entry is the most recent date. Why doesn't it get that? Good question. It gets the earlier date because of the order in which FileMaker evaluates new entries. If you define a calculation field with the above formula, you will see that it becomes the current entry as soon as you exit the field. But in the split-second before that happens, FileMaker evaluates the auto-entry calculation, grabbing the value which is there.

Lookup (not)
This is also true for Lookups and for Validations. Validations using self-relationships depend on this timing (also another article). FileMaker evaluates all these before evaluating calculations for the current record. It is also the reason why I used an auto-enter by calculation instead of a lookup. If you use a lookup, then change the PeopleID key field, the Last Date will have re-evaluated, to become the current record's date field, not too useful.

If you go back and change the dates in previous records, then the Last Date field in later records is not going to re-evaluate. There is no way that I know of to have this happen without some kind of data-entry trapping, triggering a script to ripple the change up to the more recent record. That is not real likely to be needed in this case hopefully.