12 Replies Latest reply on May 18, 2015 5:13 PM by stephensexton

    Saving a record to a history table

    Polarpro

      Hello,

       

      In one of my solutions it is required to make a copy of every record and save it to a history table, before the record is being changed. I tried several ways in the past; but wasn't too happy with it.

       

      My question is: Is there a not long-winded way of easily creating a complete copy of a record and save it to another table (that has the same structure), so that in a portal in the original table I can show all the records of the history table that belong to the original table's record.

       

      Thanks :-) Mike

        • 1. Re: Saving a record to a history table
          jrenfrew

          Assuming the tables have identical fields

          Export the single record as XML or an FMP file and immediately import to new table.???

          • 2. Re: Saving a record to a history table
            erolst

            Off the top of my head:

             

            # on source layout/TO of DataTable

            New Window

            Set Variable [ $ID ; DataTable::primaryKey ]

            Find Matching Records [ DataTable::primaryKey ]

            Go to Layout [ History ( targetTO ) ]

            Import [ from: DataTable ]

            Set Field [ HistoryTable::id_dataTable ; $ID ]

            Close Window

            • 3. Re: Saving a record to a history table
              tom.earnest

              Increment a Field each time an update is made.  Then Transfer the update to the External file.  Then the External file has a copy of all your updates or at least the fields in the record that you want to view.  Keep the ones you want to keep, i.e. most recent, or last 3, etc.  Delete the other history records.  You get to play around with this to make it work.

              • 4. Re: Saving a record to a history table
                lhallberg

                Mike,

                 

                1.  Create a calculation field called Field Values in your Records table which concatenates all your field values together using a obscure text separator.  Example using 3 pipe symbols.  Field1 & "|||" & Field2 & "|||" & Field3... etc

                2.  Create a text field in your History table called Record Dump

                3.  Create a calculation field with number result in your Records table call Modification ID = Get( RecordID ) & "." & Get ( RecordModificationCount ), and Create a Modification ID number field in your History table

                4.  Create a 1 to 1 relationship between your Records table and your History table from Modification ID = Modification ID with ability to create new records

                5.  On your record editing layout place a portal of the visible edge of the layout (way to the right) based on the 1 to 1 relationship you created in step 4.

                6.  When your Trigger or Button script runs to transfer the latest history, have it go to the portal on the layout, go to last record (new record), set the Record Dump = Field Values.

                7.  The fields in your History table can be calculation fields that pull their corresponding values from the Record Dump.

                 

                Some methods are heavier on scripting, or calculations, or relationship schema.  This method is a little heavier on the calculation side.  Hope it helps.

                 

                -lance

                • 5. Re: Saving a record to a history table
                  erolst

                  lhallberg wrote:

                  […] Some methods are heavier on scripting, or calculations, or relationship schema.  […]

                  …and some were written by Rube Goldberg …

                  • 6. Re: Saving a record to a history table
                    electon

                    Is the solution running on a server?

                    I think wimdecorte provided a method with Perform script on server.

                    Basically how that works is this:

                    The client holds a record that he edits in cache, the server has a record that is before editing started.

                    An OnRecordCommit script trigger fires a Perform Script On Server routine passing any relevant id's of the current record so it can be identified / found. The script finds the record(s) and makes a duplicate. If all is fine the record is committed with the new changes.

                    • 7. Re: Saving a record to a history table
                      Polarpro

                      Hey there,

                       

                      Thank you all for your input,

                       

                      I just tried Lance's solution, it works great, and erolst's solution, which works fine, too. And Lance, Thanks for reminding me of Get ( RecordModificationCount ). I had something like your suggestion in mind, but didn't think of this Get function.

                       

                      Cool  :-) 

                      • 8. Re: Saving a record to a history table
                        keywords

                        You might also like to take a look at Ray Cologon's Ultralog (NightWing Enterprises - UltraLog v2.x Audit Logging Demo for FileMaker Pro), which addresses this audit trail issue very neatly.

                        • 9. Re: Saving a record to a history table
                          Polarpro

                          Hello,

                           

                          I've come across another solution that works different:

                          1. In Source table create a text field called Uuid.
                          2. In History table create a text field called Uuid.
                          3. Create Relationship Source::Uuid = History::Uuid. Allow creation of records via this relationship (on the right side): yes.
                          4. In the History table all fields contain Looked-up values that get their values from the Source table through the relationship created in 3.
                          5. Create and run script:

                          step 1: Set Variable $UUID = Get ( UUID )

                          step 2: Set Field Source::Uuid = $UUID

                          step 3: Commit Record

                          step 4: Set (via relationship) Field History::Uuid = $UUID

                          step 5: Commit Record

                          step 6: Set Field Source::Uuid = ""

                          step 7: Commit Record


                          This solution seems to have the advantage that when you add or delete a field you do not need to take care of any import scripts. Sure, it can happen that you forget to add a field in the History table, but you will not accidentally put a value into the wrong field.



                          • 10. Re: Saving a record to a history table
                            siplus

                            Don't forget to also create a new record in history every time you create a new record in source...

                            • 11. Re: Saving a record to a history table
                              Polarpro

                              Good point  :-)

                              • 12. Re: Saving a record to a history table
                                stephensexton

                                This method of Perform Script on Server works well.  You could send the relevant fieldname/value pairs as script parameters and identify the fields that have changed via Get(ModifiedFields)... thats if you use a field-level audit trail approach, rather than record-level (where all fields in the record are duplicated regardless of whether every field has changed or not).