< Day Day Up > |
Data integrity is vital in a multi- user database. A well designed database, properly structured, will go a long way toward ensuring proper data integrity, but no database will ever be perfect. Pesky humans have a habit of introducing a certain " unpredictability " into the mix. Although $.02 may very well be a perfectly valid number as an invoice total, the truth that the invoice in question was actually $200 isn't something a database will ever be able to discern. Then there are cases where a client accidentally makes alterations across a number of records using an import or replace function. For cases like these, you need a mechanism to first identify and then undo changes. Possible problem records might be identifiable by date, by user, or by some other criteria. In some cases, maybe only a field needs addressing. The process of undoing changes is referred to as a rollback , and for it to be possible, you first need an "audit trail" of logged transactions in your database to provide the breadcrumbs necessary for a series of undo steps. Audit trails track the edits made to a database at the granular field level. Changes tracked usually include the field name , a timestamp, and user account for the person (or function) that made the change. Although FileMaker Pro doesn't have audit trail capabilities built in by default, it is entirely possible to build them. The following sections illustrate three increasingly complete techniques. Record Create/Modify Meta DataThe simplest way to track the evolution of one's data is to create fields for creation and modification events. This alone doesn't allow for rollbacks , but it certainly gives you visibility into the events of your database and provides a layer of accountability. This sort of data is not related to a given business or organization, but helps describe when and by whom data is entered into a database. It is often referred to as meta data : data about data. When building a system for multi-user deployment, we recommend establishing timestamps for creation and modification of records, along with account names . We don't recommend usernames. This enables you to track who's responsible and when edits have been made to your database so that you can, at a minimum, identify problems. For example, if one of your users consistently makes a data entry error, or if a bug in development led to wrong lookup values, you can isolate such records by timestamp and account name. CAUTION You might have noticed that we specified account name rather than username for these meta fields. Individual users can modify their names in the preferences dialogs of FileMaker Pro clients , and there's no corresponding authentication for such. Because this data isn't reliable, we always opt to use the account name.
Script-Controlled EditingA second technique for controlling edits to your database solutions is scripting- intensive , but allows for the most control. It's conceptually straightforward: Lock down the actual fields of your database in Browse mode and have your users make edits in global fields with a Submit or Cancel button. The script attached would then move the data from temporary fields into actual fields. This allows you to control, via script, any checks you might want to make on the data, and also allows you to write records to an audit trail database to record changes. One of the more difficult aspects of this approach is what to do with portals and related records. A technique that works well (but again will have you working in ScriptMaker quite a bit) is to use a temporary scratch table. Users place edits in its temporary child records and if they click Cancel, those records are simply discarded. Your audit trail would then need to track to which table a given row of data belonged. This scripted approach isn't for the faint-of-heart. You'll need to be quite adept with scripting, and this sort of approach will dramatically add to the time it takes for you to deliver a solution. The upside, of course, is a solid system that does everything it can to protect against honest mistakes ”both in terms of trapping data changes and providing a complete, deliberately designed user experience. Auto-Entry Technique for Audit TrailsThe third technique in building audit trails relies on the auto-entry options of FileMaker Pro and the capability for fields to modify themselves (see Figure 11.2). An audit trail should track when and by whom a change was made, and also the change itself. Figure 11.2. Notice that the Audit_Log field on the bottom has a chronological (time stamped) history.This technique might seem somewhat advanced, but it's actually quite simple. Given FileMaker's dramatically extended storage limits, you may opt to make this a standard feature in all your database solutions. You may see some performance issues arise as your database grows, so we recommend moving this data into an archive when (if) you need. The Audit_Trail field displayed in Figure 11.2 is nothing more than a text field with auto-entry options enabled. Specify that you want to have a calculation result auto-populate the Audit_Trail field, be certain to turn off the Do Not Replace Existing Value (If Any) option, and add some "seed" data to the field as well via the another Auto Entry option (see Figure 11.3). Figure 11.3. Notice that you need two auto-entry options enabled where the timestamp seeds the field.
Define your calculation as shown in Figure 11.4. Figure 11.4. Use the Evaluate function's optional trigger field parameters to cause the Audit_Trail field to be reevaluated.You need to combine a few different functions and elements to assemble this auto-entry calculation:
The seed data you added on the Auto-Entry Options dialog (creation timestamp) allows you to leave the Do Not Evaluate If All Fields Are Empty option turned on; otherwise you'd need to turn this option off to get the calculation to work the first time (when the AuditLog field was empty). Despite the triggers, it is the only actual field referenced in the calculation. CAUTION If you turn off the Do Not Evaluate If All Fields Are Empty option, you'll end up with a blank row in your audit log. Somehow that didn't feel proper to us, and theoretically it's possible for someone to create a record and leave it unedited for a period of time. Adding at least the initial timestamp to "initialize" the Audit_Log then at least offers more information. You'll want to consider some additional issues before using this technique in your database solutions. First, FileMaker Pro cannot recognize the current field name or field contents while a replace function is being performed. The audit trail will record that a change was made, but it will lack both the data itself and the field name in question. This same issue applies in the case of imports, and all script steps that don't actively mimic user actions. Set Field , for example, does not actively enter a field. In the case of using Set Field , the symptoms are identical to the case of a Replace or Import ; however, presumably because Set Field exists in a script, you could opt to record whatever audit information your system required. Second, keep in mind that your data still lives with the record in question. If you were to delete a record, you would presumably lose your audit trail. To preserve all audit trails and to ensure the capability to perform a rollback, we suggest writing a script routine that controls delete processes and properly records all data in an audit table before erasing it from your system. Related records work in the same manner: Their audit trail routines would live in their respective records, just as in a "parent" record. If you delete a related record, you will need to store that state in an audit table of some kind.
Creating Rollback FunctionalityRegardless of whether or not you choose to move your audit information into its own table or leave it in each record to which it pertains, a rollback follows the same basic principles. A rollback, true to its name, allows a database administrator, in backward fashion, to re-create the state of a database as it existed at any point in time. She can do so without having to resort to deploying a backup (which may not include the latest functionality of the system). This involves writing a script to walk through each record's audit trail (from top to bottom as an audit trail writes its data), using Set Field script steps, and re-creating a record at a given date and time. The logic relies on a loop that tests to see whether each iterative row in your audit trail data is older than (or equal to) the point in time you've selected for rollback. If the result of the test is true, then your script would be set to parse the data at the end of the line (using the Middle function), and by referencing the stored field name in that row, it would populate your data. CAUTION If your database relies on Set Field script steps (for possibly tracking various status flags or data you've scripted), don't forget that you need to re-create that information via other means. It is not just the data a user sees that needs to be rolled back. |
< Day Day Up > |