1 2 Previous Next 18 Replies Latest reply on Sep 16, 2012 7:56 AM by willrollo

    How to alter an active database

      Title

      How to alter an active database

      Post

           I was just wondering - How is one supposed to go about altering a database design file while the database is up and running? Obviously you cannot ask all employees to not alter any data while you later the file???

           How is this problem solved? Or have a missed the blindly obvious!?

        • 1. Re: How to alter an active database
          JimMac

               Although I have done it, for minor formatting changes, I know of no way to do it without risk of damage.

               I make my changes on an alternate copy , test it , and depending on extent of changes... schedule a database maintenace downtime for updates.

               Jim...

          • 2. Re: How to alter an active database
            philmodjunk

                 Scripts can automate the import process to pull the records from the current copy into the new and the same script can update the next serial value settings on any auto-entered serial number fields so that new records will still receive unique values.

                 It's also possible to structure your solution into a two file interface and data file system so that updates to the interface file can be deployed simply by replacing the old interface file with the new--minimizing the need for such data imports.

            • 3. Re: How to alter an active database
              JimMac

                   @Phil,  sounds like a way to minimize Database down time. I am glad you mentioned the "next serial number" value and Auto enter serial numbers and as a matter of fact any calculated sequening should be add toowink.

                   But... you still have to shutdown the database to "switch" it and assure all data imports are finished.

                   Jim....

              • 4. Re: How to alter an active database

                     Thanks Phil and Jim - I think the way forward for me is to alter over the weekend on a copy and then replace on monday morning! We omnly have three employees so wont be a problem - at least I didnt ask a stupid question which was my initial worry !

                • 5. Re: How to alter an active database
                  philmodjunk

                       In my opinion, a scripted update should be a required feature for all FileMaker solutions. Not only does it speed up the update process, it can protect you from accidental mistakes such as missing the update of one auto-entered serial number and then getting chaos the next morning when the users start creating records and get duplicate values in a serial number field.

                       It's also very important to run a few tests on a copy of the database after import to make sure that all is in order and then to keep the previous copy closed but uploaded on the server (perhaps in a different folder) so that if your update "blows up" on you, you can quickly shut it down and switch back to the previous copy.

                  • 6. Re: How to alter an active database

                         "a scripted update should be a required feature for all FileMaker solutions"

                          

                         What sort of thing do you have in mind? How would I begin to going about such a script?

                    • 7. Re: How to alter an active database
                      JimMac

                           Will, the script would be just to automate what you would have to do manually to update your database.

                           Mental steps...

                           1) Make a copy of active DB

                           2) Make your changes on copy and test the new features

                           3) Delete all Records from New version

                           4) Shutdown Active DB

                           5) Import all records from Active DB

                           6) Set all Serial numbers in New version to match Active DB's  [as mentioned above]

                           7) move Active DB to a folder for recovery if needed

                           8) Put New version in Server

                           9) Restart Server.

                           10) Cross your fingersyes

                           ......

                           The script would automate steps 5, 6, 7 to minimize errors.

                           Jim...

                      • 8. Re: How to alter an active database
                        JimMac

                             Last comment...

                             It is possible to do the New and Active "side by side" as Phil said...

                             Thus ...

                             You can switch with just a quick "blip" in services... so to speak... but all users would be stalled while "hot switching"

                             You see that from time to time on your home power service.

                             Not my "cup of tea"...

                             Also many advocate 'timed back ups'.... I practise "separated" back ups and updates

                             Thus...

                             Lost of a single hard drive on a single computer is not catostophic.

                             Beauty is in the eye of the beholder...angel

                             Jim....

                        • 9. Re: How to alter an active database

                               #Jim - thank you for your summary  - What I was unclear about was the,""next serial number" value and Auto enter serial numbers and as a matter of fact any calculated sequening should be add too"

                               IS this the setting when you import records -yes or no to go ahead and autoupdate/serials/calcs etc?

                               Would I make a clone of the new DB then run this script and replace on server? Do you have an example "update' scropt that I could look at to see what sort of commands are required?

                               Huge thanks..(to Phil too)

                          • 10. Re: How to alter an active database
                            philmodjunk

                                 There's a script step named Set Next Serial Value that can be used to change the value entered into the next new record to be created. Say you grab a backup copy of the database and start modifying it when such a field has a "next value" setting of 255. During testing you create several records so the next value setting is now 258. Meanwhile, in the live database, they've added 50 new records and the next value setting in that copy is now 305 and the largest serial number in that field is 304. If you just import data into your new version, the next new record will receive 258--a value that will duplicate the value in an existing record. The result can be sheer Chaos and as a new developer many years ago I fell into that pitfall and had to scramble to fix the problem while my client's business was at complete standstill waiting for the fix (and hence my other suggestion of keeping the previous copy close to hand.).

                                 In a script, you can determine the maximum value in the field over all the records you have just imported and then set the next serial value of the field to be at least one greater than that value.

                                 Here's an example script:

                                 #Immediately after importing records into a clone copy of your new version, your found set will consist of all records
                                 Go to Layout [select layout based on the table into which you just imported if you are not already on that layout]
                                 Sort Records [no dialog ; restore ] ---> Sort records by serial number field in descending order so first record contains largest value.
                                 Go to Record/Request/Page [First]
                                 Set Next Serial Value [YourTable::SerialNumber ; YourTable::SerialNumber + 1 ]

                            • 11. Re: How to alter an active database
                              JimMac

                                   @Phillaugh.  Now you appear less than a superhuman FMP programmer.

                                   I shouldn't laugh because that happened me also in FMP 8 , which didn't have  a script step named Set Next Serial Value.  Big mess too.crying

                                   But...

                                   I would use the Design Function  GetNextSerialValue in FMP11 or 12, since is it possible that the last record was deleted.

                                   Also...

                                   I have created my own counters that need updating OLD==>NEW.  Example is printing checks from a pile of checks or form checks.  People sometimes hand write check, using the next check on the stack.  So I ask suggest the next check number for printing, which may have changed

                                   Jim...

                              • 12. Re: How to alter an active database
                                philmodjunk
                                     

                                          I would use the Design Function  GetNextSerialValue in FMP11 or 12, since is it possible that the last record was deleted.

                                     And why would that matter?

                                     With proper data integrity enforced, deleting the last record  and then resetting the next serial value to the value used in that deleted record shouldn't create any issues in almost all cases. And this has to be extracted from the Old copy not the new as this function will not return the correct value in the new copy. Doing that makes for a much more complex process so I wouldn't go there unless absolutely necessary.

                                     

                                          I have created my own counters that need updating...

                                     Well I have been thinking in terms of a properly implemented Primary key here and primary keys really shouldn't be something users are permitted to change, ever. But this a good point that sometimes the serial number field is not always something set up for use as a Primary Key and then some additional user input may be a good idea.

                                • 13. Re: How to alter an active database

                                       Hi Phil

                                       Thank you very much for the script break down. And also Jim, for you additions...

                                       All very healthy for discussion of what I would have thought to be a rather important DB step..

                                        

                                  • 14. Re: How to alter an active database
                                    philmodjunk

                                         Yep, Jim and I are volleying ideas back and forth in good fun. No hurt feelings and a good exchange all around--what I really, really like to see in this forum.

                                    1 2 Previous Next