10 Replies Latest reply on Jun 23, 2012 10:39 AM by Lemmtech

    Modifications to a live database


      We are a business that runs our system in filemaker 12. The business operated 24/7 and so there are no times when live data is not being inputted. Is there a way to make modifications to an 'offline' database and then upload this to the live one? I am keen to avoid having to import all of the existing data into the modified database as this involves dozens of tables and hundereds of fields.



        • 1. Re: Modifications to a live database

          The simple answer is "no", unfortunately.


          You should schedule a service window where you can close the system for maintenance. Perhaps there are other things that need to be done at the same time, like updating the server with the latest patches.


          Also, you can copy/paste a set of fields from your development copy into your production database. If you do this while the system is live, it may take a lot of time to commit the changes if you paste indexed calculated fields, during which your users will see the coffee cup.

          • 2. Re: Modifications to a live database

            I agree with johan.


            I think copy/paste method is bit better than re-developing on live database, but it still doesn't give you a proof. Tested database is offline one, but pasted live database is not.


            The best answer I believe is replace their live database to offline database. It can make data gap but you can make sync (and care about next serial...) script.

            • 3. Re: Modifications to a live database

              At the risk of pointing out the obvious, but other than table and field additions, the separation model allows offline development and testing, followed by a very small period of downtime while the user interface file is changed over.



              • 4. Re: Modifications to a live database

                I think that worldsync at syncdek.com can provide a solution.. or perhaps 360works mirrorsync

                • 5. Re: Modifications to a live database

                  Unfortunately the lack of true data separation is one of FM real short comings but this is their core identiity so I don't see it changing anytime soon. If it's a simple change that doesn't involce data manipulation like adding a new report or a new field I just do it on the live system and have for years without any real problems. If it's a more involved process I use the developer tool to create a copy of the production database with the files renamed do all the work and testing in it and then copy and paste over the changes.


                  FM could keep  the lack of data separation if they created a feature to sync the structure (layouts, scripts, fields, table etc..) beween two files. Boy if they had that (say just in FMPA) I think we would all kiss the FMI engineers!

                  • 6. Re: Modifications to a live database

                    Pls. describe true 3 layer separation with lets say MySQL. Here you will also do changes to the schema forsing you to close down when doing major opgrades to new versions.

                    There are a lot of misunderstandings regarding this issue.

                    And ... I do suggest that all UI and all scripting exept for some very few security/account features are separated from the data layer in FileMaker.

                    • 7. Re: Modifications to a live database

                      I am not a SQL person so I'm not sure I can provide the explanation you wish about other products but most other database systems and one that I am going be pursuing saves the data from your database in separate files apart from where all the sturucture of your solution lives. This is pretty common and doesn't force you to import and export data between two versions of the same solution. It not unlike any other application the creates files for the application to use when I new version of Excel comes it just opens the old spreadsheets and you get all the new features of the application.


                      FileMaker creates only one file for everything in the database unless you implement a data separation model which many people do but this is a work around and has no true support for from within the application. This has been and will continue to be a major limitation of the FM platform and makes it I think unsuitable for commeral product development but this is just my opinion.


                      As I said this approach of everything in one file would be fine if FM added a feature to syncronize all the elements of your solution between two files. Let's hope they add something like this in a future release.

                      • 8. Re: Modifications to a live database

                        The phrase 3 layer separation brings this thought to mind with MySQL:

                        Database Layer - MySQL

                        User Interface elements - Often HTML web pages outputted from programming logic

                        Programming Logic (php sql statements, etc.) which manipulate the database and the Interface elements.

                        • 9. Re: Modifications to a live database

                          The case is that some of the 3-layour idea stems from the fact that you are having these components

                          • the database tables
                          • a coding language
                          • a display language


                          In the case of MySQL this is very often

                          • MySQL
                          • PHP
                          • HTML


                          With FileMaker the equivalent for web publishing is:

                          • FileMaker Server
                          • PHP
                          • HTML


                          Nothing new. At least not untill you decide to use FileMaker calculation fields, relational diagramme, scripts ... to make the web development more efficient than with the bare bones solution.


                          FileMaker solution Model A - the uncompromising solution

                          With FileMaker the equivalent for a FileMaker soluton (not web) is:

                          .... a little bit more interesting. Because you have to make the choices.

                          You can definetely deside to have a 3 layer separation:

                          • One file with just tables (one script though for automatic creation of accounts)
                          • One file with just scripts and the needed RD's for supporting them. No tables. No user accessible displays.
                          • One file with display (layouts for the users) and the RD's to support those.


                          There are people who for some reason deside to do like this. And I have seen fine well functioning solutions build like this.

                          But I do not understand this.

                          Go back to the first example, for a part the 3 layer division is caused by having web interfaces and the different tools needed. So before just transferring strict models between different environments, you should think of the goal. The goal is to have a solution where data is separated from presentation and logic.


                          FileMkaer solution B - the FileMaker way?

                          This model does actually, potentially, have more files than the traditionalist separation model. But it is not a blind principle that is dictating this, but "intelligence"


                          The files/layers:

                          • the database file with the clean tables (you can have more than one file if it seems to be the clever way to do it)
                          • the interface file (you can have more than one, different modules).


                          In many cases this gives two files. One with tables and one with the layouts and the scripting.


                          This is FileMaker and here is my decision (you could stick to the principles, but I will not): Therefore we are having calculation fields in the data tables! And therefore we are having those few needed relational diagrammes needed to support the calculation fields.

                          But we would not put more than that into the data files. Full Stop.


                          In many solutions we will then have one User Interface file with the layouts, their relational diagrammes and the scripting.

                          = Two files.

                          And then ... we are using a login module for creating accounts, creating groups, installing and initializing plugins etc. etc.

                          = Three files.


                          But if we are also supporting IWP with this solution, we will definetely have a separate file for the IWP user interface. The same for PHP or if we are integrating with another system like Micosoft Dynamics, e-conomic, MS SQL, Oracle or, or, or, or.

                          And if we are having very different departments: Sales persons using iPads in the field, logistic people working in the warehouse with iPads, financial/cfo/ceo people with specific needs ... we will maybe have separate interface files for those.

                          For server scripting (scheduled scripts) we will always have a separate file (or more).



                          This is definetely separation. Data in one layer and intelligent separation into modules in the other layer.


                          And each part of the system does what it should. It does not grow into un-understandable huge solutions that nobody can work with.


                          Work smarter, not harder.


                          And this will ... getting back to the point ... ensure that you will not have to have so much downtime.

                          • 10. Re: Modifications to a live database



                            Thank you for your very thorough explanation of these concepts. I just wish FM had true native support for data separation rather then being forced into a derived version of this concept which in my mind is just another (very elaborate) work around for a very needed feature. I understand that FMI wants to keep the program's ease of use at a certain level for non-programmers and the so called "knowlege workers" and I am fine with that. I just wish the Advanced product had some truly advanced features like the syncing of the structure between two files. While still not data separation per se it would allow you to work on an offline development copy of your solution and then "sync up" the changes you made to the production copy. Perhaps this is just a "pipe dream" but at least it would allow FM to not have to change their file format as drastically as true data separation would probably require.


                            This limitation is the primary reason (although there are others) that I am going be learning 4D as my new "advanced" development environment.  It supports true data separation with ease and the table structure carries far less overhead then FileMaker. There is no doubt you can create some amazing solutions with FileMaker (I have done it) but in my opinion if your requirements really demand true data separation (not all solutions do) and other advanced architectural features you really need to take a hard look at what's the best tool for the job and if FileMaker is that tool or not.