5 Replies Latest reply on Jun 23, 2014 3:09 PM by philmodjunk




      Modules & Integral Database Development


           Hello all,

           I will apologize in advance if these questions are covered somewhere but I tried a number of different searches and never seemed to find what I hoped to find.

           I have a client that will use FileMaker Server and they've asked me to convert some older databases to V13.  In looking at those databases I see some big problems and I'm going to propose to them that they allow me to create a new solution for them and when possible import older data from the older files.

           I have two questions:

           1) They wish to end up with a number of "modules" like a PO system, Material History Record System, Sales Order System, Inventory, etc.  While these "modules" are somewhat specific they will relate to each other for things like pulling over customer data, updating inventory etc.  They wish to have a menu that once logged in will take a user to a specific module to perform some set of functions.

           It seems to me that the best performance would be to build these modules into one FileMaker database and use menus, scripts, and views to target specific functionality such as POs or Inventory.   Is that generally correct?   

           I mean I understand that FileMaker through scripting can in effect chain to other FileMaker files and even pull data from remote files, but doesn't it make more sense to build the solution in one file so the tables can easily relate?

           2) Integral development.  I can completely see this customer wanting certain modules of their system up and running before others.  So I may be asked to deploy a solution for them that implements the MHR module first.   While they are teasing and beginning to use that I will begin to build the inventory and later the PO etc. 

           My question is as a remote developer what is the best way to "add" new functionality to their existing FM file?  I should be able to get remote access but I'm wondering if their are ways to create scripts which can add new tables, setup new relations etc.  

           I can see that I will have an "updated" version of their FileMaker database where I will development new modules and at some point I need to transfer that new functionality into their existing file.

           How are developers handling this typically?

           Thanks so much in advance!


        • 1. Re: Modules & Integral Database Development

               1) You can set up such a system with multiple files with one or a few tables in each file or all the tables in one file. I'm no aware of a major difference in performance between the two basic approaches.

               2) When you supply a new copy of your database solution, you can supply it with scripts that use Import Records and Set Next Serial value to import all the data from their current copy into the new copy and update any auto-entered serial number fields at the same time. You can test and debug this script on a copy before delivering it and running the import to make sure that you've covered all necessary details.

               But to reduce the amount of data imports needed, I suggest a compromise between one table to a file and all tables in one file. Use a Convert to Seperation Model approach where all data tables are in a Data file and all layouts, scripts, etc are in an interface file. Any updates/bug fixes that only modify the interface file can be done quickly by swapping out the old copy for the new with no data imports needed.

          • 2. Re: Modules & Integral Database Development

                 Hi Phil,

                 Thank you once again for your expert advice.  I was aware of data separation model and have been considering it.  Still I see that even with this model in mind if I have a "data" file and an "interface" file so to speak if I add a new table I still need to open/modify the original copy of the "data" file and get that new table and relationships in place.

                 Of course I can do this remotely I'm sure and I can perform that action during down time to minimize any user interaction but in order to develop the new "modules" so to speak I may add a number of tables and relationships in my "local" copy of the data file.  Is it still the best idea to export from the main, move in the new copy of the data file then import?

                 As you mention I can certainly just substitute the interface file pretty easily.

                 These questions come from the fact that I've used FM for years but not in a modular approach like this.  I have also spent a large number of years in MySQL, SQL Server and PHP/C# and I'm used to having a pretty easy system to add and modify tables in place.   

                 Once again thanks!

            • 3. Re: Modules & Integral Database Development

                   You should not directly modify the copy of the file currently in use. You should take a copy of the file to your computer, use Filemaker Pro Advanced to modify it, save a clone (empty copy)  and then deploy the clone back to the server--using an import records script to import the data in the case where modifications are needed to the data file. (Much of the time, relationships need only be defined in the interface file, not the data file.)

                   Trying to change the structure of a database while it is in use is a bit like changing the sparkplugs of a car while driving it down the road. Not only can such changes lock all other users out of the database while a change is committed back to the file, a network glitch at the wrong time can damage the file. And just looking at certain options in Manage | Database | Fields can lock all users out of that field's table until you close some dialogs.

                   And the Data Separation method is intended to reduce the amount of data importing needed, it cannot elminate it totally as there will be times when the next update you want to deploy includes changes to the data file.

              • 4. Re: Modules & Integral Database Development

                     Thanks again Phil.  No I was not going to mod it while it was in use.  It would be during "down time" but I think I have a good idea of how to proceed now.   Your knowledge is amazing!

                     Thanks again.

                • 5. Re: Modules & Integral Database Development

                       There is a risk of damage even during "down time".