3 Replies Latest reply on Mar 1, 2012 2:50 PM by philmodjunk

    Two tables or one table?



      Two tables or one table?


      I'm in the planning stages of a production workflow database.

      In our production workflow, we are working on two types of products so to speak.  Different users will work on Type A different users on Type B.  The workflow for each will be slightly different, but the informational fields will be the same or at least 95% the same.  I'll need to generate reports combining the workflow results of both Type A and Type B.

      What I'm wondering is if it will be easier/possible to have two separate tables for Type A and Type B and still be able to generate a report with results from both A and B or would it be easier/possible to have A and B in one table, and then have a layout automatically filter to display only results A or B.

      Any input would be greatly appreciated.  Thanks.

        • 1. Re: Two tables or one table?

          A unified table will make your reporting much easier. The 5% of your fields that are type specific can be left empty with types where they are not needed, or you can link in a related "detail" table in a one to one relationship.

          Keep in mind that you can set up separate layouts dedicated to working with Type A and Type B records for data entry purposes even when you use a unified table.

          • 2. Re: Two tables or one table?

            Thanks for your response.

            So in general terms, is there a way through scripting or some other means to provide a layout that will only show Type A or Type B and allowing searching within Type A or Type B.  Not all users will be program savvy, so I'm trying to come up with the easiest way possible for them to navigate the data. 

            Basically, what I'm thinking of, is providing some sort of welcome screen, and then having an option to work Type A production or Type B production.  Once they make that selection of A or B, it will direct them to another layout that will show the results of A or B and allow them to further refine the results by searching for specific values within Type A or B depending on the layout they have entered.

            Thanks again for the help.

            • 3. Re: Two tables or one table?

              You can use scripts that kick in automatically to limit the records visible on a given layout to just one type.

              Your button that takes the user to the layout for Type A can include a script that performs a find for only Type A records. A script trigger can perform a script each time the user leaves browse mode. Set this script to constrain the found set to only records of a given type and users can perform their own finds for records, but the results will always be limited to just the layout appropriate for the current layout.

              You can also set up auto-enter calculations that automatically label a record as Type A when the records is created on the type A layout and labeled as Type B when created on the Type B layout.

              It is also sometimes useful to use filtered portals to display a list of your records and then the portal filter can limit the records to a specific type.

              If you have FileMaker Advanced, you can also set up custom menus so that User actions that affect the found set of records perform scripts that you design that limit the records as appropriate.