3 Replies Latest reply on May 18, 2012 10:39 AM by philmodjunk

    When to use External FMP files?



      When to use External FMP files?


      I'm putting together a database for investment portfolio reporting and management.  Data is downloaded from a broker-dealer and stored in a series of tables (e.g. transactions, positions, securities, etc.) all of which are in a single FMPA 12 file.  In the event of a disaster, reading the data from the source files back into the FMP solution can be done very quickly.  (Thanks to help I've received here, my test import last night read 5,000 files and over 500,000 records into FMP in less than two minutes.  Daily imports are virtually instantaneous.)

      In terms of best practices, should the solution be kept separate from the data tables?  In other words, in addition to using the "golden master" advice provided in file_management_best_practices_en.pdf should data be kept in separate FMP files rather than in a single file?

      If it depends on size or usage, what factors go into making a decision to separate data from the solution?

        • 1. Re: When to use External FMP files?

          There's an approach called the "data separation model". It uses two files: File1 contains the interface (layouts, scripts, value lists) and File2 stores the actual data tables. The table occurrences in File1 each have an external data source reference to a table in file2.

          The assumption behind this design is that most updates will require changes to file1--the interface file and not to file2--the data file. New versions of the interface file can simply be put in place of the older version and no importing of data is required.

          This approach simplifies deploying an updated copy of your solution but requires a more complext design. If you have just one user for your solution and the time needed to import records is reasonable, you probably won't need this approach.

          If you have multiple users and/or data that takes hours to import, then the data separation method makes sense to use.

          For how to convert a single file solution into a two file interface and data system, see this thread:  Convert to Seperation Model

          • 2. Re: When to use External FMP files?

            Is there any problem or special considerations if I wish to have the data reside in a SQL Server Database?  Can Filemaker be used as a front-end for importing data to and reporting on data in SQL Server databases?

            • 3. Re: When to use External FMP files?

              It's a more complex setup, but essentially, that's another form of the data separation model, but now your data file is replaced by the SQL Server Database. The challenge is in setting up a working ODBC connection and getting any needed SQL queries to work for you.