When creating a new table, it is often thought good practice to include fields to store the record creation and record modification timestamps (date and time). These fields are timestamp fields with the field option to automatically enter the Creation Timestamp or the Modification Timestamp. You would also check the option to "Prohibit modification of value during data entry" to protect the data from change.
Developer Quiz Question
Where is the Creation or Modification Timestamp drawn from? What is the potential problem with this? (Check the answer at the end of this article)
The problem with the modification timestamp is that it records when the record was last modified. That is, when any field was edited, even if that field was edited and changed straight back without committing the change.
So the question is how do we track when any single field has been changed? An example may be that we want to track when a status field was last modified. If the status was changed from 'interested' to 'enrolled', we would like to know when it was changed (and possibly by whom).
There is a simple technique that uses the Evaluate function. The Evaluate function evaluates an expression as a calculation with optional fields to trigger the calculation to update. For example,
Evaluate ( "Get ( CurrentHostTimeStamp )" ; Status )
Notice that the expression is represented as a text string (in quotes). Note also that we have supplied one field name as a trigger. This means that every time the Status field is changed, the expression will be evaluated and the current value returned to the field.
So where do we put this? In this case, we could set up a timestamp field named Status_LastModified and then set an auto-enter calculated value that would be the expression above.
It is important that we uncheck the 'Do not replace existing field value (if any)' checkbox so that the calculation will update. Having set this, any change to the Status field will prompt an update of the timestamp in the Status_LastModified field.
Notice also that we are extracting the timestamp from the host (perhaps a FileMaker Server) and not the client. This ensures better consistency of timestamps where the client time may be incorrect or where clients are in different time zones.
So how do we record the person who changed the status? Here we are assuming that all users log in to the database with their own account and password (considered best practice in security). Therefore, we can set up another text field called Status_LastModifiedBy and using an auto-entered calculation:
Evaluate ( "Get ( AccountName )" ; Status )
Finally, if you don't need the modification data in separate fields, you could set up a single text field that uses an auto-entered calculation:
Evaluate ( "GetAsText ( Get ( CurrentHostTimeStamp ) ) & \" \" & Get ( AccountName )" ; Status )
If you extend this technique further, you can also record the data entered in the field and you are well on your way to setting up an audit trail on a field.
Developer Quiz Answer
The date and time for the record creation or modification is drawn from the time set on the client computer. This means that the either timestamp can be 'wrong' if the client computer date and time is incorrect (whether by accident or design).
The creation timestamp can be drawn from the database host (say the FileMaker Server) by using an auto-entered calculation:
Get ( CurrentHostTimeStamp )
This is also important if you want to standardise time entry between clients existing in different time zones.
There is not a similar simple solution for the modification timestamp apart from setting up a modification field as described above with all fields as triggers. However, the modification timestamp is of more use to show how long a record has remained unmodified rather than when it was last modified.
