Audit functions not working - boggler!

[keshalyi]

Wow! Here's a boggler...

I've been reading the Advanced Filemaker Pro 5.5 book, by Chris Moyer and Bob Bowers (a GREAT book,, I HIGHLY recommend it), and in it, they present an auditing mechanism, not dependent on plug-ins, originally developed by Bob Cusick.

So in theory, its great. And I know it CAN work, because the disk has a sample file on it, where it works. However, I tried building a file in parallel to their's, and it just won't work.

Here's how the mechanism works (to my understanding)

You create a calculation field (Audit_Existing Values) in the following format:

"First Name::" & First Name & "

[LiveOak]

I have to admit that I didn't take the time to dig through the whole implementation, but take a look at how the calculated fields are triggered. Specifically the setting of "do not evaluate if all fields are empty" for their calculations and yours.

You also might try a real degenerate case with the very minimum number of fields to prove the concept (use two fields only).

-bd

[keshalyi]

Tried that. Actually, in my test version, I'm only testing one field (I've tried it with two, as well.) Still no dice. Actually, I printed out the field defs, and compared them line for line, setting for setting, no result

[BobWeaver]

Some time back, I made a audit trail example file that works similarly. Maybe you'll have more luck with it.

You can find it here:

http://members.shaw.ca/indexel/download.htm

[keshalyi]

Thanks SO much, I'll take a look through it and let you know what I find out. BY the way, FYI, the link to the Mac version on the page lead to a FNF error

[keshalyi]

Yoru solution works great!

But...

Unfortunately, I did get that aspect of it to work. I logged current statuses just fine. What I'm trying to do, but can't, is to record, in parallel, the fields old value and new value in the log.

[BobWeaver]

Thanks for letting me know about the broken link.

Regarding inclusion of the old field value in the audit trail field: In the calculation field, a subtle but very important consideration is whether you reference the fields directly or via the relationship. That will affect whether you get the new or old values. Try changing the way you are referencing these values and see what happens.

[BobWeaver]

I was just playing around with this a bit more, and made a new version that records the old field contents. I won't be able to post it on my site for a few days, but I can email it to you if you like.

[BobWeaver]

Just another thought here. In the authors' original example, go through the field setup options carefully, and see what they have set for storage options on the calculation fields and the lookup options on the lookup fields. This will have a major affect on things that use status functions such as Status(CurrentFieldName) etc.

[Kundinger]

Hello JP,

I'm going to e-mail you a sample file I created that shows how to 'append' data to fields. This is only one idea on how to implement a 'record change log', or what you call an 'audit system'. There are 5 examples included about how to move data around.

I think one example in particular may be applicable to your situation. When you open the file... take a look at example 'A-3'.

I use a 'record change log' to 'archive' changes that my client users make to records. My system will take any 'change' and 'append' it to a 'log' field with the 'UserID', CurrentDate, CurrentTime, and the changed data. The field displays this with the 'newest' changes at the top. The field continues to grow as more changes are made. But we have a history of all field changes, date & time, and user.

One client wanted to 'delete' older entries to this field... I created a script that will 'delete' ALL entries or any entry over the 'count' of 10.

I'm sending you the file...

Good Luck!

Bob Kundinger

[keshalyi]

Cool looking function, Bob. Unfortunately, the fiel is write protected, and your 'view field' and view scripts buttons don't do anything - is there a password?

[keshalyi]

Heh... it was... I had it marked as read only in my file system. Sorry. My bad. Note to self - do not work with a migraine...

[Kundinger]

JP,

You say you got it to work okay? Do I understand you correctly... the file privileges were set to read-only?

Let me know if I should send it again.

Bob Kundinger

[keshalyi]

No, actually, embarrasingly enough, the file was just marked 'read only'. That is (on my macintosh), I had to just get info on the file and uncheck the 'locked box'. Duh.

But your idea worked great! The only problem I'm having now is getting it to toss the history after it reaches a certain size (That is, keep only 10 lines of history, for instance). I tried to do this just by changing the calculation to truncate anything after the second carriage return in the original field, but that made the whole thing self-referential, and therefore invalid. So I tried it with the 'Get Field command that you used before, and it just gave me nothing - long, empty blank fields. Huh. Still playing with it, I'm sure its just a programmer's glitch

Thanks again!