5 Replies Latest reply on Aug 28, 2013 4:18 PM by philmodjunk

    Adding new features and fields to a live database ...



      Adding new features and fields to a live database ...


           I'm wondering if I could get some pointers when it comes to working with a live database.  I currently have a version 2 running live of a database that I've been working on for a fair bit of time now.  At one point I took a copy version 1 offline and it became my sandbox for creating a version 2.  I was obviously able to test out new features with existing data as much as I'd like and after some time when everything was complete I saved out a cloned copy, imported the records we needed and the users were off with a new and shinny version.

           Since then I've been working on improving some scripts and because I used to separation model for version 2 it was been really easy but now I've hit somewhat of a road block.  I need to make some changes to the data file itself and I'm hesitant to pull another version offline because the import of data into version 2 certainly didn't go as smooth as I initially hoped.  Maybe it's because I'm more on the amateur side or is this sentiment shared among others?  I struggled with getting the serial numbers to cleanly line up.  There are so many of them that you have to think of that I often missed and had to rearrange a bunch from the back end once the users noticed things we're lining up properly.

           The two major changes I'm hoping to achieve are as follows:

           - Change over several fields that are currently set as calculation fields over to auto-enter fields to make things function more efficiently.  That's not as huge of a deal.  I thought of waiting till after hours, making of a copy of the most recent backup and start doing my thing.  I'm guessing for the initial change over it will only take me a few hours.  I'll be going slow and triple checking everything because I don't want to mess up, forget to index something and then lose my data in the change over.

           - Second is full on feature adding, new fields ... possibly new triggers, certainly layout changes.  Luckily for the most part the fields I would be adding wont be affecting the current setup so I was toying with the idea of working directly on the live database and accessing the new stuff from my separated version 2.1 that I'm currently working on but testing is very limited if required.

           I'm wondering if someone has a suggestion I'm just not thinking of to help me tinker with a live database?  Hoping this isn't too obscure of a conversation to have.

        • 1. Re: Adding new features and fields to a live database ...

               Well one reason that I name all my primary key fields starting with the characters: __pk (two underscores, not one) is so that sorted lists of the field names will list them first. This makes it easier to make sure that I update their next serial value settings without missing one.

               I'd use a script for importing the data that imports data from all tables and updates all serial number settings in one script. You can develop and test that script on backup copies of your database to make sure that it works and hasn't missed anything. Once you have that script, you can keep it handy as you can update and use it the next time you have to import data.

               That said, I have done exactly what you describe--waited till close of business and then made structural changes to tables, fields and relationships. Usually, I rehearse and test those changes on a back up copy to make sure that it works and I definitely keep a copy of the file pre-dating those changes handy just in case. There is, however, a small risk that a network glitch could damage your file if the changes are committed and the glitch occurs at just the wrong instant.

               Changes to layouts, script triggers, etc on the other hand, I've made to live databases and even done them while others are using the system. Though if you have a interface file, it really makes more since to make those changes to a copy of the interface file and just swap out the old copy for the new.

               But such changes, whether made after hours or during, have to be made with care. Any change you make to the design of your database will affect how your users interact with it. Small incremental changes--especially those that resolve issues that have been annoying your users generally can be made without issue, but more significant changes can trigger user complaints and questions if they are caught by surprise by the changes made. I make it a point to demo any significant design changes for my users before deploying them and letting them know exactly when to expect the change to take place. This also gives them a chance to provide feedback about those changes that I can use to make the changes work better for them.

               And as I think you know already, never, ever, make changes in Manage | Database while others are using the system. When the changes are committed by clicking OK to close the window, everyone else is locked out of making any changes to data in any of the tables. This can have catatrophic consequences if a script is modifying data at the instant that the tables lock everyone out. Even opening an auto-enter calculation and just looking at it, will lock everyone else out of that particular table until you close the specify calculation dialog.

          • 2. Re: Adding new features and fields to a live database ...

                 Thanks Phil, think you could show me a preview of what a script like that could look like?  How do you handle fields not properly lining up?  Version 1 and 2 saw major changes on every possible front.  I barely knew a thing about FileMaker when I started on version 1 so 2.0 I set out to try and make everything more efficient.  Of course the more I work the more I learn and now I see further room for improvements but my concern is if I add more fields then importing becomes tricky again .... fields don't line up properly.  I'm guessing you have a prompt for each new table to make sure the fields line up?

            • 3. Re: Adding new features and fields to a live database ...

                   No prompts in the script, but you do have to review the field matching dialogs before you run the script for real. If you specify the "matching names" option for th field mapping, existing fields whould automatcially align and should continue to align with future versions so long as you have not renamed a field--that's one of the reasons you need to check the mapping before running the script.

                   The Main script is just a long list of Import Records steps interspersed with code that checks and updates serial number field settings.

                   You also need a script in the original file that goes from layout to layout, this can be a loop, and does a show all records on each layout prior to the import to make sure that all data is imported.

              • 4. Re: Adding new features and fields to a live database ...

                     And if you have some field where you've changed their names, how do you exclude that able from the import to make sure everything is brought in correctly?


                • 5. Re: Adding new features and fields to a live database ...

                       As I said, you then need to review and update the field matching dialogs inside the import records script steps. You can start with the "matching names" option so that most of your fields match correctly and then drag to align those where you've had a field name change.

                       Of course this is simpler by a large factor if you don't change your field names in the first place. wink