FileMaker Tips

FileMaker 7: Recommended Audit Fields

ToolBoxIcon_smallI’ve been working with FileMaker 7 a lot these last 6 months. That’s every work day, several hours a day. And, I recommend that you go all out with the automatic audit fields that FileMaker 7 will keep for you. Put 10 fields into every table just to automatically track record creation and modification. Here is the creation half:

Creation Audit Fields.png

It’s often helpful to a user to see when a record is created and when it was last modified. You might see, for example, that this is a long-time customer. Or, an old customer who hasn’t had a new order in years. The magic word these days is context. And this kind of information provides context.

So much for the average user’s needs. As a developer, I also care a lot about trouble-shooting. Coming to the rescue in the rare cases (seriously) that my customers have problems they can’t solve themselves. These audit fields come in very handy when you are trying to figure out what happened. What went wrong?

Yes. User error is often the explanation – especially after a system has been working just fine for a long time. With audit fields, you can narrow down very specifically when something went awry and you can probably tell who was at the wheel when it happened.

In 7, you can not only get the user name of the person who created and last modified the record, you can get the account name. I like using the user’s full name as their account name. That way I can match the user’s account name to their employee record full name and set up their session with things like the contacts they’ve marked and can go considerably further than that if I want.

I want both user name and account name in case a user is at a different computer than usual or is signing in as an account name other than their own. Assuming an identity of sorts. Just as well have the extra info when it comes time to sorting things out. The date and time comes in handy also. It might be that at that particular date and time, a temp was in working. All part of the sometimes fun detective work that comes with the territory.

The New Timestamp field. I still haven’t figured out too many uses for this timestamp field type, but my guess is that it is great for calculating elapsed time that may cross over between days. The thing I don’t like is that I can’t get the field to be very readable in a list. The two sub-fields – date and time, don’t line up. For most purposes, I like the good, old-fashioned date and time fields better. But since it’s automatic, why not get it all?

Make sure to set those audit fields to Prohibit modification of the value during entry. You want to be able to rely on this information and don’t want it arbitrarily changed. Whenever you need a user-modifiable date, time or timestamp field, just create those separately even if you use the auto-enter creation field options for them.

One last thing. This is still just a thought, but I’m planning to try it out the next time I create a FileMaker 7 database from scratch. Create one or more template tables just for the field definitions piece perhaps. Then crank up FMrobot (on a PC) and have it create the 10 or so tables that you need – all with the one or more of your little templates. The basic template will have your 10 audit fields in it and a serial number field and a global for the serial number for starters. Until we can duplicate a table with the Define Database dialog, we can use FMrobot to help us out with the field definition piece at least.

1 reply on “FileMaker 7: Recommended Audit Fields”

Thank you for a great site. Being new to FM I have really enjoyed setting up a new dbase for my employer.
I had thought about setting up an area to check on who is entering what (I work from home and my work is 5 hours away) so your tip has now encouraged me to do it.
Thanks again for a great site

Leave a Reply

Your email address will not be published. Required fields are marked *