Yet another Audit Trail

[BobWeaver]

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, 2006 by Guest

[aaa]

Very good and very useful!

[Ugo DI LUCA]

Neat, very neat... Bob

[BobWeaver]

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.

[SteveB]

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

Steve

[BobWeaver]

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

[Ugo DI LUCA]

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.

[BobWeaver]

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 );"")&

[BobWeaver]

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.

[Ugo DI LUCA]

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.

[stevie.at]

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

[BobWeaver]

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.

[stevie.at]

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

[stevie.at]

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.

[BobWeaver]

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.

[stevie.at]

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?

[BobWeaver]

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

[stevie.at]

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!

[BobWeaver]

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

[seanc]

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.

[BobWeaver]

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.

[seanc]

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.

[BobWeaver]

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.

[seanc]

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.

[BobWeaver]

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?

[seanc]

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.

[BobWeaver]

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>

[BobWeaver]

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

[Jalz]

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?

[BobWeaver]

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.

[JamesL]

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

My next post in this thread seems okay.

[JamesL]

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.

[BobWeaver]

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

[BobWeaver]

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

AuditTrail-r3_01.fp7.zip

[Christian Coppe]

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