8 Replies Latest reply on Aug 28, 2012 2:57 PM by ShaunHamid

    Create History of Multiple Fields

    ShaunHamid

      Title

      Create History of Multiple Fields

      Post

      I have a somewhat complex idea (well it seems complex to me) and these forums have always steered me right.

       

      I have a database of records connecting to a number of episodes of a television show.  The database is broken down in tables based on the the portions of the show.  The tables go SHOW->EPISODE->MEDIA. 

       

      In the EPISODE table, there is a pair of shipping fields.  One field "ship_status" indicates if it is sent or sent revised and a "ship_date" field lists the date.  What I would like to do is create a method in which if I modify both fields it will create an archived piece of information listing such.  For example, if I shipped an episode on 8/17/12 it would say "Shipped 08/17/12" but if I later ship a revised version it would list the previous ship on 8/17/12 as well as the new shipment. 

      I have been able to create a field "shipping_change_log" that lists modifications of either field in a list with an autocalc text field that uses the following calculation:

      shipping_change_log & ship_status & " " & ship_date & ¶

      It works well except that it creates a list entry if I modify either field, and so I have entries that are half complete.  Is there a more legant way to achieve this?  The only stop gap idea I can come up with is to create an individual log for each field and place them side by side, but that seems clumsy.

      Any help would be greatly appreciated.

       

      Cheers,

      Shaun

        • 1. Re: Create History of Multiple Fields
          philmodjunk

          Hmm, how about this?

          Set up a relationship to the change log:

          Episode::EpisodeID = ChangeLog::EpisodeID AND
          Episode::ChangeRev = ChangeLog::ChangeRev

          Enable "allow creation..." for ChangeLog

          Use the OnObjectSave trigger on Episode::Ship_Status to perform this script:

          If [ Not IsEmpty ( ChangeLog::Ship_status ) ]
              Set Field [ Episode::ChangeRev ; Episode::ChangeRev + 1 ]
              Commit Record
          End If
           Set Field [ ChangeLog::Ship_Status ; Episode::Ship_Status ]

          In similar manner, swap the fields around for Episode::Ship_date:

          If [ Not IsEmpty ( ChangeLog::Ship_date ) ]
              Set Field [ Episode::ChangeRev ; Episode::ChangeRev + 1 ]
              Commit Record
          End If
           Set Field [ ChangeLog::Ship_date ; Episode::Ship_date ]

          Note: set Episode::ChangeRev to auto-enter a 1 and use Replace Field Contents to give a 1 to all existing records.

          • 2. Re: Create History of Multiple Fields
            ShaunHamid

            Hi Phil,

             

            Thanks for always coming up with a solution for my questions.

             

            A couple questions:

             

            1.  ChangeRev, is that a new field or is that my existing field with the change info calc "shipping_change_log & ship_status & " " & ship_date & ¶"

            2.  I proceeded assuming what you said, but this does not give me a list of data, if I set a field to autoenter a 1, the trigger just adds one to that number, so it tells me how many times I have modified the field, but not what those changes were.  I think I might have missed the point here.

             

            Sorry for my silliness.

             

            Shaun

            • 3. Re: Create History of Multiple Fields
              philmodjunk

              1) it's a new field, a number field that increments each time there is a need to log a new pair of "changed" entries in your changeLog table.

              2) Each time you log a change, a new record is created with the logged data instead of a list of the data. Put up a portal to ChangeLog on your Episodes layout and note the records that appear in this portal as you edit data in these two fields.

              • 4. Re: Create History of Multiple Fields
                philmodjunk

                Hmmm, Let's modify that part in 2)....

                If you define this relationship:

                Episodes::EpisodeID = ChangeLogByID::EpisodeID

                where ChangeLogById is a second occurrence of ChangeLog, a portal to changeLogByID will list all the changes made for that Episode.

                • 5. Re: Create History of Multiple Fields
                  ShaunHamid

                  In regards to the script, is it a separate script for each field?  So I create one script to trigger on episode::ship_status (say changelog_ship_status) and one to trigger on episode::ship_date (say changelog_ship_date).  Or are they a single unified script?

                   

                  The portal does not seem to be adding new entries.  It changes the first line only and does not seem to add a new record to the list.  So if I modify the ship_status and then the ship_date on the Episode table, the portal to changelogbyID just changes to correspond to the current value in those fields without adding a new version.

                   

                  Of course part A of my question might affect part B. 

                   

                  Thanks for the patience.

                  • 6. Re: Create History of Multiple Fields
                    ShaunHamid

                    Hmmm, is "Changelog" a table occurance of "Episode" or a child table?

                    • 7. Re: Create History of Multiple Fields
                      philmodjunk

                      Yes, a different script for each field.

                      ChangeLog is a separate table.

                      and you may have missed a post by me:

                      If you define this relationship:

                      Episodes::EpisodeID = ChangeLogByID::EpisodeID

                      where ChangeLogByID is a second occurrence of ChangeLog, base your portal on ChangeLogByID, not ChangeLog and you should see multiple entries for a given episode appear as you modify data in these two fields.

                      • 8. Re: Create History of Multiple Fields
                        ShaunHamid

                        Elegance in database clothing.

                         

                        Thank you again, Phil!