Auditing Fields Modified By Script

[frinholp]

Hi All

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

Filemaker Magazine Super Audit Logging Tutorial

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 creator 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.

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.

Can anyone give me any pointers?

Thanks in advance

Lee

[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

[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

Lee