12 Replies Latest reply on Apr 2, 2015 2:56 PM by disabled_jackrodgers

    Updating Solution keeping Records Updated

    raphthepenguin

      Hey,

       

      I think this is probably an easy question... at least I hope so!

      I am working on advancing a solution that is already been used. So I was wondering what is the best way to update the new version with the records of the old one.

      Simply importing to seems to just take the records from one Table.

       

      I am sure more people do this and so I would love to have some input here, so I dont have to recheck every Record!

       

      Thanks

        • 1. Re: Updating Solution keeping Records Updated
          Mike_Mitchell

          Raph -

           

          You're correct. Migrating data from one copy of a solution to another is a common chore. Most of us script it, so we don't have to do it manually (which is a pain, and contributes to errors anyway). As you've discovered, the Import Records command is single table to single table, and manual updating works poorly in most real-world solutions (which typically have dozens of tables, or more).

           

          Importing is one method for accomplishing the task, but it does have some weaknesses. If a record fails to update for any reason, you won't know which record failed (because it's processed as a single operation). You just know there was an error. Another method is to use something Ray Cologon calls "processing in place", where you loop over the records to be updated and move the records from the old solution to the new one, updating a global key field for each record as you go. This has the advantage of being able to trap and log specific errors on a field-by-field basis.

           

          Other considerations during migration include making sure you update any file references for new file names (often, the version number for a product is included in the file name, so you have to update the file references or things won't work right), making sure you disable auto-entry on selected fields (you want to preserve values like modification and creation date, time, account name, etc.), and updating the next serial value for any fields that use serial IDs.

           

          If that sounds too daunting, there's a commercial product called RefreshFM from Goya (http://www.goya.com.au/refreshfm) that will help you automate updates.

           

          HTH

           

          Mike

          • 2. Re: Updating Solution keeping Records Updated
            raphthepenguin

            I think I start understanding the process...

             

            Is there a step for step guide somewhere? I do think that this skill will be worth for me learning!

            • 3. Re: Updating Solution keeping Records Updated
              Mike_Mitchell

              Not that I'm aware of. Most of us have just developed our own versions over the years.

              • 4. Re: Updating Solution keeping Records Updated
                raphthepenguin

                Uh, thats tough! But you had to start somewhere? Right?

                • 5. Re: Updating Solution keeping Records Updated
                  Mike_Mitchell

                  Indeed. I started with a manual process. Moved up to a scripted process using Import Records, which had to be tailored to each solution (and each upgrade). Used that for a while, until I started using a process that is much more transportable. The current process I use is based on the synchronization guide (attached), and basically uses an external file to move the data from the old file to the new one through a “processing in place” script. That way, I can use a table that contains information about the tables in the solution, including field mapping, and do all the exclusions I need to (along with all the proper error checking). I can also populate that table using ExecuteSQL to parse out the table schema in the old and new file as a starting point.

                   

                  But I suggest you either start smaller, using Import Records, or purchase the commercial product. Learning is good; learning too fast will result in outrunning your skill level and you'll get yourself into trouble.

                   

                  Mike

                  1 of 1 people found this helpful
                  • 6. Re: Updating Solution keeping Records Updated
                    raphthepenguin

                    Thanks! I will put that on the backburner and slowly start learning about it!

                    • 7. Re: Updating Solution keeping Records Updated
                      raphthepenguin

                      So I have now found time to look into this more!

                       

                      I am a bit unsure about how to use the Import script correctly. I have played with it a bit and find that following works well... But I wanted to see if this is the right approach!

                       

                      Go through every layout

                      Delete all existing Records (so Database is completely empty)

                       

                      Import All files from "motherfile"

                      Repeat for every table

                       

                      It seems to work! But I would appreaciate some feedback on this!

                      Thanks

                      • 8. Re: Updating Solution keeping Records Updated
                        Mike_Mitchell

                        Yes, that is one approach. It does have some issues, however:

                         

                        1) If an error occurs with the import, you’ve blown away your old data. That may or may not be a problem for your particular solution, but it usually is.

                         

                        2) If someone has one or more records locked during the delete operation, those old records will still remain. So you can possibly end up with a mixture of old and new data.

                         

                        If you can live with those possibilities, then this approach will work.

                         

                        Mike

                        • 9. Re: Updating Solution keeping Records Updated
                          Malcolm

                          Go through every layout

                           

                          Delete all existing Records (so Database is completely empty)

                           

                          You can do the same thing safely and easily in one step. Save as Clone.

                           

                          Import All files from "motherfile"

                           

                          Repeat for every table

                           

                          Putting this into a script will make it easier.

                           

                          Malcolm

                          • 10. Re: Updating Solution keeping Records Updated
                            Mike_Mitchell

                            The Save as Clone strategy is excellent for a few reasons:

                             

                            1) It's fast.

                            2) It is way easier on the database than delete / import.

                            3) Cloning rebuilds a lot of database structure from the ground up, contributing to stability going forward.

                             

                            The only thing you have to be careful about is making sure you bring in anything from the new version that doesn't yet exist in the old version, such as settings or defaults in new tables. Your scripts should include these options.

                            • 11. Re: Updating Solution keeping Records Updated
                              raphthepenguin

                              Thanks guys!

                               

                              As for your concerns Mike:

                               

                              to 1) I will always have a backup before deleting, so I will have still access in case things are getting corrupted.

                               

                              to 2) I am the only one using this method for developing reasons. So here as well I think the issue is overseeable!

                               

                               

                              As for the clone...

                               

                              Thanks alot, I will use that step. Do you know if ID counts will be reset in clones, that would be the only issue here!

                               

                              Thank you guys so much for your input! I can not express my greatfulness!

                              • 12. Re: Updating Solution keeping Records Updated

                                There are some gotchas involved in importing.

                                 

                                Auto Serial Numbers:

                                Do not let them trigger when you import. Turn off the auto serial and turn it back on later.

                                Update the serial number counting at the end of the import. This can be less than easy if for instance a lot of invoices where deleted near the end of the numbers but the invoice items where not. If you simply go to the largest serial number and set your next serial number to +1, you're in trouble.

                                 

                                Modified date

                                Test whether or not your import will affect the modified date if you are using that for anything important

                                 

                                Calculation sequence

                                In older versions the order in which calculated fields were created determined how they calculated (is that right). No longer works that way. Is this important?

                                 

                                Which file to import first, which scripts to import, which layouts and objects to copy...

                                 

                                Next are calculations that are not based on two original tablels but on duplicated TOs or TOs to TOs to TOs  (sorry).

                                 

                                Sometimes it gets so complicated it is just simpler and cheaper to rewrite the whole thing.