4 Replies Latest reply on Jul 22, 2010 3:53 AM by Frinholp

    Auditing changes made by a script

    Frinholp

      Title

      Auditing changes made by a script

      Post

      Hi All

      I have found a fantastic video tutorial on logging changes made to records freely available at the following url:

       http://www.filemakermagazine.com/secured/popup/658/SuperAuditLogging_full.mov?width=800&height=600&access=189aec2174af5e8833d590515176d8ed

      Moderators: Please edit this post if this infringes copyright. I am assuming not on the basis Matt Petrowski mentions copyright issues in the video.

      All credit goes to Matt Petrowski and the original function was created by Ray Cologon,NightWing Enterprises, Melbourne, Australia
      www.nightwing.com.au/FileMaker

      Function usage: AuditLog (Logfield; Fields; Format; Seperator)

      The code to the function is as follows:

      Let(
      [
      // 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;
        LogField
        )

      )

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

      // Revised by Matt Petrowsky

      I have been playing around with this for hours now trying to get the function to audit changes made by a script. Obviously my coding skills aren't quite up to it.

      Can anyone give me any pointers?

      Thanks in advance

      Lee

        • 1. Re: Auditing changes made by a script
          RickWhitelaw

          I believe this will audit and lag all changes to records regardless of how they were made.

          RW

          • 2. Re: Auditing changes made by a script
            Frinholp

            No I have tried and tried and tried again.

             _Script = Get(ScriptName);

            Holds the name of the script, however in this original copy of this custom function _Script only seems to be used to test whether a script exists (Please correct me if my understanding of this is incorrect)

            I have created a test script attached to a button that simply uses Set Field to change the contents of a field which has been passed into the function as a parameter, but the change is not logged. I have tried both with and without using Commit record in the test script. 

            If I then manually enter data into the field, the function logs the value that has been entered by the script as the old value and logs the change to the new value. Hence, an inconsistent audit.

            Lee

            • 3. Re: Auditing changes made by a script
              Frinholp

              Sorry all,

              I should of listened to the tutorial more carefully. Oops!

              I have tried creating an analogue of AuditLog which I have named ScriptLog.

              ScriptLog(LogField; FieldChanged; Format; Separator)

              Let(
              [
              // Setup

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

              // 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;
              _%Script = _Script;
              _%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;
                LogField
                )

              )

              I am using a test script:

              SetField[fieldbeingchanged; value)]

              SetField[logfield; ScriptLog(logfield; fieldbeingchanged; "%Date%Time%Account%Field%Change" ; " " )

              but the logfield is not being updated.

              Can anyone see any error in my code?

              Thanks in advance

              Lee

              • 4. Re: Auditing changes made by a script
                Frinholp

                Ok, problem solved

                Took me a bit of time to sort this one as I'm not an experienced developer.

                I'll give you my solution incase anyone else would like to re-use the code. Again all credit to Matt Petrowski and Ray Cologon.

                ScriptLog(LogField; FieldChanged; Format; Separator) // Do not use full table and field reference for FieldChanged, just field name if using in conjunction with AuditLog

                Let(
                [
                // Set field name to be searched for

                _FieldName = FieldChanged;

                // 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 ) )

                ];

                  _LogString & ¶  & LogField
                 
                )

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

                // Revised by Matt Petrowsky & Me