1 2 Previous Next 23 Replies Latest reply on Jun 23, 2017 8:43 AM by tods

    Easy way to copy an entire record to another table?

    tods

      In the solution I am working on I have a table of Service records.  This table has 87 fields in it.  I am trying to create a Service record history table that is an exact copy of the Service record table.  The only difference between the tables is the primary key on the Service record table is a unique serial number and i turned off the unique serial number on the history table.  I created a one to many relationship using that field from the Service record table to the Service record history table. I want to copy the existing record over to the Service record history table when the user hits the "Edit" button.

       

      This way, if the user makes a mistake in editing, we can go into the Service records history table, find the last edited record and restore it.

       

      I can't think of any other way other than having 87 Set Field lines to copy over the data, and this won't scale as we add more fields to the table.

       

      Is this possible?

       

      -Tod

        • 1. Re: Easy way to copy an entire record to another table?
          MichaelManousos

          Why dont you make an audit log to track all changes? So in evere record you will know exactly who/when/what changed and you can have a roll-back feature.

          • 2. Re: Easy way to copy an entire record to another table?
            StephenWonfor

            Tod

             

            I like Michael's idea of the audit log but, if the tables are a 1:1 field match, you could fire up an import script that pulls the data then timestamps the import date so you can access a history of the main table.  Of course, you;'d need to manage the field additions and update the script.

             

            Stephen

            1 of 1 people found this helpful
            • 3. Re: Easy way to copy an entire record to another table?
              BruceRobertson

              Why are there 87 fields? What makes you think you need to add more? Sounds like a design (data structure) problem.

              • 4. Re: Easy way to copy an entire record to another table?
                Ben

                Hi Tod,

                 

                you might find this article helpful, if I understand correctly what you want to do - create a way to save edits and have a roll-back/undo feature.

                 

                fmLog – AppWorks

                 

                I hope this helps.

                1 of 1 people found this helpful
                • 5. Re: Easy way to copy an entire record to another table?
                  tods

                  Why dont you make an audit log to track all changes? So in evere record you will know exactly who/when/what changed and you can have a roll-back feature.

                  I am trying to make an audit log, but all of the solutions I have been able to find require I put something on each and every field I want to track, which doesn't scale well for my application.

                  • 6. Re: Easy way to copy an entire record to another table?
                    tods

                    I like Michael's idea of the audit log but, if the tables are a 1:1 field match, you could fire up an import script that pulls the data then timestamps the import date so you can access a history of the main table.  Of course, you;'d need to manage the field additions and update the script.

                    This is the problem.  I don't want to manage that as I will inevitable end up missing important fields in my log.

                    • 7. Re: Easy way to copy an entire record to another table?
                      tods

                      Why are there 87 fields? What makes you think you need to add more? Sounds like a design (data structure) problem.

                       

                      This Service record table has 87 fields in it because it is a table of records for a phone company.  They include things like current POTS line, phone number, address, cable pairs, and tons of technical information that only applies to that line.  If i broke it up into separate tables, it would all relate back to the POTS line on a 1 to 1 relationship so there's no point in putting it in different tables.

                      • 8. Re: Easy way to copy an entire record to another table?
                        tods

                        you might find this article helpful, if I understand correctly what you want to do - create a way to save edits and have a roll-back/undo feature.

                         

                        fmLog – AppWorks

                         

                        Ben,

                         

                        I watched that video, but it logs based on specific fields, not whole records like I want.

                         

                        -Tod

                        • 9. Re: Easy way to copy an entire record to another table?
                          philmodjunk

                          As I recall, POTS = Plain Old Telephone Service

                           

                          The difference between an audit log that logs each change on a field by field basis and keeping a change log that duplicates the entire record is like the difference between making back ups of your entire database file or making incremental back ups.

                           

                          Both can fully back up your data, but an incremental back up, by backing up only the changes made can do so while creating a file that is much smaller than complete copies of your file.

                           

                          It's your choice, but a field level audit log can be used to fully restore and/or review changes made to your data, but it only needs to log info on those specific fields that were actually changed. If all 81 fields are changed every time, there's no advantage to using a field level process. But if only 10 fields were changed, the amount of data needed to log what changed is much smaller for a field level system compared to saving a copy of the entire records where the data from the other 71 fields is redundant.

                           

                          If i broke it up into separate tables, it would all relate back to the POTS line on a 1 to 1 relationship so there's no point in putting it in different tables.

                          Actually, there can be advantages to restructuring your data even if it's all a 1 to 1 relationship to the data. When client session accesses even one field of a record in your table, all 81 fields worth of data has to be downloaded to the client. If you broke it up, just for discussion purposes, into 4 records of about 20 fields, then referencing that 1 field would normally only require 20 fields worth of data instead of all 81. For WANS, WebDirect and FileMaker Go clients, this "narrow table" approach can help improve performance.

                          1 of 1 people found this helpful
                          • 10. Re: Easy way to copy an entire record to another table?
                            tods

                            As I recall, POTS = Plain Old Telephone Service

                            Yes :-)

                            The difference between an audit log that logs each change on a field by field basis and keeping a change log that duplicates the entire record is like the difference between making back ups of your entire database file or making incremental back ups.

                             

                            Both can fully back up your data, but an incremental back up, by backing up only the changes made can do so while creating a file that is much smaller than complete copies of your file.

                             

                            It's your choice, but a field level audit log can be used to fully restore and/or review changes made to your data, but it only needs to log info on those specific fields that were actually changed. If all 81 fields are changed every time, there's no advantage to using a field level process. But if only 10 fields were changed, the amount of data needed to log what changed is much smaller for a field level system compared to saving a copy of the entire records where the data from the other 71 fields is redundant.

                            I understand the difference.  i'm dealing with a bug in my solution that is overwriting entire records, and I can't replicate it in any of my testing.  So I want the whole record backed up just in case until I can find the root cause.

                            Actually, there can be advantages to restructuring your data even if it's all a 1 to 1 relationship to the data. When client session accesses even one field of a record in your table, all 81 fields worth of data has to be downloaded to the client. If you broke it up, just for discussion purposes, into 4 records of about 20 fields, then referencing that 1 field would normally only require 20 fields worth of data instead of all 81. For WANS, WebDirect and FileMaker Go clients, this "narrow table" approach can help improve performance.

                            I did not know this, but it doesn't matter for this solution, as all 87 fields need to be loaded on the main layout anyway.  Yes, it is a pain, but I can't do anything about that right now.

                             

                            -Tod

                            • 11. Re: Easy way to copy an entire record to another table?
                              Ben

                              So you want to track changes, but you don't want to create a log of changes using something like fmLog or similar?

                               

                               

                              Carrying on from StephenWonfor's idea suggested above. Perhaps you could set a trigger, onRecordLoad to export the entire record when a user starts to edit - you'd have to think about how to control if a user is just viewing or editing - you could add an exportedTimestamp field and perhaps capture the current user with am exportedByUser field, to be set when the trigger runs. This way you have an exact copy with the same primary keys etc. the only difference would be the new timestamp with which you could track all changes. The exported records could be saved in a separate file or imported back into the original file into a duplicate table?

                              • 12. Re: Easy way to copy an entire record to another table?
                                BruceHerbach

                                You could use the "magic key" method of creating a history record.  Take a look at this article:

                                  https://filemakerhacks.com/2011/07/31/magic-key-and-check-box-reporting/

                                 

                                Add a Global field to the POTS table and connect it to a TOC for the history table with the option for "Allow creation of records in this table via this relationship"  checked.

                                 

                                Assuming that the fields in the history table do not require formulas,  you could set up auto enter calculations for all of the data fields that pulls the value from the current POTSrecord.  With Do Not replace existing value checked

                                 

                                In the history table add a foreign key field which will have the primary key for the POTS record.  This will allow a One to Many relationship for POTS to History

                                 

                                When changes are saved to the POTS record have a script create a new History record with a script that does the following:

                                 

                                Clears the Global field

                                Set Field to the TOC of the History table created above with the Primary Key of POTS record.

                                Commit record

                                 

                                At this point you should have a history record with all of the current values from the POTS record.

                                Assuming that you have additional fields with date, time of creation and User Name for the History record,  you now have a everything you need.

                                 

                                You can add a roll back by selecting a history record and having a script go through the fields and reset the values in the POTS record.  In this case you may have 87 set field steps.

                                1 of 1 people found this helpful
                                • 13. Re: Easy way to copy an entire record to another table?
                                  Lemmtech

                                  JSON is your bet bet. You can package all your field names and values into a single value pair JSON array and store the array into a single field in a log table or parse the array into the same fields in your log table if you want. You need plugins/custom functions prior to 16 or upgrade to 16 to do it.  I've built a whole audit log process using JSON or look at this one: Learn FileMaker Pro – Free Function: JSONGetRecord v1.2 (Update)

                                   

                                  Good luck!

                                  • 14. Re: Easy way to copy an entire record to another table?
                                    philmodjunk

                                    No need to get fancy, just use import records to copy the entire record to the other table. You can then set up a relationship to compare a record to it's archived copies if the primary key is not also being overwritten.

                                     

                                    Import records can move data between two tables in the same file. Just make sure that your record is isolated in a found set of that one single record.

                                     

                                    Good luck tracking down the gremlin that's caused you to set up this system.

                                    1 of 1 people found this helpful
                                    1 2 Previous Next