RF_Audit ( LogField ; Watchlist )

Prepends an audit log of changes to fields in Watchlist to LogField

Average rating: 4.2 (37 votes) Log in to vote

Peter Vinogradov - Show more from this author

Sample input:

RF_Audit ( Data_Audit; "tf_Reps[2] & nf1")

Sample output:

[4/28/2007 12:56:19 AM] User Admin ------ <tf_Reps[2] > to |World| from |Hello|
[4/28/2007 12:56:19 AM] User Admin ------ <nf1 > to |14| from |12|
{formatting not shown}

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

/*
RF_Audit ( LogField ; Watchlist )
By Peter Vinogradov
inspired by SuperLog CFs and technique by Ray Cologon, Nightwing Enterprises

Sample Audit Line:
[12.25.06 13:30:31] User {UserName} changed field <{FieldName}> to |this is the value╫this is another line| from |this was the value|

note: "╫" = alt+4567 in windows
*/

If (
 Substitute ( Watchlist ; " " ; "") = "" ; LogField ; //End Recursion after last value
Let (
[
//-------------- Color settings
DT_r = 50 ; DT_G = 200 ; DT_b = 50;  			//date colors
U_r = 50 ; U_G = 50 ; U_b = 170; 			//User colors
newVal_r = 100; newVal_g = 0; newVal_b = 0; 		//new value colors
oldVal_r = 100; oldVal_g = 100; oldVal_b = 100;		//old value colors

ESC_CR = "╫";  // the character used to escape CRs
ESC_PIPE = "@@ESCPIPE@@"; //the character used to escape pipes
TABCHR = "	";

DT = TextColor ( Get (CurrentTimeStamp) ; RGB (DT_r ; DT_g; DT_b ));
usr = TextColor ( Get ( UserName) ; RGB (U_r ; U_g; U_b ) );

WatchlistCleanSpaces = Substitute ( Watchlist ; [" &" ; "&"];["& ";"&"] );
WatchlistVL = Substitute ( WatchlistCleanspaces ; "&" ; "¶" );

CurField = Substitute ( LeftValues ( WatchlistVL ;1) ; "¶";"");
Remainder = RightValues (Watchlistvl ; ValueCount (Watchlistvl)-1);

//-------- Isolate references to the field being evaluated and determine position and range of previous value
curFieldReference = "------ <" & TextStyleAdd ( curfield; Bold+Uppercase) & ">" & TABCHR &"to |"; 
CurFieldWasStrt = Position ( LogField ; curFieldReference ;1 ;1 );
CurFieldWasEnd = Position ( LogField ; "|" ; curfieldWasStrt + Length (curFieldReference) ;1 );
CurFieldWasLen = CurFieldWasEnd - ( CurFieldWasStrt + Length(curFieldReference));

//---------Capture previous value of current field
CurFieldWas =
If ( CurFieldWasStrt <>0 ; 
 TextColor (
  TextStyleAdd ( If (CurFieldWasLen>0 ;  Middle ( LogField ; CurFieldWasStrt + Length ( curFieldReference ) ; CurFieldWasLen ); "[...]" ) ;  Italic+Strikethrough) ;
 RGB ( oldVal_r ; oldVal_g ; oldVal_b )
 ) ;
 "") ; 


passonWatchlist = Substitute (Remainder ; "¶" ; "&"); // Passed to next iteration

curfieldVal = Substitute ( GetAsText (  Evaluate (CurField) ) ; ["¶"; ESC_CR];["|";ESC_PIPE]) ;
CurfieldValue = TextColor ( TextStyleAdd ( If (curfieldVal = "" ; "[...]" ; curfieldVal) ; Bold )  ; RGB(newval_r ; newval_g; newval_b)  ) ] ;


If (curFieldValue <> CurFieldWas and curfieldVal<>"?"  ; 
"["& DT & "]" & TABCHR & " User "& usr & TABCHR & curFieldReference  &curfieldValue & "| "& TABCHR &"from |" & CurFieldWas  & "|"   & "¶" ; "")  //audit line or nothing

& RF_Audit ( logfield ; passonWatchlist ) // rest of recursion

)

)

Inspired by Ray Cologon's SuperLog technique, this recursive CF is used to capture changes to specified fields. Typically, the "LogField" is a text field stored in the table you want to audit. It is defined as an auto-entry calc that triggers this CF whenever modifications are made to fields in the watchlist.

This autoentry calc requires a bit more fuss than the SuperLog method, since you have to explicitly define field triggers AND a string of Watch fields to pass to the function. In the example above, Data_Audit would be defined as follows:
-------------
Let (
[
Triggers = tf_Reps[2] & nf1;
WatchList = "tf_Reps[2] & nf1";
];
RF_Audit ( Data_Audit; WatchList)
)
-------------
The tradeoff is that the function is not context-dependent, and therefore no separate logic is needed for modifications made by script.
This function also includes formatting logic for the log.
Function has not been seriously field tested or put up against a large watchlist so it may turn out to be slow under load. Feedback appreciated (pvino at optonline.net)

Comments

		Joan SubirÃ³s, fmSuit Nov 11, 2019
Hello, How can I "audit" if the user edit or modify a relational field in a portal ??? Thanks.

		Andrew Mallison Mar 18, 2024
Can I suggest you drop the formatting, 'User ' & '------' etc to keep it simpler and smaller

		Andrew Mallison Mar 18, 2024
Also remove the ';' off the WatchList line in your example.

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

RF_Audit ( LogField ; Watchlist )

Comments

Support this website.

Support this website.