Solved

audit trail

Posted on 2014-09-15
6
232 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
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…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

636 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