7 Replies Latest reply on Oct 11, 2012 1:02 PM by rmercado

    Using relational database to track changes

    JessicaBrogan

      Title

      Using relational database to track changes

      Post

      Hi,

       

      I'm new to setting up relational databases.  I have a database in place with one table that contains 85 fields of information.  I want to be able to track every time a change is  made within this table.  I know how to create an auto enter modified by field and an auto enter modified timestamp field.  Those two fields work just fine, but every time someone new makes a change, the modified by and modified timestamp is overwritten with the user name and time stamp of whoever last modified the record.  I need a way to track every time a modification was made to the record and who modified the record.  Nothing I have tried has worked.  I'm thinking that I need a separate table that creates a new record each time a record in my original table is modified.  In the new table I want to be able to see the unique value assigned to the record that was modified and a history of every time a modification was made to the record.  I would prefer to have only one record in my second table for each record that was modified that listed  multiple modifications.  Help!!!

       

      Thanks,

       

      Jess

        • 1. Re: Using relational database to track changes
          JessicaBrogan

          I forgot to mention I'm using FM Pro 11 Advanced on a Mac.  I'm not sure which version of OSX I'm running.

           

          Thanks,

           

          Jess

          • 2. Re: Using relational database to track changes
            philmodjunk

            This is not a simple thing to do.

             

            The script trigger OnRecordCommit could be used to log User Account Name and the current time stamp in a related table.

             

            Tracking all changes to all fields?

             

            The method that comes to mind would be to define a second table that has the same fields/types as your current table. You'd then isolate the current record in a single record found set and use import records to copy this record to this new table. THis saves each new version of a given record. Comparing each such record with it's predecessor would tell you which fields have been altered. Since this record can include fields that auto-enter modification account names and time stamps, you wouldn't need a separate log for that information.

            • 3. Re: Using relational database to track changes
              JessicaBrogan

               

               


              PhilModJunk wrote:

              This is not a simple thing to do.

               

              <!--  StartFragment  -->This is proving to be very complicated.  I've tried several different ways of creating tables, joining them, and using a combination of portals and look-up fields.<!--  EndFragment  -->

               

              Tracking all changes to all fields?

               

              <!--  StartFragment  -->I am not nearly as interested in what was changed in each record.  I am really only interested in tracking each time a record is modified.  I have a test database of 100,000 records.  Each of those records has a unique tracking number that was pre-assigned to it (in other words, it is not an auto enter serial number).  In my original table I added two fields, auto enter modification user and auto enter modification timestamp.  In my second table, I created three fields, the unique tracking number, and a look-up of the modification user and look-up of modification timestamp.  If I join the two tables via the unique tracking number, nothing happens because there is no data in the unique tracking field in the second table.  I made sure to allow creation of records in the second table.  So  I populated the second table by importing only the unique tracking number information.  When defining the look-up fields, the program says it will only look up the information when the unique tracking number changes in the first table.  Well, that is static information that will not change.

               

              <!--  StartFragment  -->I'm thinking the ultimate solution is to keep the two auto enter fields in the main table to track the most current user and timestamp of modification and to have the second table create a new record each time the information in the timestamp field changes in the original table.  Each time a record is created in the second table, the unique tracking number needs to be automatically entered along with the user name.  I then think I could put a portal in the original table that lists every time that record was modified.  But it never works....I know I'm close, but I can't quite get there.

               

              <!--  StartFragment  -->

              Your help is greatly appreciated!!!!!!

               

              Jess

              <!--  EndFragment  -->

               


               



               

               

              • 4. Re: Using relational database to track changes
                philmodjunk

                A simple logging script could be set up:

                 

                Set Variable [$TrackingNumb ; YourTable::TrackingNumber ]

                Set Variable [$Name ; Get ( AccountName ) ]

                Set Variable [$TimeStamp ; Get ( CurrentTimeStamp) ]

                Go To Layout [ModificationLog (Modification Log)]

                New Record/Request

                Set Field [ ModificationLog::TrackingNumber ; $TrackingNumb]

                Set Field [ModificationLog::Name ; $Name ]

                Set Field [ModificationLog::ModificationTimeStamp ; $TimeStamp]

                Go To Layout [Original layout]

                 

                Use the layout based script trigger, OnRecordCommit, to perform this script every time a record is modified.

                If you link your table to ModificationLog by the tracking number fields, you can set up a portal that lists the entries to this log made for the current record.

                 

                Note: Since a tracking number is apparently entered by hand, you may want to use an auto-entered serial number field as the primary key for table and keep the tracking number field simply for search and identification purposes. You'd then use this serial number field in place of Tracking Number in the above scripts and relationships. (This protects you from issues that arise if you discover you have previously entered a tracking number incorrectly.)

                • 5. Re: Using relational database to track changes
                  JessicaBrogan

                  Phil,

                   

                  I'm going to try this right now.  I've never done a script before so I hope I can get this to work!  I cannot thank you enough for helping with this.  I'll let you know how it goes....

                   

                  Jess

                  • 6. Re: Using relational database to track changes
                    JessicaBrogan

                    It works!!!!!  I can't tell you how happy I am.....can I send you a cookie basket?

                     

                    Jess

                    • 7. Re: Using relational database to track changes
                      rmercado

                           Can you add to this Script that someone has to supply a reason for the change to the field?

                           -Ronie