Yet another Audit Trail

[BobWeaver]

Hi Christian, Thanks for the feedback.

First of all, attached is a new version which fixes a bug that I noticed.

2005-06-21, Version 3.02:

-Corrected a bug which causes the audit to fail if one field name is a substring of another field name. Example: Field Text is a substring of field Text1. The change affects the formula for field atChanges.

-Revised the timestamp format to use the file or system settings. This simplifies the atTrail formula. The format was originally hard coded to force the columns to line up, but this can be achieved by setting the system date and time formats to suit.

Now to answer your questions:

-When importing, did you select the "perform auto-enter...." option? Otherwise the audit trail functions won't calculate.

-The creation of new records is problematic with all audit trail implementations that are based on autoenter calculations. My implementation seems to detect the creation of new records in most cases (including several where previous techniques failed), but apparently there are situations where it doesn't. I'm still experimenting with this in a new large project, so I may uncover a few answers in the process. If you are scripting the import, then one solution is to add a step to the script to append a timestamp and the text "<record imported>" to the audit trail field in the imported records.

-If you create autoenter fields with the "evaluate always" option, the precedence order will affect the audit trail. Filemaker determines the order in which the autoenter fields will update. It was much easier in the versions prior to 7 because you were not allowed circular references. But, now you can have circular references, and I don't really know how Filemaker determines the evaluation order. The way I set up the audit trail formulae, they normally execute last, but you have apparently uncovered a situation where they don't. Possibly, the audit trail fields will have to be defined after all the other autoenter fields have been defined. You could try deleting at least one of the audit trail fields and then adding it in again, to force the evaluation order to change.

AuditTrail-r3_02.fp7.zip

[Christian Coppe]

Hi Bob,

Thanks for the suggestions. Regarding the import of new records, I now created a small script which

-import a new record (only one) with "perform auto-enter" option

-cut the Text1 field

-paste the Text1 field

-go to the next field

And it works !

I will test your other suggestions.

Christian

[BruceJ]

This is an incredible piece of work I'm still trying to get my brain around. It's forcing me to learn about the newer functions I've been avoiding - I use v8, but my brain is still on v6!

I ran into this issue:

Calculation fields that depend on another field are the ones that show up in the audit trail with the data entry occurs in the leading field.

For example:

Date Field for Brithdate:

demo_DOB: If(${demo_DOB}>Get(CurrentDate); ${demo_DOB}-(36525) ; ${demo_DOB})

set to AutoEnter Calc - replace existing

Calc Field for Age from Birth Date:

demo_age.c: If(IsEmpty(${demo_DOB}); demo_age.c; Year(Get(CurrentDate)) - Year( ${demo_DOB}) - (Get(CurrentDate) < Date(Month( ${demo_DOB}); Day( ${demo_DOB}); Year(Get(CurrentDate)))))

set to AutoEnter Calc - replace existing

[color:red]When you enter the birthdate, the Age is the only thing that shows up in the audit trail.

If you delselect the age calc to be audited and only have the birthdate, then nothing shows up in the audit trail when you change the date of birth.

Any sugestions?

I know the above calcs seem cluttered for a simple date of birth and age calc, but they are set to correct for people'd common data entry error in not entering the century in the date of birth - and to allow for a user to skip the date of birth and just manually enter the age in the age field.

Also - in a solution with many tables - I assume I need to create these in EACH table and there's no simple way to have an audit trail collectively monitor ALL of the related records.

Again - great work! You're definitley fit into the genious profle in my book.

Edited January 19, 2006 by Guest

[BruceJ]

I made a little change that I find handy.

Often, I need to look at a database and see all the records a specific user has made. Or - I need to see what records were changed on a specific date.

So... I simpley added paragraph returns in the system so that I can then use a relationship and use the Account Name or the Date as a Key to view all of the related records.

[color:blue]_________________

1/19/2006

6:16:05 PM

Bruce Johnson

demo_gender: [empty] >> "Male"

_________________

1/19/2006

6:15:48 PM

Bruce Johnson

name_first: [empty] >> "Bruce"

name_last: [empty] >> "Johnson"

name_middle: [empty] >> "Henry"

_________________

1/19/2006

6:15:24 PM

Bruce Johnson

demo_DOB: [empty] >> "?"

Here's the changes I made to the field "atTrail":

[color:purple]Evaluate (Quote (

Let([dup=(not IsEmpty(atTrail) and tsCreated=Get ( CurrentTimeStamp ));

tsFormatted=GetAsText(Case(dup;tsCreated;tsModified))];

Case(

GetAsNumber ( GetAsTimestamp(LeftWords ( atTrail ; 2)) ) = GetAsNumber ( LeftValues ( atChanges ; 1 ) );atTrail;

dup;tsFormatted&"¶"&Get ( AccountName ) &"¶" & gTab&"¶" & atTrail;

IsEmpty ( MiddleValues ( atChanges ; 2 ; 1 ) );atTrail;

[color:Red]"_________________¶" & GetAsText(Get ( CurrentDate )) & "¶" & GetAsText(Get(CurrentTime))

&"¶"&Get ( AccountName ) & "¶" & gTab& MiddleValues(atChanges;2;999999) &

atTrail)

) )

; atChanges)

Do you forsee any problems with this?

[BobWeaver]

Hi Bruce,

Glad you find it useful.

Re, your first post:

1. I'm not familiar with FM8 as I'm still using version 7. It appears that you are using variables rather than fields in your calculation? Maybe this has something to do with the problem. Or, maybe there is some other difference in how 8 behaves.

I'll play around with this and see if I can duplicate the problem in version 7.

2. Yes, you will have to repeat the all audit trail fields for each table that you want to audit. It's not too bad since you can copy and paste the formula from one field definition to another.

Re: Your second post:

I don't foresee any problems with what you did. The only fields you don't want to mess with are atOldData and atChanges. atTrail is fairly forgiving. What you did is a good idea. I've been using this audit trail technique in a database we set up for a major project for close to a year now. It's been very useful, but we've found, as you did, that the format could be improved to make it easier to find changes by date or by user.

BTW are you using the latest version (3.02) that I posted? Changes to the forum editing behavior prevented me from overwriting the version in the first post. So, I had to post the updated one further down the thread. I see that this is fixed now, so I added the link to the first post.

[BruceJ]

BTW are you using the latest version (3.02) that I posted?

Yes, thank you I am using v 3.02 although I've never used a repeating field since FMP v3 nor have I used IWP so some of the changes that were specific to those areas I'm probably not taking advantage of.

Reguarding #1 - the birthdate/age example. I can see how this would happen since the age field does change as a result of the input in the birthdate field - i was just hoping this was some common thing and you might have foudn a solution. I'm not using script parameters or anything specific to FM v8 (I actually made all of it in FMP v7. The "$" signs appeared after I started using FMP v8 Developer - I'm not sure why they appeared or what they mean... 'Gues I have to figure that out!

Edited January 22, 2006 by Guest

[BruceJ]

Ok - I finally discovered a solution to a thing that was driving me crtazy. I created a file with several tables, each with an audit trail in them.

Problem I found was that the field at_AllFileds only returned the fields form the first table created inthe database. You have to go in an change the part "FieldNames( Get(Fielname); ____" to specifiy a layout and then make sure that ALL of your fields for that table are in the layout.

Is there another way around this? This isn't a showstopper, just another thing to be aware of.

[BobWeaver]

When I set up a multi-table solution for my current project, I created a script to fill out the atFieldList field. I will post an update here when I have a chance to get back at it again. I'm having trouble finding spare time these days. :)

[Stat Larry]

Bob,

I believe after a couple days of focused attention I now really pretty much understand how your proposed auditing mechanism works. I REALLY like the functionality provided and REALLY appreciate your sharing it...

However, I've noted a few things I find a bit confusing....

A. It seems that the atOptions field is never set anywhere? Or at least isn't provided on any of the layouts provided in your example? And yet there are at least two places where a CHOOSE function is implemented that depends on the value of the atOptions field. Comments?

B. The atFieldList_N_reps field is defined but never seems to be used. Nor does the "Options" value list or the CHOOSE functions that alter the construction of field lists according to atOptions values seem to offer any use of this field. I'm assuming then that this represents an idea that was never really implemented in your example?

C. The calculation definitions for BOTH the atFieldList and atFieldList_All_Reps field contain a CHOOSE function that constructs a field list based on the value of the atOptions field, but these two versions of the logic are slightly different. At least for me this makes it hard to understand what the result of the combined logic is... I've included my slightly reformatted and commented versions of the definitions below:

//atFieldList reformatted

//Formatted for ease of visual interpretation after transfer to text editor

/*Function of this CHOOSE is paralleled in defined calculation of

atFieldList_All_Reps - but in a slightly DIFFERENT fashion */

[color:red]Choose ( atOption;

""; /*No option in 'Options' value list

matches a selection of 'zero' */

atAllFields; //Option 1 implies 'All fields'

atFieldList_All_Reps; //Option 2 implies 'All fields and repetitions'

atFieldList; //Option 3 implies 'Custom List'

atFieldList_All_Reps //Option 4 implies 'Custom List and repetitions'

)

//atFieldList_All_Reps reformatted

//Formatted for ease of visual interpretation after transfer to text editor

Let([/*Function of the following CHOOSE function is similar, but slightly

DIFFERENT than similar function in defn of atFieldList field */

[color:red]FL = Choose(atOption;

""; /*No 'zero' option currently listed in

'Options' value list applied by user in

setting auditing options */

atAllFields; //Option 1 implies 'All fields'

atAllFields; //Option 2 implies 'All fields and repetitions'

atFieldListManualEntry; //Option 3 implies 'Custom List'

atFieldListManualEntry); //Option 4 implies 'Custom List and repetitions'

.

(calculation definition continues)

.

I have NO indication that item C causes "bugs". However, for my own use I'm thinking of restructuring the "field list" fields a bit so that this CHOOSE logic is applied ONLY in ONE location. I think this will make it easier for me to be dead clear about how the functionality of the audit trail is working.

Specific Questions

I had TWO thoughts about this effort on my part:

1. I'm happy to contribute my revision back to this thread if that feels desirable. Please let me know if this seems desirable?

2. I thought I should check whether you or anyone else had ALREADY done something like this to avoid "recreating the wheel". If anyone HAS done this and is willing to share, could they please indicate so?

Planned Changes

FYI, here's what I was thinking of doing with regard to item C listed above...

Restructured "field list" fields:

1. atFieldListManual (custom field list entered by user)

2. atFieldListManualAllReps (custom field list expanded for repetitions)

3. atFieldListTab (all fields in table with removal of fields used to implement audit trail)

4. atFieldListTabAllReps (all fields in table with removal of fields used to implement audit trail, expanded to account for defined repetitions)

5. atFieldListFinal (Based on the value of atOptions field, this field will contain the appropriate list taken from one of the four fields listed above)

atFieldList_All_Reps would be changed to simply take it's input field list from the new atFieldListFinal.

Again, I really appreciate access to the thinking and work that you must have put into getting this auditing approach as nicely refined as it is. If I can contribute something back that is helpful, I'd be pleased...

[BobWeaver]

Hi Larry,

Thanks for the feedback.

As I recall, atOptions is set from a set of radio buttons on the "Select Fields" layout.

atFieldList_N_reps was provided as one possible way of expanding repeating field repetitions, but it wasn't compatible with the other automatic options. But, I left it in there anyway in case anyone was interested.

As for the two choose functions, I recall at the time I was setting them up that they were not all that easy to follow. If you look at the "Select Fields" layout and trace through the logic (or lack thereof), it may eventually become apparent what I was trying to do. :

You are more than welcome to post your changes back here.

However, for a practical implementation, I would get rid of atOptions as well as most of the other calculated fields that build the atFieldList. After using it for about a year in a fairly large project, I ended up using a script to populate the atFieldList field. I find it much more practical. It can be customized to ignore specific types of fields. For example, it's redundant auditing both a calculated field and its referenced fields.

I don't have a lot of spare time these days, but I will try to post a revised version that includes the script ASAP.

[Stat Larry]

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.

P.S. One question: Are there problems with this "audit trail" approach if field values contain unmatched quotes?

[SteveB]

Larry: You might want to look at Core's recently released white paper on doing an audit trail. You can find it at Audit Trail

It looks to be relatively easy to implement on a multi-file solution.

Steve

Edited March 15, 2006 by Guest

[BobWeaver]

Okay, I finally updated with what I think are significant improvements which will make this more useful for a real life application. See the first post in this thread for the new version.

Version 4.0 new features:

- Now compiles the list of fields to be audited using a script which allows more flexibility.

- Allows the audit trail function to be switched on and off under script control. This allows scripts which process large numbers of records, to run significantly faster.

I didn't include BruceJ's suggestions (i.e., reformatting the atTrail field to improve searching by date or user) this time around, but they can be easily incorporated.

Larry,

Unmatched quotes should not cause a problem, because all the field data is quoted, and any special characters (like quotes) are escaped. This was a problem in an early experimental version, but that one never saw the light of day.

[BruceJ]

Again - Bravo!

I like this, it keeps getting better!

I think the CoreSolutions Audit trail is nice, but I like this one better for my purposes. This oneis more passive in the audit process.

[BobWeaver]

Hi, Bruce,

I was thinking about the changes you suggested a few weeks ago: putting line breaks between the time, date and username, so that the field could be keyed to a related date/user field to simplify searching. I kind of like my existing format with the date and user name on the left and the list of changes on the right. But, it occurred to me that you could just create a separate key field with date and username etc., that could be the foreign key field for searching like so:

atKey =

Let([

last=GetField("atKey");

Name=Get ( AccountName );

revTS=GetAsTimestamp(Max(tsModified;tsCreated));

revDate=GetAsDate(revTS)

];

Case(Position(last;¶&Name;1;1);"";Name&¶)&

Case(Position(last;¶&revDate;1;1);"";revDate&¶)&

Case(Position(last;¶&Name&" "&revDate;1;1);"";Name&" "&revDate&¶)&revTS&¶&last

)

This is set up so that if a user makes multiple changes to a field, their name only appears once. Likewise, multiple changes on a single date will only enter the date once. This will keep the key field to as small a size as possible.

This key field would allow you to search by timestamp, date, user, or user + date. And, the relationship could be set up as a range allowing the user to search a range of dates or range of timestamps. That should provide enough options to cover most situations.

[Razumovsky]

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

[xochi]

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/

[xochi]

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...

[xochi]

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.

[Tyrant]

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?

[BobWeaver]

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?

[Tyrant]

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.

[Tyrant]

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?

[BobWeaver]

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.

[greg@work]

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

[BobWeaver]

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.

[Genx]

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

[Stat Larry]

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

[BobWeaver]

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.

[Stat Larry]

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...

[Stat Larry]

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