Starter Template with Audit Trail Log

[BruceJ]

I just got my latest copy of FileMaker Advisor and had to give this a try.

This file has an audit trail that tracks any and all changes to specified fields. The article was great, and I'm liking this so much I wanted to share it will everyone.

My attached stater template has all the standardized fields and naming conventions of CoreSolutions along with some of the stadard graphics I use, screen sizes, etc.

The record serial number field also uses the latest random stuff that was mentioned here in the Forum as well.

This has always been a good place for me to start, hope someone esle can use it as well.

Template.zip

[mgeorge]

One issue that i've run into, is that if a validation calculation is attached to the userdata fields being audited, is that the trigger gets set twice resulting in a double entry in the audit history. Just add a validation to userdata1 and userdata2 and see what happens.

Mike

[BruceJ]

Interesteing issue.

I experimented after you mentioned this and I found that the validation does not allways cause another entry, only when you enter an invalid entry and then hit "Revert Field", it shows the contents you had tried to enter and then the contents that it changed them back to the original contents.

I can live with this, it shows what was attempted to enter and then what it was corrected to.

Because this version of Audit Trail works off of a self relationship, and not a script, there's no way I know of around this one - and I don't know if I'd consider it an error either

[BruceJ]

I found a problem/bug.. perhaps..

In the calc that triggers the audit, I can't add more than eleven fields, otherwise I get an error saying the field can't be found.

I'm going to email FielMaker ADvisor and see what they have to say.

[BruceJ]

I found a solution to this limitation of eleven fields...

Instead of using "Case" statement like the article, use "IF" and "IsEmpty" in the audit trail trigger.

The new trigger that is unlimited is:

zl.auditTrigger.c = If (IsEmpty(userData1) or IsEmpty(UserData2) or ISEmpty(userData3) ..., ContactID, ContactID)

This seems to have fixed the problem. BTW - if you set a field to validate, it still shows up if you choose "revert field" as was mentioned above. May or may not be a problem depending on viewpoint.

[Bush]

I've yet to download your sample, but I just created a log using the MODLOG file (John Mark Osborne's site) as the basis for my work.

I've run into the FIELD NOT FOUND problem, though, in the CASE (name <> name OLD, name OLD & " -> " name, address <> address OLD, address OLD & " -> " address, etc) calculation for actually writing the field-changes to the log. I got, however, far further along than a mere 11 fields before I hit this problem (probably more like 100 fields in).

Does this sample of yours get around this by creating a new record for each change, so it's not writing to a log field associated with each record? How do I get around the limitation in my CASE?

[macguys]

I can't seem to find a file called "modlog" on John Mark's site...am I missing something?

[Paradigm]

Okay, I may have just discovered another possible glitch or bug when the fields being audited have a validation attached.

I have a Time Keeping or Time Card solution I created and I have two fields tradePersonsName and weekBegins ( which is the week that the Timecard is for that I didn

[Bush]

How interesting. I cannot find MODLOG on the site, either. It's a 3-cupper named MODLOG that's called Modification Log in the window.

I have a copy of it, still, if you'd like I can zip it and email it.

[Lee Smith]

Sorry for entering this late, but if you are still wanting these files: Go to www.databasepros.com, click on Resources and then do a search for "modification". This returned two pages files, and in that there were at least to files that would do this listed.

HTH

Lee

[Bush]

Not so, the file's not there.

I emailed JMO about this and his response was:

"I have only published this file on my book Scriptology."

Which is interesting 'cause I don't own this book (although it's supposed to be a spectacular resource) and I _know_ I got MODLOG off his site. So. JMO did offer to email it to me, but I already have it; in fact, I'd written him asking if he minded if I forwarded the file to any who asked for it. That he did not address, so I'm gonna assume it's a NO.

So. I guess I hafta refer you to SCRIPTOLOGY.

[macguys]

I found it. It's at http://www.scriptology.com/x345ijr.html

[Lee Smith]

Sorry for coming back late on this. I thought I had this site set up to send me the posts, so I forgot about it. A couple of days ago, I started getting a digest and I realized that I hadn't been here for quite awhile. Catching up on what has transpired on this one, I notice that several of you have still not been able to get the file from JMO's site. I really don't understand this, I just did a search on the HTML (not flash) site for modification and it returned nine (9) items with three (3) of the files indicating tracking modifications.

Lee