1 2 Previous Next 27 Replies Latest reply on Dec 18, 2013 11:04 AM by philmodjunk

    Best practices for history table

    EP

      Title

      Best practices for history table

      Post

           Just wondering what the best practices are for creating a history table.  I have a human resources file and the parent record just contains a serial number and employee status (active, terminated, etc).  This parent record has related records (RR) that contain all the personnel info. This allows for all changes to be logged by creating new records each time information is changed.  My original idea was to have the creation of a new RR by duplicating the last RR so all of the info from the last record populates in the fields and the user would just change the appropriate fields.  A layout based on the parent record would display the latest RR via a relationship sort.  Is there a way to not have to duplicate the last RR; rather the user would simply enter data into the fields that need updating, and leave the other fields blank.....then somehow display the updated fields on the parent record layout, but use data from the second to last RR for the other fields that were not updated?  This would allow someone to easily view what was updated during a record audit.

           Thanks

            

        • 1. Re: Best practices for history table
          philmodjunk
               

                    My original idea was to have the creation of a new RR by duplicating the last RR so all of the info from the last record populates in the fields and the user would just change the appropriate fields.

               You shouldn't need to duplicate any data from a previous related record. That suggests that you are storing data in the history table that should instead be stored in the employee table so that all related records can access that data without the need to duplicate it each time.

          • 2. Re: Best practices for history table
            EP

                 Correct.  I don't understand how else to set this up.  If I store the data in the parent record, and want to change an employee's phone number, last name (due to say, marriage) and pay rate.....how would I change this in the parent record but be able to see the history (the old phone number, last name, pay rate, and when it was changed?

                 Thanks

            • 3. Re: Best practices for history table
              philmodjunk

                   In that case, you would need to put that data somewhere, and duplicating the most recent related record would be one way to do that.

                   But you could also just log the "change" instead of duplicating the entire record. Example: You could have a field in the History record labeled "old Phone". When you update an employee's phone number, a script trigger on the field creates a new record in the history table with the original phone number in the the Old phone field. Data that remains unchanged would be left blank in their "old" fields in the history record.

              • 4. Re: Best practices for history table
                EP

                     You got me thinking now.  What if I input the data in the parent table and if a user clicks an "edit" button, they are taken to a layout where each field has a script trigger that creates a new RR for each field that is edited via a script trigger on each field, like you suggested.  

                • 5. Re: Best practices for history table
                  philmodjunk

                       Yes, but why do you want to make them click an edit button in order to make those changes? Why can't you put the triggers on the first layout and then not need a second layout for editing?

                  • 6. Re: Best practices for history table
                    EP

                         Bc I make my records read-only to avoid unintentional changes.  An "edit" button pops up an editable layout with "ok" and "cancel" buttons.

                    • 7. Re: Best practices for history table
                      philmodjunk

                           Ok and that works, but you can also do it like I have set up a number of fields on the Tutorial layout in the Known Bugs List Database. When you enter a field, the OnObjectEntry script trigger uses a script parameter to capture the original value of the field and puts it in a global variable. The OnObjectSave trigger then compares the current value of the field to the original value in the variable. If the original value is not empty and does not match the current value, a dialog pops up asking the user to confirm their edits. If they click "Revert", the script copies the value from the variable back into the field to revert the field.

                           That sounds like a technique that could be adapted to generate the history record if the user confirms the change in the dialog.

                      • 8. Re: Best practices for history table
                        EP

                             So in your file, each field edit creates a new RR, not a new RR with multiple edited fields?  I am trying to figure out a way to display changed fields (history) in a portal with a "revised on xx /xx /xxxx" timestamp, but don't want a huge portal with all fields.  Is there a way to ONLY show changed fields in a portal? Maybe stack all the fields on top of each other, and only the field with text will show through?

                        • 9. Re: Best practices for history table
                          EP

                               Thinking some more, I'm considering making 2 fields. PersonnelHistory::oldData and PersonnelHistory::NewData.  I will use these 2 fields in my portal to reflect the old value and new value (for audit purposes).  I will rid of all the matching fields I created in the related table (fields that match the personnel table).  Thanks Phil.

                          • 10. Re: Best practices for history table
                            RickWhitelaw

                                 Sorry. Once again typed out a long detailed response and crashed. Will not try again. Bad Forum software.

                            • 11. Re: Best practices for history table
                              EP

                                   So, I have my OnObjectEnter and OnObjectSave scripts set up.  Since I have so many fields that are editable, is there a shortcut to not have to name the field in the script parameter, or do I need to take the time to change the script parameter for each field?

                                   Also, since I'm using only 2 fields to reflect the changes in the record (PersonnelHistory::oldData and PersonnelHistory::newData), is there a way to capture the name of the field and use it with a tooltip, so a user can hover over PersonnelHistory::oldData in a portal and see that the Personnel::LastName field was changed (I'd have it display "Last Name" however?

                                   Thanks

                              • 12. Re: Best practices for history table
                                philmodjunk

                                     I use the field name in the script parameter because I can get the original value of the field no matter what field format is used. You can use Get (ActiveFieldContents) to access this value with OnObjectEnter as long as the field is not formatted with a drop down list or pop up menu.

                                • 13. Re: Best practices for history table
                                  EP

                                       I have a couple fields that use a popup menu.  I guess I will just add a unique script parameter to each field.

                                       Since I'm using only 2 fields to reflect the changes in the record (PersonnelHistory::oldData and PersonnelHistory::newData), is there a way to capture the name of the field and use it with a tooltip, so a user can hover over PersonnelHistory::oldData in a portal and see that the Personnel::LastName field was changed (I'd have it display "Last Name" however?

                                       Thanks

                                        

                                  • 14. Re: Best practices for history table
                                    philmodjunk

                                         If you add a field for storing the field name, Get ( ActiveFieldName ) will return the name of the field being modified and thus your script can store this in a field in your related table. Your tool tip can then use a calculation to refer to this text field to show the field name.

                                         PS. You can even use this data to "roll back" a value in a field to a previous value.

                                    1 2 Previous Next