What’s so great about the Modification Date?

Problem:
Every once in a while, I have a client say, “I want to have the modification date on the layout.  Oh, but I only care when the <<Insert Field Name here>> has been modified”.

Filemaker has a built in auto enter option on a field called ‘Modification’.  This lets you create a field that gives you information about the last time the record was modified.  You can capture any of the following: User Name, Account Name, Date, Time or Timestamp.  This is a great function, but it updates the value whenever any field in the record is modified – in other words, it doesn’t give feedback as to which field was modified.

In one example, I was working on a contact database that had the usual complement of fields for First Name, Last Name and so on.  There was also a field containing that contacts current balance from the a quickbooks file.  This database  had a nightly batch script that looped through all the records, and updated the balance field with the current info from Quickbooks.  As a consequence, every record in the database always had the same modification date.  That is the last time the batch script was run.  The user wanted to display the last time the address was updated, not the entire record.

The root problem, again, is that the modification auto enter option is all or nothing.  You can’t narrow the function down to only track modification of certain fields. Here I present a way to create a field to track modification of specific fields.  This is by no means original, but I find it extremely useful.

Solution:
In all databases I create there is a field called z_ModTS, it is your regular TimeStamp field.  The field is set up to auto enter the modification Timestamp of the record.  Next we are going to create a z_ContactModTS field.  This one is going to track the changes in the specific fields we are interested in.  The fields we are going to track are the Email and HomePhone fields.

For the z_ContactModTS field, use the following auto enter calculation:

If(Phone1&email;Get(currentTimeStamp);Get(currentTimestamp))

(Don’t forget to check the ‘Replace field contents’ box)

This will change anytime the two contact fields are modified.  But not change if we change any other data in the record.

Discussion:
This technique isn’t applicable in every database you will build, but it is a great tool to keep in the tool box for when it is appropriate.  Often this is in solutions that use server side scripts to update records.
Another way to acheive the same effect could be to update the records in a temporary table, import the updated info into the real table and no performing auto enter when doing the update.  Of course using this method can be useful sometimes, but it’s overly complicated.

A more important  issue is that this is best used on managed mature systems.  The developer must manually add all the fields that are to be tracked into the modification field.  While developing a database, you are constantly adding new fields, it is too easy to forget to add fields when creating them.  When I set this up I usually use the get field name design function to get a nice list of all the fields.  Then delete any fields not needed in the list.