Bob-

Outstanding. I have been picking this apart for a week now, and the principle behind it can be adapted in so many ways!

One suggestion that I have is incorporating Table name and serial num (or tsMod) or something on the lefthand side for each audit value. This would allow a calc in an Invoice file to display a value list of all line item edits in their order.

Just a thought...thanks for the inspiration!

-Raz

Has anyone tried this using IWP? I'm using a different solution (one that uses a custom function and a single audit field) and it's behaving really weird under IWP (most field changes are not tracked, and the only one that is tracked shows up multiple times).

See this thread for that discussion: http://fmforums.com/forum/showtopic.php?tid/175342/

Bob,

I've actually completed a revision specifically designed for easing use of the audit trail in multi-table databases.

To facilitate this, I:

A. Moved most of the really complex functionality to "custom functions". I felt this was necessary to make it less likely someone would mistakenly alter the calcuation descriptions when recreating fields in a new table to be audited. I feel this also makes it easier to understand the overall logic of the application.

B. Created a high-level flowchart to help explain overall flow of execution. Actually, this is where I STARTED my work, but I expect to provide this back with my revision as part of the documentation.

C. Added two "options" for auditing fields that are listed on a specific layout. The user indicates the layout by entering it's name on a global field. This avoids the need for an administrator to be mucking around in calculation definitions to set this option appropriately.

D. Revised the calculation for "all fields in the table" to accept the name of the table from a user-specified global - again, avoid the need to have people working within the calculations.

E. Revised the calculation for "all fields in the table" to EXCLUDE fields based on field naming CONVENTIONS rather than specific field names. I use a set of naming conventions based on the Core Solutions conventions, and this approach reduces my need for maintaining the audit trail functions considerable. Basically, fields are excluded from this list if they are named as calculations, summaries, "interface only", or key fields. I know this won't fit EVERYONE's naming conventions, but I think the principle could be easily modified to match a different set of conventions.

I'm going to need a little time to "clean up" the presentation of these modifications so it's digestible for others, and time is short now... might take me a couple of weeks. In the meantime, I have a test situation with a client that will provide some useful feedback on whether I've done a good job reducing the possibilities for problems.

Hi Stat Larry,

Would you be willing to share your modifications? I really like the idea of encapsulating the audit log calcs into custom functions whenever possible...

Has anyone tried this using IWP?

After reading the entire thread more carefully, i see that this solution DOES work under IWP...sorry for missing that the first time.

we use alot of scripts to enter new records into filemaker. I've noticed that if say 5 records are entered through a script that this audittrail skips tracking the information. I'm thinking maybe clear the current trail and recapture all of the fields via script unless someone has a better way of doing it. anyone?

The most recent version that I posted has the provision for disabling the audit trail when running scripts in order to speed things up. The script can then handle the updating of the audit field by itself, and then turn the audit trail back on when exiting the script.

As for the audit trail not working in your script, I haven't encountered this before. Could you provide a bit more information about what you were doing?

bah i got a lot on my plate lately developing this thing, barcodes are my first priority then i'll get back to the audit trail. I most likely implemented it wrong, let me fully check it before asking any more questions.

ok i fixed things and they work absolutly great now! just one question, is there a way that i can track record creation/deletion for a portal or maybe a script to add a line to the audit trail when items are added to the portal?

If you have the audit trail fields set up for all of the tables of your database, all record creation and editing will be tracked whether entered directly or via a portal. However, the audit trail info will always be kept in the actual record where the changes were made, not in the parent record. However, you could do a bit of scripting to browse through the portal record audit trail fields from the parent record.

This solution appears to keep the audit data in the same file as the main records data, is there a way to have the audit log in its own file?

Reason I ask is that I have a DB I am converting from FM6 that has 300,000+ records and is about 500MB in size (all text), I can only imagine it would double or more in size if the log data is in the same file. If anyone has doen this with this audit log and woudl care to sher please.

I am new to FM8.x but have used FM 5.5 for quite some time.

Cheers

You can create a housekeeping script that exports the data from the audit trail field to an external file and then clears the audit trail field. You just run the script periodically or use various scheduling tools to run the script automatically every day/week etc.

I love your work, You have saved me enormous amounts of time.

Embarrassing how time gets away...

Now I'm finally into another project that will perhaps use this "audit trail" functionality, so am revisiting.

I've attached some databases that demonstrate my reworking of Weaver's basic function. I'll apologize in advance for not YET having time to closely review the functionality here... I'm expecting to get that review finished in the next couple weeks. Hopefully, what I've provided here is NOT buggy...

audit_trail_demo_2006_03_15.zip

Hi Larry,

I tried to have a look, but your files are password protected.

Question: I note from your accompanying text files that you have used a custom function. Is there an advantage to using a custom function other than saving time entering the formula in each table that needs to be audited? I've seen a number of audit trail solutions that use custom functions, but so far I haven't been able to determine what advantage they offer.

Bob,

So nice to hear from the MASTER!

Geez, I apologize if I packaged up some password-protected files. Give me a couple days. I'll provide a totally open set.

I think I saw the main advantage of using custom functions to be "easier repeated application"... as you suspected. I felt this advantage would be big enough to be worthwhile, and it took quite a bit of dinking around to get the custom functions working properly. Not really "breaking new ground", but a lot of fussing with syntax details, etc.

Got to say that I really loved your original idea and felt this was potentially really useful.

Ironically, I provided this to a client who's administrator never seemed to take the time to learn the setup process for the auditing. Even though I really tried to provide alternative setup approaches to match different working preferences...

In this copy of the files, I've set the "bc_db_template.fp7" file to automatically apply the "administrator" login. I believe this should provide full access to the provided files...

audit_trail_demo_2008_06_09.zip