Updates:

2006-03-15: Latest version 4.0 is added (see bottom for AuditTrail-r4_0.fp7.zip)

2006-01-20: Version 3.02 is Here

2005-03-30: Added revision 2 of file in this post

I spent part of Saturday searching for an audit trail example that makes use of FM7's new features, thinking that an FM7 implementation could be considerably simpler than an FM5/6 version. While I found a couple of excellent examples posted by Ray Cologon and Ugo di Luca, they weren't quite what I was looking for, so I spent the rest of the day trying to come up with a different version. I've posted the result here.

It works without scripts, lookups or relationships. And while it only requires 3 auto-enter calculation fields and a global to hold the names of the fields to be monitored, it isn't as simple as I was hoping it would turn out.

Anyway, have fun, and please let me know about any bugs that you find.

twoversions.zip

AuditTrail-r4_0.fp7.zip

Edited March 16, 200619 yr by Guest

Very good and very useful!

Neat, very neat... Bob

Just posted a revised version (see first post) which corrects an error in one of the field formulas.

Also modified it so it now does a better job of tracking changes in related records, and detects and logs duplicated records.

Bob, I assume that your version would not be suitable to be modified for use in Version 6?

Steve

Unfortunately no. It makes heavy use of the Evaluate function.

Hi again,

I'd be curious to see how you can monitor repeating fields changes with this technique, Bob.

Have you tried it yet ?

Also, did you already tried to involve the FieldNames ( ) function here instead of your fieldList, and if so how did it go ?

I didn't seriously tried myself with some AuditTrail since FM7 was released, but as recursion went live with this version, I was thinking of referencing Layout and FieldNames, which seems rather "natura"l.

I just tried it with repeating fields. It works if you list all the repetitions that you want to monitor in the Field list global like this

Text1

Text2

Num1

Num2

RepField[1]

RepField[2]

RepField[3]

RepField[4]

I can see a way to make it so that you don't have list the individual repetitions to monitor. I'll implement that as soon as I have time, although I don't really know why you would want to audit repeating fields, since they are usually only used for utility functions.

As for using the FieldNames() function, it should work fine, but you should delete the audit trail fields from it like this:

Substitute(FieldNames(Get ( FileName );"")&

Okay Ugo, here you go, I added a new version which handles repeating fields and automatically generates a list of fields using the Fieldnames() function.

I replaced the attachment in the first post. It now has both versions. So, you can take your pick: simple or complex.

Thanks Bob,

Glad to see you're back with too much time on your hands

The Rep thing was just for curiosity (may be to make Soren a bit mor upset ) but the FieldNames ( ) is an option that I was considering.

I will take a close look to your files...

Thanks again.

I just had a look at your Audit Trail and have to say it's pretty nice! I''m trying to integrate something like it into our office-solution. So far I had another file which worked well too but caused some trouble at look up-fields...yours doesn't, but on my solution the only problem I got is that the entries for the User and the Field Name stick together. In your sample file there's a line of dots in between...As a newbie at FM it was a bit tricky to set it all up and I couldn't find the hint where the problem might be...Got one for me???

The audit trail data has a tab character embedded in it between the user account name and the field name. So, you need to set up the paragraph format of the field to include a tab position about 2.5 inches over to the right so that the field names will line up in a column. You can optionally set the fill character too (I have it set to a period). If you look at my example in layout mode, select the audit trail field and then Format>Text...>Paragraph>Tabs..., you will see the current settings.

I used the Format Painter when incorporating your fields, so the settings are the same...another hint?

I didn't rebuilt all your relationships, that means I don't use a table "Global". Could this cause troubles?

While playing with the solution I found that if you change the content of a field that is not checked as to be tracked, and check it afterwards, the Audit Trail automatically enters the last modification in that field, but instead of the previous data some number (like "553") appears as Old Value...Is there an easy way to modify this so that the tracking starts after a user selects this field to be tracked and after the first modification has been done (instead of taking the last one made before select the field to be tracked)?

Hope this makes sense...

Bob,

I made it work...my "gTab" field was empty, so no wonder there was no tab at all!

The only thing that's left is it reacts a little weird on certain fields if you check and uncheck them...and make entries while they're unchecked. But it still makes me happy! Keep on developing!

St.

In my example, I deliberately placed the field list editing on a separate layout/table to prevent interaction while editing the list, and had included a script that should be run after changing the field list (it runs when you click the done button after editing the field list). This updates one of the auto enter fields so that the data order agrees with the field list data order. If you don't run this, you may get an odd entry in the audit trail field either immediately or when you make your next field modification.

Got it! Should really have taken a closer look...I'm currently testing my solution on an iBook (G3, MacOSX 10.2.8) with your Audit Trail in a file containing some 6000 records. After including the script in the "Done" button the update takes a while...about 15 minutes. If that's the case it's better not to let all users edit the Audit list but the administrator...or is my machine just too powerless?

No. Nobody should be changing the list of fields to be audited once the database has been set up. If the developer adds a new field then it could be added to the list at that time, and the update script would be part of regular maintenance. If this is to happen frequently, then a more efficient update script could be created. The one I made was quick and dirty, and not particularly efficient. But in any event you have to recognize that adding an audit trail to a database is going to add significant overhead to it. If you have scripts that modify large numbers of records and they are run frequently, then you may be better off looking at a totally scripted solution with a scripted audit trail such as the one posted by Brian C here:

http://www.fmforums.com/threads/showflat.php?Cat=0&Number=148691&an=0&page=0#148691

I see. I've seen the post on my way through the forum but didn't download the solution so far. Thanx for the link!

I was playing around with this file to see if it would survive Instant Web Publishing. I found that it works on IWP with an exception. Fields which contain linebreaks cause the audit trail to fail. After checking out what was happening, I found that IWP (on OSX at least) has a bug which causes it to insert linefeed characters immediately after the linebreak character when the record is submitted, whether the field is modified or not. This may be a platform specific bug because Windows and OSX (aka Unix) have different linebreak characters.

I have attached another version to this post which strips out all linefeed characters from the critical auto-enter fields, atOldData and atChanges, to fix the problem. I would be interested to hear from Windows users to find out if the problem exists when running on that platform, and whether this new version does or does not work under Windows.

AuditTrail-r3.fp7.zip

Hi Bob,

I've had a good solid look at this, and as far as I can tell it does work properly with windows IWP. The line feed issue does appear to be there on Windows, I think that was part of the problem with my existing audit trail.

I have to confess that so far, this is too complicated for me to fully understand. I'm relatively new to FM7 so I tend to not use a lot of these newer functions fully yet, but I'll keep working on it. I took the latest version and de-constructed it to remove all the fields I didn't need (such as the repetition stuff. Had some serious issues when I changed field names, but I worked it out.

One of the fields I wanted to audit is my primary key - a serial number auto entry field. Of course it should never change, but I feel it's an important field and if it does ever change I want to know about it. The side effect of this though is that new records are always listed as "related record created" although they are not.

All in all, I love your work!

Much more effective than my previous version.

I just wish I fully understood it. I think I have a pretty good grasp of Filemaker, but then I see work like this and I realise I have a LOT to learn.

Thanks,

Sean.

Hi Sean,

Thanks for verifying the IWP bug.

I'm not sure what you are doing differently with the primary key field that would cause it to indicate "related record created," but you could simply change that string in the formula to read "record created." It's probably not important how the record was created anyway.

Upon reflection, I'm not sure that the DONE button is ideal.

Using this effrectively wipes out the modified account and modified timestamp fields, which I already use for other things and rely on (portal sorting for example).

I have noticed that not pressing the button doesn't stop things from working, and a change to any field in a record will trigger the audit fields to update for that record. The only unfortunate thing is that it throws some gibberish numbers into the audit record for the newly added fields.

This is better behavior for me than losing the modification auto entered data, although it would be nice to be able to show something other that seemingly random numbers.

Cheers,

Sean.

But as I mentioned before to Stevie, changing which fields are to be audited is not something that should be done very often--if ever.

Once the system is completed, I would agree.

But because this was a rebuild of a FM6 solution to FM7, I find I'm adding to it on a daily basis. Also now that its on the web and working well, people want more and more functionality. Which is fine by me

More importantly though, I am integrating your solution into my existing files, which means I have tens of thousands of records with valid and useful modification fields.

Changing those fields even once is very bad. Much lost data, which I always try to avoid.

I haven't found skipping the "done" button to have any serious side effects, and since I have maybe 20 odd tables in my database it also cuts down on a lot of scripting. I'll live with the odd random number. Maybe one day I'll be able to write them out.

Regarding the primary key field, I don't think this is specific to anything I have done. If I set your original files to audit the primary key with no other changes, I get the same behaviour. And you are right, modifying to "record created" would be fine.

Thanks again!

Sean.

I haven't been able to duplicate the problem you mention with the key field. I tried changing the key field in both the parent and child records, and they seem to behave as expected. What exactly did you do to get this to occur?

Hi Bob,

Ah, I see I was misleading with what I wrote, sorry.

I meant I did it to my primary key, and duplicated it by making one of your fields behave the same way, though it wasn't actually your primary key.

This will do it:

open your file AuditTrail-r3.fp7

Click on Try It

Define database and turn set Num1 to autoenter a serial number (leave it as 1 is fine)

exit Define fields

Create a new record - using your button

Audit field says Related record created, and enters the Num1 entry.

I actually like this behavior once its changed to record created.

Would it have made the formula's any simpler to have not worried about the related bit? (they are still a bit beyond me, and I haven't had time to delve into it).

Cheers,

Sean.

Yes, you can change the case function at the end of the atChanges formula from:

Case(

IsEmpty(atChanges) and IsEmpty(List);"<record created>";

IsEmpty(List);"";

IsEmpty(atChanges);"<related record created>

BTW, thanks for catching that primary key problem. I had not tried auditing autoenter fields. This is something that will need to be addressed.

Hi Guys,

Im a new convert to FMP 7, so please be gentle. Im rewriting my exisiting application to 7 format, and I thought it would be a good idea to audit a number of fields.

I've been looking at your example file, and it looks like what I need, do I need to create all the fields from tsCreated to gHex0A to get this working in my solution?

You might just want to implement what's in the revision 1 file. It's the simplest, and probably all that most people need. You could add more things later as required. The minimum fields required are tsModified, tsCreated, and all the ones with the 'at' prefix.

Warning, I've recently been testing this out on a file with lots of records, and found that the audit trail calculations will severely slow things down if you are using the Replace Field Contents command or are running scripts which modify many records at once. So, you should take this into consideration before implementing this.

I'm looking at modifying the audit trail field formulae so that the auditing may be turned off and on as required to allow scripts and Replace to run as fast as possible, but don't know when I'll have time to get this done.

Sorry, my post was misplaced, so after all I edited/deleted it.

My next post in this thread seems okay.

Bob's Audit Trail is excellent, I tried out his 2nd Revision. At this point I'm not able to understand it, but applying the solution was easy and interesting. Moreover I'm happy that the autoenter fields work as desired, and so does the Audit Trail in IWP.

I discovered two minor problems (but these may be my own fault)

1) In every created Newly Record repeating fields with over 9 defined reps show up in the Audit Trail like this (e.g. a field with 15 reps):

Keywords [1011]: [empty] >> "?"

Keywords [16]: [empty] >> "?"

2) If the audited table is not the first created table then the "atAllFields" calculation does not work as it should in IWP:

the "atAllFields" calculation shows all fields in the 1st created table only. In my table I made the following tiny change:

FieldNames(Get ( FileName );"")

to

FieldNames(Get ( FileName );Get ( LayoutName ))

If I've overseen something I will be glad to know.

I'm glad you're finding it useful.

I haven't tested it exhaustively, so you may find lots of bugs.

As for the repeating fields, I added that part rather quickly. It's probably something related to the number of digits in the repetition affecting the parsing. You probably shouldn't be using repeating fields in your solution anyway.

But, I'll have a look at it when I have time.

<Edited>

Found the problem. There's typo in the formula for the field atFieldList_All_Reps. Change this line:

Rlist="1

Attached revision 3.01. No new features; just fixed some bugs.

AuditTrail-r3_01.fp7.zip

Hello Bob,

Congratulation for your audit trail for FMP 7! Very usefull and professional! As I am strongly interested by this function, I made some tests on your last version. It is obvious that the primary goal of an audit trail is to record any change in "critical' fields, and it is logical to take into consideration the "manual" changes by a user. However, there are other ways to change the data of a field. I made some tests and I noticed the following behaviours

1) I imported a set of data by creating a new record : no trace in the audit trail.

2) I imported one more time, but by updating the previous data : OK, the changes are recorded in the audit trail! (Never happen with older versions)

3) I created a link in another file (on the field "Text1") and, from this second file, I created a small script updating the field "Num1" in the related record. Strange behaviour : the first time, nothing happened. The second time, a change appeared in the audit trail, but whith the PREVIOUS data instead of the new one. A third test gave the same behaviour : the two old values were present.

4) In your file, I created an additional field. It is called "test", is a text field with an automatic calculation, replacement allowed, defined as 'Num1 &"P"& test'. I created a new record and restart the exercice. Same behaviour in your audit trail : the first time nothing happened, but the value of Num1 was recorded in my test field. The second time, the previous value appeared in your

audit trail, and the two values were listed in my field, etc.

It is only an idea to improve your tool, and my "test" field works only on one field, of course. But in the multi files or multi tables developments, I think that a record updated from another file or table is quite common. What do you think about?

Christian

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

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

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, 200620 yr by Guest

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?

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.

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, 200620 yr by Guest

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.

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

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

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.

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?

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, 200619 yr by Guest

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.

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.

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.