9 Replies Latest reply on Dec 11, 2008 5:32 AM by LeoCA

    Create Change History Records

    LeoCA

      Title

      Create Change History Records

      Post

      We want to insert records into a change history table (invoice_Chg_hist) every time a record gets changed on the target table (invoices).  invoice_Chg_hist has all columns in invoices plus user_id and change_date columns. This can be done using Trigger on other database.  Does FileMaker have Trigger feature?  If not, can we execute SQL statements from scripts?  I am thinking:

       

      INSERT INTO invoice_Chg_hist

      SELECT *,user_id,SYSTEMTIME

      FROM invoices

      WHERE invoice_ID=...

       

      OR, any idea of implementing similar feature in FileMaker.

       

      Thanks.

       

      LeoCA

        • 1. Re: Create Change History Records
          Orlando
            

          FileMaker does not support field triggers natively, however you can use a plug-in to achieve this.

           

          Have a look at a plug-in called EventScript by Software4Humans

           

          http://www.softs4humans.com/FMPro_Plugins.html

           

          You can have this run a script when a field is modified and pass any information you require into your history table.

           

          Also look at Nightwing Enterprises' SuperLog Audit System http://www.nightwing.com.au/FileMaker/demos8/demo809.html which is what I based my Audit Log system on.

           

          Do post if you need any help with this.

          • 2. Re: Create Change History Records
            LeoCA
              

            Thank you Orlando.

             

            I suppose that the Plug-in requires workstation administrator's right to install. I will need to go through our IT governing process to make it part of the workstation image, which takes time.  I try to see if I can accomplish this trigger type of feature through programming.  Any other suggestions?

             

            Thanks.

             

            LeoCA

            • 3. Re: Create Change History Records
              VinceDolan
                

              Leo

               

               

              I am not sure if you are running FM Server, but if you are you can deploy and update plugins from the server (no admin rights are required to do this, as far as I know) and some simple scripting. Refer to the Server Docs for specifics.

               

              PS I use zippscript, it is free and pretty simple to develop with.

               

              Vince Dolan 

              • 4. Re: Create Change History Records
                Orlando
                  

                Plug-ins dont have to be installed in the Applications folder, which you may not have rights to, they can also be installed in the user folder, specifically:

                 

                Mac: User/Library/Application Support/FileMaker/Extensions/ 

                Windows: Users/User Name/AppData/Local/FileMaker/Extensions/ 

                 

                You should have permissions to install in this part of the system if the rest is locked down by your administrator.

                 

                I hope this helps. 

                 

                If you can install the plug-in and you do want a copy of a demo I put together to do this contact me back channel and I will email the file over to you.

                • 5. Re: Create Change History Records
                  LeoCA
                    

                  Thank you Orlando and vinced.

                   

                  We have about 5 workstations with FileMaker Pro 8.5.  Workstations are running Windows XP on an intranet.  All users are general users without OS administrator's rights.  The application is developed in two .fp7 files (with defined file references for connection).  Both files are placed in a shared folder of network drive.  First user opens the Master.fp7 with sharing and others open it using "Open Remote". Developers open the file with "Full Access Account Name" for making changes (from any workstation) while general users use Account Name with "Privilege Set" for processing.  The other .fp7 file is transparent to general users when they are in FileMaker application.

                   

                  I would like to try Orlando's plug-in approach.  Any suggestion on some free plug-in's I can try?

                   

                  Thanks.

                   

                  LeoCA

                   

                   

                  • 6. Re: Create Change History Records
                    Orlando
                      

                    Glad to help,

                     

                    you can try EventScript by Software4Humans http://www.softs4humans.com/FMPro_Plugins.htm although it has been pointed out to me recently that there are FM 9 / Vista issues with some of its functionality.

                     

                    However another alternative is myFMbutler's DoScript http://www.myfmbutler.com/index.lasso?p=416 I aim to test this out soon, so I don't know how it compares to EventScript at this time.

                     

                    Both plug-ins are completely free. 

                    • 7. Re: Create Change History Records
                      LeoCA
                        

                      Hi Orlando,

                       

                      I put the downloaded plug-in (EventScript.fmx) in the "C:\Documents and Settings\User Name\Application Data\Local\FileMaker\Extensions\" folder.

                      My workstation did not have these folders "Local\FileMaker\Extensions\" under the personal area.  I need to create them.

                       

                      After then, I opened "EventScript Samples.fp7" that came with plg-in download and can't see the plug-in.

                       

                      The "README EventScript 1_1.txt" indicated the plug-in should be installed in "c:\Program Files\FileMaker\FileMaker YY (7 or 8)\Extensions"  folder.  General users do not have rights (OS Administrator's right is required???) to update this folder.  Therefore, I can't install it.

                       

                      Am I doing wrong?  Thanks.

                       

                      LeoCA

                      • 8. Re: Create Change History Records
                        Orlando
                          

                        Sorry to hear this is not working for you. May I just check whiat version of FileMaker you are using and what version of Windows you are using?

                         

                        I have recently found out about some issues with EventScript and Vista. Also the Extension location may be a new feature in FileMaker 9.

                         

                        Another option if you are using Vista is to try a similar product called DoScript by myFMbutler http://www.myfmbutler.com/index.lasso?p=416. This works better with Windows and has a few more features from what I can tell. 

                         

                         

                        • 9. Re: Create Change History Records
                          LeoCA
                            

                          Thank you for your reply Orlando. 

                           

                          Our OS is Windows XP Professional 2002 Version with Service Pack 2. 

                          FileMaker is Filemaker Pro 8.5v2.

                           

                          Thank you for the information on "myFMbutler DoScript plug-in 2.0.1".  I will see if it can meet our needs.

                           

                          LeoCA