4 Replies Latest reply on Nov 9, 2012 12:09 AM by Gingernut

    Audit Log Help


      I have been exploring Ray Cologan's Superlog as edited by Matt Petrowsky and finding it great except that I need to be able to identify the Key field of the record being changed and this does not seem to be part of the solution in the custom function.

      The reason I need this is that I am tracking if a Model of a machine is changed and there could be many instances of the same model. How would I know which record had been changed if all I have is date time account etc etc. A search would find multiple instances.

      I am using Filemaker 12 on Windows 7.


      I am guessing I have to do something with the FORMAT part of the cf but do not know what. As the key field may be differently named in each table would I have to use a script trigger to set a variable called kP each time a layout was entered?

      I am going round in circles at the moment so some help would be much appreciated.



      // Setup

      _Trigger = Fields;
      _LogLength = Length(LogField);
      _Script = Get(ScriptName);
      _Repetition = Get(ActiveRepetitionNumber);
      _RepeatFlag = If(_Repetition > 1; "[" & _Repetition & "]");
      _FieldName = Get(ActiveFieldName) & _RepeatFlag;

      // Locate Any Previous Field Value in Log

      _FieldInLog = Position(LogField; Separator & _FieldName & Separator; 1; 1);
      _Divider = Separator & "-»" & Separator;
      _Start = Position(LogField; _Divider; _FieldInLog; 1) + Length(_Divider);
      _End = Position(LogField & ¶; ¶; _FieldInLog; 1);
      _PreviousValue = If(_FieldInLog; Middle(LogField; _Start; _End - _Start); "[---]");

      // New Field Value (fixes returns)

      _FieldValue = GetField(_FieldName);
      _FixedValue = Substitute(_FieldValue; ¶; "‡");
      _NewValue = If(Length(_FieldValue); _FixedValue; "[null]");

      // Format Values

      _%Date = GetAsDate(Get(CurrentHostTimeStamp));
      _%Time = GetAsTime(Get(CurrentHostTimeStamp));
      _%Account = Get(AccountName);
      _%Field = _FieldName;
      _%Change = _PreviousValue & Separator & "-»" & Separator & _NewValue;
      _%Table = Get (LayoutTableName);
      _%Layout = Get(LayoutName);
      _%Key = GetAsNumber (Get(CurrentHostTimeStamp)) & _PreviousValue & _NewValue;

      _LogFormat = Substitute( Format;
      ["%Date"; _%Date & Separator];
      ["%Time"; _%Time & Separator];
      ["%Account"; _%Account & Separator];
      ["%Field"; _%Field & Separator];
      ["%Change"; _%Change & Separator];
      ["%Table"; _%Table & Separator];
      ["%Layout"; _%Layout & Separator];
      ["%Key"; _%Key & Separator]
      _LogString = Left( _LogFormat; Length( _LogFormat ) - Length( Separator ) )


      If( Length(_FieldName) and Length(_Script) = 0;
      _LogString & Left(¶; _LogLength) & LogField;



      // Original Function by Ray Cologon
      // NightWing Enterprises, Melbourne, Australia
      // www.nightwing.com.au/FileMaker

      // Revised by Matt Petrowsky

        • 1. Re: Audit Log Help

          Here is an Audit Log formula that I posted in another thread.  Maybe it will help you in some way.


          This type of a formula via auto-entry to the Field "Audit_Log" can get you the data at the record level within its FileMaker table.  But to get it into another table or a file you will need to use a script.


          Evaluate (

          Quote (

          Get ( AccountName ) & ;  " | " & ;

          Get ( ActiveFieldName ) & ; " | " & ;

          Get ( ActiveFieldContents ) & ; " | " & ;

          Get ( CurrentTimeStamp ) & ; "¶"

          & ; Audit_Log ) ; [ First_Name ; Middle_Name ; Last_Name ; Date_of_Birth ; Date ; Salutation ] )


          Also make sure "Do not replace existing value of field" is "Unchecked"

          • 2. Re: Audit Log Help

            Sorry can't help you with Ray's stuff. I just created my own audit log using script triggers attached to any field I need logged and write all the data to a log table and it works great. I capture the before and after values in a field and if they are different I captuer what was changed into a log table with auto entered the date and time of the change, by whom before and after value etc. Your example seems awfully complicated, good luck with it.

            • 3. Re: Audit Log Help

              Well, you could simply use MBS Plugin to do the audit.

              The plugin helps you with logging and has quite a few options:






              • 4. Re: Audit Log Help

                Thanks to everyone, I found Rays UltraAudit log and managed to get this to do EXACTLY what I need so I am one happy bunny

                Very grateful as always to people sharing their genius and making me appear to my employer as a semi-genius