Go Premium for a chance to win a PS4. Enter to Win

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

audit trail

I was given the audit trail solution below a while back; however, it is all in one field.  When we try to do a find on that field then it pulls up all other information.  Is there a way this solution can be done in a sub or normalized table so that each entry is a separate rec and each field can be put into its own field?  It seems that would make it much more searchable.  Thanks.

LeftValues (
SuperLog_cf ( Data_log ; NewTntDone & LeaseType )
; 1000 )
     //  SYNTAX:  SuperLog ( LogField; FieldsToLog )
    //  © 2006, NightWing Enterprises, Melbourne, Australia
   //  www.nightwing.com.au/FileMaker
  //  Audit Log Function by Ray Cologon - v1.8

Trg = FieldsToLog;
Lval = Length(LogField);
Scpt = Get(ScriptName);
Rpt = Get(ActiveRepetitionNumber);
Rflg = If(Rpt > 1; "[" & Rpt & "]") ;
Fnm = Get(ActiveFieldName) & Rflg;
Pref = Position(LogField; "      " & Fnm & "      "; 1; 1);
Pst = Position(LogField; "      -»      "; Pref; 1) + 4;
Pnd = Position(LogField & ¶; ¶; Pref; 1);
Pval = If(Pref; Middle(LogField; Pst; Pnd - Pst); "[---]");
Tval = Get(CurrentHostTimeStamp);
Fval = GetField(Fnm);
Sval = Substitute(Fval; ¶; "‡");
Nval = If(Length(Fval); Sval; "[null]")];
If(Length(Fnm) and Length(Scpt) = 0;
GetAsDate(Tval) & "      " &
GetAsTime(Tval) & "      " &
Get(AccountName) & "      " &
Fnm & "      " & Pval & "      -»      " & Nval & Left(¶; Lval) & LogField; LogField)
  • 3
  • 2
2 Solutions
Will LovingPresidentCommented:
Well, If you are using a Script Trigger to fire the above calculation then you could use the same script to create a record in another table. But I'm not sure off the top of my head how you would do that because what the above calculation is doing is detecting the change at the field level. Without using the mechanism, I don't know of an easy way to compare changes from before and after without keeping a duplicate record.

You might be able to do something with setting the record to not save field level changes immediately (Layout Setup) and then forcing them into an Edit mode which could only be excited by a Cancel, which would then either not save changes or a Commit would save them and create change records based on a comparison of that particular records values that were captures when they went into Edit mode.

In the databases that I've added a function like SuperLog to, I limit the number of fields being tracked to only those necessary and then viewing the contents of the Audit Trail field is a relatively short list that begins with the timestamp on each line. Are you perhaps tracking more fields that necessary?
rvfowler2Author Commented:
Regarding the fields, sometimes my boss wants most of them audited.  Second, sometimes you can get as many as 15-20 lines in one audit trail field and he assumed he can search and only pull up one line.
Will LovingPresidentCommented:
Bosses and their assumptions, eh? Well, perhaps some intelligent filtering of the Audit Trail Log field. I myself asked this question on EE a while back and the answer can be found here, along with a demo file:


Maybe this or something similar can work for you.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

rvfowler2Author Commented:
Sorry, been busy, will look at this Monday.
The "brute force" direct solution would probably be:

In the tail end of that script, that concatenates all the values:
If(Length(Fnm) and Length(Scpt) = 0;
GetAsDate(Tval) & "      " &
GetAsTime(Tval) & "      " &
Get(AccountName) & "      " &
Fnm & "      " & Pval & "      -»      " & Nval & Left(¶; Lval) & LogField; LogField)

1. Make sure all the "    " are actually either tab values or commas
2. Put double-quotes around each of the field values (so, for example, GetAsDate(Tval) becomes "\"" & GetAsDate(Tval) & "\""
3. Create a script that saves that field data to a file, and then imports the file to an your new audit table as CSV or TSV data, and schedule it.
rvfowler2Author Commented:
Either one of these works for me.  Thank you.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now