We've all been there: a data record no longer exists or has been changed, but nobody wants to have done it. This is particularly annoying when it is no longer known which values were previously in the fields. If they exist, a backup can help in such cases, but restoring a backup is usually quite time-consuming.
It is much easier to deal with such problems if the change to a field value is recorded directly (or "tracked"). In earlier FileMaker versions, this could be realized using input dialogs, for example, by clicking on the [OK] button and a Script was called to record the process. With the newer FileMaker versions, the whole thing is possible thanks to Script trigger has become much easier. This article shows how to do this with a corresponding example file.
The advantages of recording events
If field events and routines are consistently recorded in FileMaker databases, there are tangible advantages during operation. Each time a change is made, the event is recorded in a log that can be viewed as a complete log or linked to another log. Database can be displayed. For example, all customer-related events can be displayed in a layout and an overview of all events that have been carried out in connection with the customer called up can be obtained. The Table for the event log contains standard information such as date, user name, table or Primary key for field changes, the actual field values before and after the change was made. This enables further functions such as the recovery (unlimited undo in any order) of changed data - perhaps remotely comparable to "visual voicemail for data" ;-)
Let's get to work: What is needed?
It is possible to develop an event log for an existing FileMaker solution with manageable resources. First of all, a new table is required that contains all the relevant fields for managing events. In the example file, the table is called "Event log".
Each recorded event corresponds to a data record in this table, the automatic primary key is called _pk_Ereignis_ID. The reference key to which the event relates is saved in the key field _fk_Fremdschlüssel. For example, if a field in a customer database is changed, this field is filled with the primary key of the customer database. Which key is in this field is entered in the field Foreign key written.
For field changes, this table also contains the fields Field_content_before and Field_content_afterwhich contain the field content before and after the change.
Two scripts are enough.
To record an event, two scripts are now required in addition to the table: One script is used for the pure entry in the table Event log and is to be called up with script parameters, and another script is required for recording field changes, which is called up via script trigger on the respective field.
Entering an event
Let's first look at the pure entry script [ts.event log], which accepts the following script parameters:
- Foreign key
- File
- Origin (script/field, etc.)
- Status (OK, error)
- Table
- Text
- Transaction type
- Foreign key type
- Field content before
- Field content after
The script itself is simply structured, writes the values contained in the script parameter first to variables and then to a new data record in the Event log table.
This script can be called by any other script that is to make entries in the event log. In the sample solution, the recording of field events was also implemented, which this script also calls up.
Script [ts.record_field_event]
The script ts.record_field_event is set for each field as a script trigger [OnObjectEnter] and [OnObjectExit]. It can accept a tab of a tab control as an optional script parameter if the field is located in an area of a tab control. This is necessary so that the script can switch to the correct tab after the log data record has been created. Register control on the target layout.
The script first checks whether the system variable $$field is filled or empty. If this system variable is empty, the user has entered a field and the script fills the system variable $$field with the active field name and $$field_content_before with the field content before the change. The script is then exited, as the script does not have to do anything else when entering a field.
If the system variable $$field is already filled, the user may have changed the field content and is just leaving the field. The script now checks whether the field content has been changed and creates a data record in the event log if a change has been made by calling the subscript ts.event log. If the field is in a tab control and the name of the tab was supplied as a script parameter, the script now switches to the specified tab, as otherwise it would switch to the default tab control after the entry. Finally, the script deletes the variables again 1TP4rider, $$field, $$field_content_before and $$field_content_afterand the game can start again from the beginning, because the system variable $$field is now empty again.
Assign fields with script triggers
In the last step, the desired fields must now be provided with script triggers so that the entry in the event log can take place when a field is changed. Two script triggers must be set up for each field, each of which calls the same script:
- WithObjectEnter ["ts.field_event_record"; {parameter: "tab"}]
- WithObjectExit ["ts.field_event_record"; {parameter: "tab"}]
The "Tab" script parameter is optional and is only specified if the field is located in a tab control, as in the example file. The name of the tab is specified, which is used in the Inspector under the [Position] tab in the Name must be specified.
Example file for field value recording
At the end of this article you can download a sample file for FileMaker 12 in which the event log with field value recording has been implemented. You can use this sample file as the basis for your own database or to transfer the functions to your own FileMaker solution.
Download sample file for FileMaker 12
How to transfer scripts from the sample file to your solution
Scripts can be easily transferred from one FileMaker solution to another via the clipboard. Simply open the dialog Manage scriptsSelect the desired script with the mouse and copy the script to the clipboard using the [Edit - Copy] menu or [Ctrl/Cmd-C] key combination. Then open the same dialog in the target solution and simply paste the previously copied script using the [Edit - Paste] menu command.
To transfer tables from the example file to your solution
Tables can be inserted into an existing solution in several ways. The easiest way is to import the table using the [Import...] button in the Manage database. All tables to be imported from the source solution can then be selected in a subsequent dialog. This method is therefore particularly suitable if several tables are to be imported at the same time. If only one table is to be imported, this can also be done via the clipboard like a script. If a table is to be imported together with its data, this can be done with the command [File > Import records > File]. In the import dialog, the target table New table must be specified.
How to transfer value lists from the example file to your solution
Value lists can be easily imported from one FileMaker solution to another via the clipboard. Simply open the dialog under [File/File > Manage > Value lists], select the desired value list and click on [Import]. Value list with the mouse and copy the value list to the clipboard using the [Edit - Copy] menu or the [Ctrl/Cmd-C] key combination. Then open the same dialog in the target solution and paste the previously copied value list using the [Edit - Paste] menu command.
How to transfer your own functions from the example file to your solution
In the [File > Manage > Custom functions...] dialog, click on the [Import] button and select the sample file. Select the desired custom functions in the following dialog and confirm the dialog. Please note that this function is only available in FileMaker Pro Advanced, but not in FileMaker Pro.
Markus Schall has been developing individual databases, interfaces and business applications based on Claris FileMaker since 1994. He is a Claris partner, FMM Award winner 2011 and developer of the ERP software gFM-Business. He is also a book author and founder of the M. Schall Publishers.
Great thing, helped us a lot. Many thanks for everything!