1 2 Previous Next 18 Replies Latest reply on Apr 16, 2012 9:13 AM by philmodjunk

    I need some EXPERT opinion!



      I need some EXPERT opinion!


      Hi all!


      I have a general FM design question that I am hoping some of the experts here can give me their opinion on.

      I have a FM solution that I have been developing since 1999 with FM 4.0. It is currently being used with FM 11v3 on a Windows system.  It originally started in 1999 as being a small database for driver contacts and it has GROWN to be much more over the past 13 years.

      I work in the Transportation Department for the movie industry.  My "Trans" program as it has become know is used to run the Transportation Department for many TV shows and movies. I use it myself to run our Transportation Department on Criminal Minds.

      I have been reading much about the new FM 12 and I have played with the demo a bit and I like what I see in the design area of the new FM12. I think I would like to make the upgrade to FM 12 but my concern is that my 13 year old program has become bloated and sometimes unpredictable and the CONVERT from 11 to 12 seems to be where there is a lot of problems.

      So, I have decided to create Trans 2.0 with FM12 from the ground up but ever since FM added the ability to have multi tables in one DB I have never been clear on if and when that was the best way to build your solution.

      Here is the question...  Do I build it the same as before with MANY outside databases or use just one database and make MANY tables inside of the one DB?

      As it is now the "Trans" program has MANY databases built around the MAIN file called Trans.fp7 which is the original file I made in 1999 and it is used for the Driver Contact List and through Trans you can open the other databases. The other databases consist of...

      Budgets.fp7 1500+ records
      Timecards.fp7 1400+ records
      Purchase Orders.fp7 500+ records
      Petty Cash.fp7 20+ records
      Vendor List.fp7 1100+ records
      Picture Cars.fp7 300+ records
      Callsheet.fp7  10,000+ records

      And a few more smaller support databases.

      And the main Trans.fp7 has about 1400+ records.

      As you can imagine, I have hundreds of relationships, links, lookups etc etc between all these files.

      I do not use the stand alone apps from the developer addition, all the users of my program have Filemaker installed on their systems.

      At the end or a SEASON or when a movie is over, all the files get "RESET" and all the records are deleted with the exception of the Trans file Driver Database of 1400 records and growing and the Vendor Database at 1100 records and growing. Because on the next show you will still use the same drivers and vendors.

      I use my solution on a Windows machine but I do have others who use it on a Mac either native or they use parallels and run it in windows.

      Before I start this daunting task I would love to get some of your opinions on what has been you experience of building muilti file DB's or using the multi table option.

      Any input you have in this discussion it greatly appreciated!


      Robert Dulys




        • 1. Re: I need some EXPERT opinion!

          I have only been working with Filemaker Pro for the past year. I also made a solution for the film industry. Mine is one for a company that rents "Location Management" vehicles and equipment and that manages Location Managers.

          I don't know how everything was done in the beginning days of Filemaker but I came to filemaker with the multiple tables in one database structure. And that's the only way I can imagine it.

          It might be a daunting task for you to redesign your database. But you could just make one Database file with multiple tables and import some dummy data. You could try it out and test it and once you are happy you can just delete all the records and import all of your old data from your old files.

          Just my two cents.

          From my solution I can guarantuee you that within one database it's perfectly possible to get a lot of very varied stuff done. Here's a few of the things that can be done with my Database:

          - Make invoices
          - Manage bookings and check availability of equipment
          - Note tasks for other people
          - Manage and store vehicle damage. And create reports.
          - Manage contracts for end credit logo's
          - Lists for Location managers, Vehicles, Rental Equipment, Sales Equipment,
          - Manage and track expenses
          - Track maintenance of equipment
          - Personalized Shopping lists
          - ...

          With one Database file and multiple tables together with multiple table occurrences the sky is the limit.

          And all in one file that's easy to back up.

          • 2. Re: I need some EXPERT opinion!

            Thanks for the quick reply DaSaint.


            My question is not on weather FM can do it but rather what is OPTIMAL for the operation.  My driver call sheet as we come to the end on Season 7 of Criminal Minds has 10,000+ records and I am not sure of when a file is better to be out in it OWN DB to avoid a file getting too large and maybe see a slow down if it is ALL in one file.

            I will be importing my DATA from the driver database and the vendor datatbase when the new Trans 2.0 is finished.  I know I can save the data. When I said import to FM12 I should have said CONVERT!  I will not be converting any of my old FM11 files to FM12 but on the 2 files mentioned above I will be importing the data to FM12.

            Thanks again for the reply!

            • 3. Re: I need some EXPERT opinion!

              Also I should mention with my current Trans program it has been convenient to be able to address a problem in certain section and be able to upload the fix to a user.

              For instance, if someone found a glitch in the budget section I could fix it and just send them the budget.fp7 file to replace the budget.fp7 file they have.


              If I build an "all-in-one" solution I will be losing that ability...

              Just my own 2 cents on my own question :)

              Again, looking forward to other opinions!

              • 4. Re: I need some EXPERT opinion!

                It's an all or nothing move from 11 to 12 since neither will open the other's files.

                Best Advice (25 years): Go Slow and thoroughly test. DO NOT immediately replace all of your Filemaker Pro and Server copies with 12. Repeat. Do not upgrade everything now. Wait until v3 or later.

                It's OK to use 12 on a single computer or even a server set up for testing that is not your production solution. And that's the idea.

                I have been testing (retired) 12 Pro and Go and like others I am finding things that just don't work and feel 12 should still be called a beta. They will be fixed. There are slowness issues to be resolved.

                There are many changes and you might find it worth while to start from scratch and revise a lot of old thinking and slim down those files and scripts and the other stuff we drag along.

                To often the new comers get swept up in the fancy stuff and even take bad advice. My thinking is that if you can't get a solution working just using FIlemaker's defaults and avoid extreme complications, you should...

                This doesn't mean that you can't setup a 12 server and begin work and ask people to test, etc. What it means is don't try to replace and convert everything now, you'll be facing a lot of broken stuff and tons of work to do NOW...

                • 5. Re: I need some EXPERT opinion!

                  Thanks for the responce Jack,

                  I am still going to continue using my current version of my Trans file on FM11 - it is complete and I use it EVERYDAY!

                  I only use it on 1 host computer and one guest at my setup at work. No servers etc...


                  But now that I want to start to build it ALL OVER AGAIN from the ground up using the new FM12 which will take me MANY months and in that time I am sure FM will release bug fixes to address some of these NEW problems with FM12.

                  What I a trying to get a feel for is one of 2 choices...

                  1) Create this new version that same way I did 13 years ago... 1 main FM file is opened and from that file I open and close numerous other FM support database files all day long.


                  2) Use Multible Tables in FM and make 1 file with MANY tables inside the one file all self contained in 1 file.

                  Still trying to find out what is optimal for my situation...

                  • 6. Re: I need some EXPERT opinion!

                    Everything Jack says X 2.  Plus:

                    You can't simply send out the updated structure of the 'Budgets' file, as the users will have to import the existing data.  Therefore you will have to write an import routine to pull the old data into the new file.  (Not exactly A Biggie.)  So if you have to do that import routine for every individual file (in case that file gets updated) you have got exactly the same work as you would have if they were all tables in the one file.  So that suggests go with one file, anyway.

                    Another example is when you want to improve the security privileges you could potentially have to write that improvement into all the files, but if they are in one file they are much easier to keep co-ordinated.  Even entering a new user becomes One Task x the number of files.

                    Unless you have a driving reason to have separate files I would suggest one file, many tables.

                    (I have to declare a bias away from what is probably considered 'the norm': for very similar reasons as my first example I am not a big fan of the 'Filemaker Simulation Of A Data Separated Model'.  I am not against it, I just don't share the fervour for it that its converts do.  If you update the scripts or layouts, you can simply swop over the 'front end file' and everything works dandy.  But as soon as your improvement needs even one field added to the data file, then you have to have an import routine written.  And if you have to write it anyway, I just don't see where the long-term benefit is.)

                    Although I'm a bit confused by you saying that the only person who uses the file is you, but then you mention sending an update of the 'Budget' file to others...

                    • 7. Re: I need some EXPERT opinion!

                      Thanks for the response Sorbsbuster!


                      I am not sure where I said I am the only one using it, sorry for the confusion.

                      I have about 20 people that use it right now and what I have done in the past is when they show me a glitch like I said before in the bunget.fp7 file, I have them email me the file they have.  I fix the glitch on MY my program then I IMPORT their data into the repaired buget.fp7 and then I email it back to them.

                      I cannot rely on them doing the import.  Lucky for me the data we handle is not confidential in nature, but if it was I could see my method would be a problem.

                      And again I want to say I am continuing using my FM 11 Trans program... It is complete, it works etc...  I just want to start work on this other version and wait for the FM 12 bugs to work out.

                      As for users and passwords etc...  I have a master PW and there is only one user PW in his file, so 2 passwords are not a big problem.  They only have to be added to the files once.

                      Does anyone have experience with a single file solution that has 15000+ records in it?  Is there any performance hits or issues???

                      Thanks again for the reply!


                      • 8. Re: I need some EXPERT opinion!

                        I have  singe file solution where two tables exceed a million records each. In terms of record counts, there aren't a lot of differences between a single table to a file and multiple tables to a file. Instead, you have trade offs and you have to weigh the alternatives to make your own choices.

                        A one table to a file solution can make distributing a new file easier as you can replace just the files that were upgraded and import only the data in those files into the new. If you deploy a unified all tables in one file solution, you have to import all data from all tables for any updates made to that file. With small tables, this is not a big deal as you can use a script to manage the import process--importing even 15,000 records isn't all THAT big a deal time wise--though your mileage can vary depending on how many stored, indexed calculation fields exist in your file.

                        On the other hand, managing other parts of your solution become more complex and difficult to manage with single file tables. Each file needs it's own set of accounts and passwords if you use interal accounts to manage security. Adding, removing and changing accounts requires making identical changes in each file. This can be managed with scripts--but it's still an added complication to design and manage.

                        In addition designing and managing scripts that pass data from one file to another can't use variables and relationship graphs need to replicate relationships in multiple files in many cases.

                        One compromise approach you should consider is the data separation model where you put the data tables in one file and the layouts, scripts and other parts of your user interface in a second: Convert to Seperation Model

                        • 9. Re: I need some EXPERT opinion!

                          Let's consider the two file solution where one is a data file only (mostly) and the other file(s) are the interface/script files. Also note that you can create a separate interface only file to a file that is self contained.

                          The Interface Files are just that, no tables although they can have their own unique tables (isn't Filemaker fun!).

                          You create an interface file by additing a TO from any external file (this is indicated by an italic font).

                          So, you can limit the files that are in this file based on the account name and thus Fred, Tom and Sally can have their own interface file with limited tables and accounts.

                          Now your data file is going to remain solid with no script, layout, etc. changes. Upgrading this portions should be simple, if needed later.

                          Go the idea: one data file, many user files that access the data but contain none of their own. 

                          If you need to fix a file, the user file can be replaced without any importing, etc. These files act as your single files do when you create a 100 file solution with each file linked to others in that solution except they contain no data.

                          You can test the idea by duplicating your solution folder and working with the duplicated files.

                          Pick one and duplicate it. Name one duplicate Data and one GUI. In the data file delete all of your scripts. In the GUI file change all of your TOs so they point at the same table in your GUI file. You'll have to rename them if you changed the name of the TO. After you've repointed all of your tables, delete all of the tables where you create the tables and delete all layouts and scripts. Now the Data file has only tables. You can delete all of the layouts and create just table layouts where needed. Your GUI file has no tables just pointed TOs.

                          There will be some conflicts and things to fix in this case, rather than if you start from scratch. But overall the new GUI file should work just as your original file did.

                          Send the two files to a new customer. THey fill in their data. New update for your GUI file, just send that. No changes in the data file. No importing.

                          You don't have to separate the two, you can just create new user files...

                          Some lazy developers hate the idea because they have to open the second file to add a field or table... But when I heard one developer say how it took 12 hours to import the data every time he made a revision...

                          • 10. Re: I need some EXPERT opinion!

                            "I only use it on 1 host computer and one guest at my setup at work. No servers etc..." - I interpreted this comment to mean 'Only one instance of the files in operation'.

                            • 11. Re: I need some EXPERT opinion!

                              "Some lazy developers hate the idea because they have to open the second file to add a field or table..."  I don't usually think of myself as terribly lazy, Jack, but if the development step requires the addition of another field in the data file, I don't immediately see what alternative there is to adding the field and then importing all the data from the 'old' data file.  I accept that it is then a problem of importing from 1 'Table' and not every 'Table'.

                              I am the first to admit that the Overwhelming Evangelical Zeal that advocates for Data Separation are often possessed with makes me think that I am missing something big.  I am a bit slow on the uptake; maybe I'll get there eventually.

                              • 12. Re: I need some EXPERT opinion!

                                OK, now you've forced me to reveal a top secret method used by first class developers:

                                Add extra fields to your database so you don't have to stop a running file just to add a new field...

                                You can name them something like "Unamed New Field" and add half a dozen or so text fields, number fields, date fields, etc.

                                Note that these only take up one tab space when not used, something like that.

                                So, when you need an extra field, just use the next unused field.

                                You can keep track of them by making a table layout of these fields and seeing if there is data in them.

                                Overwhelming Evangelical Zeal...or did you mean Seal

                                OEZ the new caps for things like POTUS

                                • 13. Re: I need some EXPERT opinion!

                                  Nope; still not getting it.  If there are 20 incidents of the original data file out there, and the updated file has 3 fields changed from UnUsedField1, 2, and 3, to StartDate EndDate and Period, surely all the old data in those files has to be imported into the new data file?  Or at least when you run out of the half-a-dozen UnNamed fields, an import is necessary anyway?  So, at some stage in the files's life the import routine has to be written - and to me it seems that writing that import script once is the work of a few lines, copied-and-pasted for each table.  It only ever has to be updated when a new table is added, which is presumably less often that a new or updated field.

                                  I can usually tell that about this stage I have manufactured enough rope to hang myself, so I will quit while I'm only this far behind.

                                  • 14. Re: I need some EXPERT opinion!

                                    Have you considered using a central Filemaker Server and having each current customer having an account name and password? These 20 or 30 clients could share the one database and you could update it as you wish and they would all benefit from the changes immediately and this would eliminate your update and import problem.

                                    You can setup your own server or rent one online. I had a price of $150 a month for my own unshared computer and server software or as little as $20 a month if I shared the server with others, sometimes as many as 50 people.

                                    You could spend that $20 to test the concept and then invest the monthly fee if it works for you.

                                    Of course you would still have to do the import for each client if you combine them into one.

                                    The import routine you speak of is easy but tedious to write. When you do be sure to select the option to match all fields. It's been a while so I am unsure as to whether you still need to do a show all records in the file to be imported, etc.


                                    Filemaker must add the ability to add fields usiing a script at some time in the future.

                                    1 2 Previous Next