10 Replies Latest reply on Sep 24, 2012 9:20 AM by nsabel

    Logging Script Trigger

    user12837

      I run FM 11.

       

      I use onObjectSave to write an entry to a log table of the form

       

      LogID

      TableName

      FieldName

      OldValue

      NewValue

      User

      Date

       

      but I don't know how to capture OldValue.

       

      The approach which ought to work is to use a separate onObjectEnter trigger to write the current value of the selected field to a global field. But onObjectEnter does not fire when one selects a field within a portal (it fires only when a different portal row is selected).

       

      I could use onRecordLoad to copy the entire record to mirror table consisting of globals, and get OldValue from this mirror table whenever the user changes the corresponding field in the real table. But I'm dealing with a table with hundreds of columns. Seems like too much work.

       

      The number of columns also makes UltraLog infeasible. The list of fields in the calculation UltraLog( LogData; ModStamp; "TheText¶TheText[2]¶TheNumber¶TheDate¶TheTime") would be the length of a short story -- much too tricky to maintain.

       

      What I need is either a beforeModify trigger, or the DELETED and INSERTED tables provided by Transact-SQL (but I don't want to use ESS in this case).

       

      What's the efficient FM way to do this?

       

      Thanks,

       

      Tom

        • 1. Re: Logging Script Trigger
          ariley

          You could try OnObjectModify and OnObjectSave. One logs the old value, the other one takes the new. 

           

          The best thing, however is to use WorldSync fmDataGuard (http://www.syncdek.com/fmDataGuard/) or SyncDeck for audit logging. 

           

          agnes b. riley . filemaker and web development

          FileMaker Business Alliance . FileMaker Technical Network

          www.zerobluetech.com (http://www.zerobluetech.com)

           

           

          T 201-299-6223 (NJ) .

          212-842-8830 (NY)

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

          .

          917-660-7221 (C)

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

          FileMaker Certified in 10 and 11

          people + products + events + todos + invoices + documents = productivity

          • 2. Re: Logging Script Trigger
            comment

            user12837 wrote:

             

            The list of fields in the calculation UltraLog( LogData; ModStamp; "TheText¶TheText[2]¶TheNumber¶TheDate¶TheTime") would be the length of a short story -- much too tricky to maintain.

             

            Perhaps you should look into that.

            • 3. Re: Logging Script Trigger
              keywords

              I agree with Michael---take a closer look at Ray Cologon's UltraLog. Each entry in the log is only half a dozen columns wide, recording what was changed (both old entry and new), by whom and when. The list of fields is just a listing of the fields which will trigger an entry in the log. The longer the list, the more entries, but they will all be the same number of columns. From my experience, I'd say that UltraLog is exactly what you need.

              • 4. Re: Logging Script Trigger
                comment

                keywords wrote:

                 

                I agree with Michael---take a closer look at Ray Cologon's UltraLog.

                 

                Ahm... actually, I meant look into the number of fields in your table. In most cases, "a table with hundreds of columns" is a result of poor data structure.

                • 5. Re: Logging Script Trigger
                  keywords

                  I agree with THAT too!

                  • 6. Re: Logging Script Trigger
                    comment

                    LOL, you are easy!

                    • 7. Re: Logging Script Trigger
                      user12837

                      Obviously.  But the user doesn't want me to mess with the table structure, I can't afford to insult him, and I don't have time anyway.  What he wants is a logging mechanism that works with the current structure.

                      • 8. Re: Logging Script Trigger
                        DavidJondreau

                        You could use Design functions or ExecuteSQL() and some custom functions to get a list of field names and their repetitions into the UltraLog() function. Wouldn't be pretty, but probably functional. But the triggering should work ok...

                         

                        "But onObjectEnter does not fire when one selects a field within a portal (it fires only when a different portal row is selected)."

                         

                        I'm not sure what you mean here. It should work just fine.

                        • 9. Re: Logging Script Trigger
                          user12837

                          >"But onObjectEnter does not fire when one selects a field within a portal (it fires only when a different portal row is selected)."

                          >

                          >I'm not sure what you mean here. It should work just fine.

                           

                          You’re absolutely right.  I misinterpreted this caveat from http://www.filemaker.com/11help/html/script_trigg.38.2.html#1029602:

                           

                          If OnObjectEnter is assigned to a portal object, it will activate whenever a different portal row becomes active. Clicking another object in the same portal row will not cause the portal’s OnObjectEnter script trigger to activate again.

                           

                          I somehow missed the distinction between a portal object and an object within a portal object.

                           

                          Thanks,

                           

                          Tom

                          • 10. Re: Logging Script Trigger
                            nsabel

                            You could use Design functions or ExecuteSQL() and some custom functions to get a list of field names and their repetitions into the UltraLog() function. Wouldn't be pretty, but probably functional. But the triggering should work ok...

                             

                            I argree with David executeSQL + custom function + UltraLog is the best option. It would be easy to implement and maintain no matter the size of your table. For the sql all you need is this one simple statement to get the names from the system table.

                             

                            ExecuteSQL ( "SELECT FieldName, FieldReps FROM FileMaker_Fields WHERE TableName = ?" ; "" ; "" ; "Your_Table_Name_Here" )

                             

                            That will return a CR delimited list of FieldName, # of Repititions then all you need to do is use a simple recursive custom function to build the repititions into the list.

                             

                            Nick