2 Replies Latest reply on Sep 9, 2012 2:57 PM by brodlang

    DB Design Issue and Importing

    brodlang

      Title

      DB Design Issue and Importing

      Post

           FYI: I'm usong FMP Advanced. I will likely upgrade to the Server version once I have this design properly done and running.

           Summary:

           I'm in the door business. We sell doors to contractors for specific projects.  Each project needs submittal information, orders, etc.  There are several users, several tables and many forms and reports.  Any one user will only work on one project at a time (session) but more than one user could be working on the same project at the same time.  I understand about record locking, etc.

           I've been displaying and manipulating data by specific project info by using found sets. Is this the best way to handle this issue of working within "projects" from the beginning? Does the DB Manager offer a better way? Should I be importing projects for each session? It seems like this must be a common issue, but I haven't been able to figure out a simple solution. Most of our data is best displayed in lists. Portals are OK but don't always work for what I need.

           More:

           My DB design now has several tables that have all the records for all the projects. When users begin, they select the project from a list and that projects number becomes a global variable. For each layout they open, a script creates a found set to display just the records for that project.  This is becoming cumbersome as I need to create a new script for each different table or TO.  A bigger issue is importing records (other FM records from the same DB).  Some tables are populated by importing data from other tables. Some of that data is then changed for vendor order forms. (again using import techniques)  These are sometimes temporary and also involve deleting records - usually found sets.  Having to import all records in my DB just to display the ones I need for one project seems inelegant. Also, deleting found sets makes me nervous. There's probably a way to import just a found set, and I'll want to know how to do that, (or import with criteria eg "only project number 1234") but it feels like I'm taking the wrong approach from the start.  The DB with eventually have tens or hundreds of thousands of records in some of the tables.

           Thanks,

           BL

        • 1. Re: DB Design Issue and Importing
          philmodjunk

               Found sets are almost always your best option. Keep in mind that one user's found set is invisible to another. User 1 can pull up a found set for all records for project 1 on a layout and user 2 can pull up a found set of all records for project 2 on the same layout and each user will only see their foundset. Record Locking will only come into play if both users attempt to edit the same record at the same time.

               More

               

                    For each layout they open, a script creates a found set to display just the records for that project.  This is becoming cumbersome as I need to create a new script for each different table or TO.

               You might find that you can use the same script, but with different parameters used. Scripts can use steps such as Set Field By Name and GetField to work with table occurrence names that can change each time a script is executed with the occurrence name passed as a parameter to the script.

               

                    A bigger issue is importing records (other FM records from the same DB).

               It's impossible to tell from just the info you've posted here, but if you are importing data from table to table, this is something that is rarely necessary in FileMaker. Usually, importing records is needed for 3 common reasons 1) To move data to and from a backup copy of the file. 2) To copy a block of records that are then modified to become data distinct from the data originally imported. 3) To combine data from 2 or more tables temporarily for reporting purposes.

               Item 3 is much less common than 1 and 2 and can often be avoided with a effective table and relationship design of your database but is sometimes required to get disparate data into a single report.

               Note that when you import records from the table of a file that is open, the records imported are the current found set of the table occurrence specified by the import. Import records from a closed file will import all records from that table.

          • 2. Re: DB Design Issue and Importing
            brodlang

                 Thanks again, Phil. At least I know I'm going in the right direction with the found sets.  And you tidbit about the imports using the last found set of an open file was a big help too.  I'm sure you'll be hearing more from me. 

                 BL