65 replies to this topic

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

Attached Files

•  1111980144-twoversions.zip   36.75K   929 downloads
•  1142488509-AuditTrail-r4_0.fp7.zip   28.83K   878 downloads

[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. /threads/images/graemlins/mad.gif

[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 );"")&¶;
["atFieldList¶";""];["atOldData¶";""];["atChanges¶";""];
["atTrail¶";""];["tsCreated¶";""];["tsModified¶";""])

[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 /threads/images/graemlins/wink.gif

The Rep thing was just for curiosity (may be to make Soren a bit mor upset  /threads/images/graemlins/smile.gif ) 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. /threads/images/graemlins/wink.gif

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

Attached Files

•  1114996645-AuditTrail-r3.fp7.zip   22.38K   268 downloads

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