Solved

audit trail

Posted on 2014-09-15
6
198 Views
Last Modified: 2014-10-27
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

Let([
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)
)
0
Comment
Question by:rvfowler2
  • 3
  • 2
6 Comments
 
LVL 25

Expert Comment

by:Will Loving
ID: 40323464
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?
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 40323565
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.
0
 
LVL 25

Assisted Solution

by:Will Loving
Will Loving earned 250 total points
ID: 40323650
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:

http://www.experts-exchange.com/Software/Apple_Software/Filemaker_Pro/Q_27531161.html

Maybe this or something similar can work for you.
LineFilter.fmp12
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 2

Author Comment

by:rvfowler2
ID: 40333256
Sorry, been busy, will look at this Monday.
0
 
LVL 6

Accepted Solution

by:
slinkygn earned 250 total points
ID: 40401178
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.
0
 
LVL 2

Author Closing Comment

by:rvfowler2
ID: 40406572
Either one of these works for me.  Thank you.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question