Build a Robust Logging System - ISO FileMaker Magazine

RATING: Intermediate
PLATFORM: Macintosh/Windows
VERSION: FileMaker 4/5
TECHNIQUE FILES: Log.fp3, Data.fp3

Your company's data is one of its most valuable resources. You back it up daily, if not several times a day. You do everything that you can think of to protect your files. But, often, you're still concerned that someone, perhaps a new user, could accidentally change, or worse, delete important records. Is there another safety net? The strategy presented here will enable you to create a system to track modifications to data and provide you with the extra peace of mind you need. This technique requires that you have the oAzium Events and oAzium String Functions plug-ins installed (you can download these at the Waves in Motion website: < http://www.wmotion.com >).

Imagine being able to run a script when a field is modified. Well, we know that we can do that with oAzium Events. But what can this do for me you ask? Well, you can log that action (field modification) to a record in a database, and log it in exceptional detail. You can choose to log the date and time it was modified, who modified it, in what file the field was, and even what layout the user was on when they modified the specific record ID. The best part is that you can also store the original value!

Depending upon the level of detail you wish to record, the resulting log file could potentially become your largest database very quickly. Lock this file down and don't allow deletes unless you are logged in as the administrator, and now you have a great troubleshooting tool. Let's get started and see how to pull all this together. The key is to keep your eye on the token value.

We invite you to explore what might be of help to you in your business by building your own FileMaker data modification logging system. We'll show you how easy it is. We have provided two files, Data.fp5 and Log.fp5, to assist you in understanding the methodology.

Since the Log.fp5 database might grow to an incredible number of records, if you want to quickly locate information, you might want to consider one of our companion products which allows you to filter portals -- oAzium Portal Filter.

We'd like to thank Jim Schliestett for inspiring this technique. In the spirit of the FileMaker tradition, we hammered it out and found a way to do what he asked. We actually surprised ourselves. We had one of those "Eureka!" moments. We're sure you'll find many other interesting uses for this novel technique. Please do let us know.

Explanation

For every field you would like to track modifications for, copy and paste this calculation into the validation calculation window of your field:

External("OzEv-AddEvent",
"script" & "|" &
"log_modification" & "|" &
"log.fp5" & "|" &
"log_modification" & "|" &
"N" & "|" &
Status(CurrentDate) & ":" &
Status(CurrentTime) & ":" &
Status(CurrentUserName) & ":" &
Status(CurrentFileName) & ":" &
Status(CurrentLayoutName) & ":" &
"address" & ":" &
id & ":" &
address
) = "Success"

The word "address" here is hard coded to pass the name of the field through the token. In this case the name of the field is "address." The next value that you might have differently in your solution is the record id, or what some call the primary id. This is referenced here as the field "id." The last item in the token string is the actual value in the field "address." Note that there are no quotes around both the "id" field and the "address" field, because we want to pass the actual values along.

Here is a sample of what this token string might look like:

3/10/2002:3/10/2002:Vince Menanno:Data.fp5:Data:address:20:2325 Hill:2345 Hill

Something to keep in mind is that the data is being held by the token for the script that is triggered when a record is modified. There is a suggested limit of 256 characters. So, it is best suited for fields that typically will only contain very little information, such as name, address, an inventory number, who changed it and when, and what was it before it was changed. This strategy is extremely powerful, and we're sure that you will discover other interesting ways of using the token to store information until the script that you asked to run calls for it again.

Let's continue to explore how this all works

If you haven't already opened the "Data.fp5" file, then please do so now. This example can also log delete actions as well. This capability only works if you are running FileMaker 5.5. This file opens with an auto enter password "user" (the master password is "master").

Once you have opened "Data.fp5," the "Log.fp5" file will open automatically because of the portal. In the "Data.fp5" file, click on the "Data" tab and make any changes you want to the current record. Make changes then simply use the tab key to tab from field to field. As you do this you will also notice the portal at the bottom actually logging the changes to each field. If you are using FileMaker 5.5 and have opened up the "Data.fp5" with the user password, go ahead and duplicate a record and then delete it. Now click on the "History" tab and look at all the changes that have been made to this file. Another useful plug-in here might be portal filter!

Now let's review the basic concept. When a field is modified and you exit the field with a tab or hit the enter key, an event is created to run a script in the "Log.fp5" file. The purpose of this script is to log the action that just occurred. The script "log_modification" will first prepare to log information about what that specific field contained before it was changed. Then it creates the entry and logs all the information. The last step is to understand at what point it was in the tab order. A script in the "Data.fp5" file called "navigation" will navigate you back into the correct tab order.

Now let's look at what you would need to do to your files to tie it into our "Log.fp5" file and instantly begin logging modifications.

In any file that you want to log changes of a field add the following 3 fields:

FIELDS:

gt_field:    global text field
gt_file:        global text field
gt_key:    global text field
----------------------------------
constant:    calculation field with a value of 1 and the result set to number

RELATIONSHIPS: (Name: Left Field: Right Field: File Related to)

Log key: gt_key: Key: Log.fp5
Log File: gt_file: file: Log.fp5

That is all that needs to be added to your files. You are ready to tie it all together now.

For every field for which you want to track modifications, add the following validation calculation:

Explore the fields in the Data.fp5 file and see how they work, and before you know it, you'll be adding detailed logging routines to your solutions!

Happy FileMaking!

Vincenzo Mennano <vince@wmotion.com> is the President of Waves in Motion, a full service development and consulting firm, but also one of the leading developers of FileMaker Pro plug-ins. Visit their web site at < http://www.wmotion.com > and "Imagine the things you can do!"

ISO FileMaker Magazine

Site navigation

User menu

You love new FileMaker information right?