Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Calc Audit Trail - can't erase?


Recommended Posts

Hey everyone,


I created a calculated audit trail for our join tables based on some solutions I found here, but the problem is, because it is triggered by field change, I can't get it to clear the field when the log is "harvested". Normally I would have a "Set Field" script step that sets the value of the field to "" and that would clear it. But when I do that, the field puts another entry in with blank values where there should be changes in the log. The calculation in the audit_trail calc field is:


Let (

trigger = PieceID & StateID & ActiveStatus & Quantity


If ( PieceID ≠ "" and StateID ≠ "" and ActiveStatus ≠ "" ;

Get ( CurrentHostTimeStamp ) & " " &

TextStyleAdd ( "Account" & ": (" & TextColor(Get ( AccountName ); RGB(200;0;0)) & ")"; Bold ) &

TextSize ( TextStyleAdd ( " changed " ; Italic ) ; 10 ) &

TextColor ( Get (ActiveFieldName) ; RGB(200;0;0) ) &

TextSize ( TextStyleAdd ( " from " ; Italic ) ; 10 ) & TextSize ( TextStyleAdd ( "an unstored value" ; Italic ) ; 10 ) &

TextSize ( TextStyleAdd ( " to: " ; Italic ) ; 10 ) & TextColor(Get (ActiveFieldContents);RGB(0;0;200)) & TextSize ( TextStyleAdd ( " for RecordID: " ; Italic ) ; 10 ) & Get ( RecordID ) & TextSize ( TextStyleAdd ( " KeyFieldValue1: " ; Italic ) ; 10 ) & TextColor(PieceID;RGB(0;0;200)) & TextSize ( TextStyleAdd ( " KeyFieldValue2: " ; Italic ) ; 10 ) & TextColor(StateID;RGB(0;0;200)) & TextSize ( TextStyleAdd ( " on layout: " ; Italic ) ; 10 ) & TextColor(TextStyleAdd ( Get ( LayoutName ) ; Italic );RGB(0;200;0)) & TextSize ( TextStyleAdd ( " in join table: " ; Italic ) ; 10 ) & TextColor(TextStyleAdd ( Get ( ActiveFieldTableName ) ; Italic );RGB(0;200;0)) & ¶ & Audit_Trail




This is the result after I've copied the data out (data copies fine, btw) and then try to use "Set Field" to clear it (I've placed XXXX where there should be a value):


8/8/2006 2:57:19 PM Account: (Justin) changed XXXX from an unstored value to: XXXX for RecordID: 1015 KeyFieldValue1: 1171388 KeyFieldValue2: 2000002 on layout: H_FACtoCOMP_J in join table: XXXX



Ok, so the bottom line is, there are two major problems:

1. Because it isn't working the normal way, it has blank values so it screws up our text parsing in our log table

2. We can never really completely clean our audit_trail fields out.


If there is some way to bypass the calc and just clear the field, that would be really ideal for us. Anyone know of another way other than "Set Field"?


Thanks in advance for your help!



Filemaker Goblin

Link to comment
Share on other sites

I would rather use scripting to create logs as it is much more reliable. Another alternative is to use a technique I once downloaded from databasepros.com. You can find it at:




and type "log" as the search criteria. I used the "Change log" (Last one on the search results) that tracks changes on all desired fields on a single auto enter calculation. They have quite a few other options that can be helpful depending on your specific needs.


I hope this is helpful.

Link to comment
Share on other sites

Hi Justin,


You need to control the wipe from within the calculation. Create a global called gWipe doesn't have to be within the same table or, to save adding a field, a global variable called $$wipe should work just as well. When your calc *sees a 1 in this global (or the variable), it will blank the fields. Remove the 1 and your calc will begin to function again. Back up first, this is untested ...


Incorporation in your calc will be a simple wrap of your entire calc, such as:


If ( not gWipe ; your entire calc here )


Since no default result is specified in the If(), it will blank it. Now ... you will need to trigger each record to update after you set the 1. Why? Because your log must see a data change before your calculation will fire (and blank them all at once). I don't know your archive process to suggest where the scripting should be inserted but envision it something like:


1) Perform your archive process

2) Set Variable [ $$wipe ; 1 ] or Set Field [ gWipe ; 1 ]

3) Use Loop or Replace Field Contents to set a trigger field to itself.

4) Set Variable [ $$wipe ; "" ] or Set Field [ gWipe ; "" ]


I'm making an assumption that you know how to loop through your records or use Replace Field Contents. If not, we can walk you through whichever you choose here. I have not tried the variable trick but it makes sense that it would work for this purpose.


LaRetta smiley-laughing

Link to comment
Share on other sites



I think you solved it for me! Thats an excellent solution! Thank you very much! :) I'll try that right away! :)



Link to comment
Share on other sites

LaRetta -


Just a follow up:

It worked perfectly and it was very quick to implement! Thanks again for the assistance! :)



Link to comment
Share on other sites

Good. Don't ever use $$wipe or gWipe for anything else. If you do, you'll blank your archive on any records which are modified between archive runs. Build some protections into it - a few Custom Dialogs with "Are you SURE" and so forth. smiley-laughing


Oh. And if you are implementing this in stand-alone and you're using global, remember to close your file with that field BLANK. Otherwise, it'll keep coming on. It may be wise to blank it in your open or close script. But if you use $$wipe, you'll be fine. smiley-tongue-out

Link to comment
Share on other sites


  • Create New...

Important Information

Terms of Use