9 Replies Latest reply on Nov 13, 2012 1:45 PM by philmodjunk

    Creating a report based on selected checkboxes



      Creating a report based on selected checkboxes


           I'm extremely new to the advanced features of Filemaker, so I'm not sure how to ask this question.

           What I have is a list of specifications based on materials... if a project uses aluminum, then the report needs to include aluminum specifications. I would like to have a checkbox list of all possible materials and then an automated report that includes information for ONLY the checked items.

           Any suggestions for how to set this up?

        • 1. Re: Creating a report based on selected checkboxes

               There are multiple ways to do this. A more complete description of your database structure would help us narrow down that list of possible approaches. Do you have a table of materials and a table of specification records such as the aluminum specs that you mention? (I used to manage a specifications database--both purchased materials and manufacturing specs, so this is a familiar subject.)

          • 2. Re: Creating a report based on selected checkboxes

                 Right now I don't have anything set up. In the past we've done this by hand, starting with a master Word document, deleting the material sections that don't apply, and renumbering. So we have the information, but no database.

                 Could this be built to also auto-number the sections?


            • 3. Re: Creating a report based on selected checkboxes

                   I suggest that you back up and work out a basic approach for what you need to do to manage your specifications from a database.

                   These specs could be purchase specs---specs that tell your vendors what you expect from them when you purchase their material and which your QA department references for doing QA tests on incoming material.

                   They can be manufacturing specs--specs that tell your workers how to manufacture a product and again tell your QA staff what standards to use during QA monitoring of the manufacturing process.

                   You may or may not have a BOM--a bill of materials with specs for each item in the BOM. This might even be true for some purchased items.

                   Your specifications can list numbers, text, drawings and art work and can be set up with one record for each material or each item in your BOM or you can have specs that are more "modular" where the specs for a given item may consist of a list of specifications records.

                   And there are a variety of ways to "auto-number" things, but the basic design of your system in terms of tables and relationships will determine which approach will work best for that.

              • 4. Re: Creating a report based on selected checkboxes

                     Thanks for the responses,

                     What we have is manufacturing specs only. So it will say "Aluminum" and then have subsections 1-5 or whatever. Then "Brass" and subsections 1-3. The subsections always stay together. The choice is only whether the project uses aluminum and brass.

                     So would I make a table where the record is "Aluminum" and then a message field with all the subsections? Some of these may be two pages long and would have to page break on the report.

                     I'm used to using a straightforward database without relationships, so I'm not even sure where to start with formating. It's hard researching new things when you don't even know the terms to use.

                • 5. Re: Creating a report based on selected checkboxes

                       In my book, a 'straight forward database without relationships' is a contradictory statement. Relationships are key to good database design.

                       How much will a BOM figure in this spec? Does it make sense to link the specs to each item in the BOM such that all the specs associated with the BOM provide the specs for the manufactured item. (Some items such as drawings, etc would then be added to the material specs to complete the specifications.)

                       Multiple page material specs shouldn't be a problem. You will need, however, to consider the format you want to use for these specs--whether PDF's of some or all of the material spec might be useful.

                  • 6. Re: Creating a report based on selected checkboxes

                         No BOM, as I understand it. Right now the format is text-only. I guess a PDF might be possible in the future, but we haven't so far.

                         We're designers trying to give guidelines to the fabricators, but we don't engineer, so we're not that specific.

                    • 7. Re: Creating a report based on selected checkboxes

                           Then it comes down to how you choose to structure the specs.

                           You can enter or import text into text fields and numbers into number fields. Images such as a JPG file can be inserted into a container field.

                           But what you will have to decide is into how many modules you can divide your specs and then mix and match to create a specification document.

                           Your relationships might look like this in very general terms:


                           Products::__pkProductID = Product_Spec::_fkProductID
                           Specifications::__pkSpecID = Product_Spec::_fkSpecID

                           See: Common Forum Relationship and Field Notations Explained for an explanation of the notation that I am using.(Don't have to read entire thread, just the first post.)

                           This allows you to set up a specifications record and link it to many different products such as an Aluminum spec that links to many different products that contain that aluminum alloy. At the same time, A given product can link to more than one specifications record--such as a product made of Brass, Stainless Steel and Aluminum linking to 3 specifications records, one for each metal.

                           This is called a "many to many" relationship---not the simplest thing to work with if you are new to relational database design, but it matches what I understand you to need for your specifications.


                      • 8. Re: Creating a report based on selected checkboxes

                             I wonder if my needs are simpler than that. Right now it's basically "Aluminum, yes or no?" That's why I was thinking checkboxes... if the aluminum box is checked, the aluminum spec is included.

                             Does it still need "many to many" to accomplish that? Sorry for my beginner questions.

                        • 9. Re: Creating a report based on selected checkboxes

                               The checkboxes are actually a secondary design issue here. Such a format can be used whether or not you employ a many to many relationship. There's just not much point going into detail on that issue until you have a solid data model for your database--in other words, a set of tables and relationships that can do the job efficiently and without unecessary duplication of data. The many to many set up I am suggesting makes that happen.

                               Here's a many to many demo file. If you check out the "check boxes" layout, you'll see that what you want is possible with a many to many relationship: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7