5 Replies Latest reply on May 17, 2012 11:50 AM by philmodjunk

    Any reason to link tables with virtually unlimited fields?  Record ID# on table?

    CorreenFrappaolo

      Title

      Any reason to link tables with virtually unlimited fields?  Record ID# on table?

      Post

      Good afternoon!

      I have only worked with Access. 

      I am in the process of creating databases for my employer using FileMaker Pro 12.

      Here is what I am trying to do (please advise):
           ProblemTable:  Customers will select from 1-100 check-boxes (Boolean fields, I know).
           AdviceTable:  These check-boxes will determine recommendations (etc.) for fixing their problem (about 10 fields).
           ProductTable:  These check-boxes will also determine 1-500 products (check-boxes "checked" by the owner of the company) suggesting products the customer should buy, which will take them to the product on our website.

      Is there any reason why I should have three tables?  I know in Access I would have had three linked tables, but only because of the max number of fields allowed in Access.

      If I do need three tables, please tell me:
           How do I link the record number between the tables?  It's not a field in Relationships!
           How do I insert the Problem/Product tables into the Advice table's form?

      With sincere gratitude,

      Correen
          

        • 1. Re: Any reason to link tables with virtually unlimited fields?  Record ID# on table?
          philmodjunk

          Three separate tables with one record for each checkbox still seems a more reasonable design, but much depends on how you need your database to work. Keep in mind that you can have just one field with multiple checkboxes (a checkbox group) assigned to that one field, but this may or may not be the most useful way to implement things for your solution.

          Portals (think Access SubForms) can be put on your layout to display a group of records with single value check boxes. You can also use buttons in place of actual check boxes to perform a script when the button is clicked with conditional formatting to change the appearance of each clicked button so that users can see which values have been selected.

          • 2. Re: Any reason to link tables with virtually unlimited fields?  Record ID# on table?
            CorreenFrappaolo

            PhilModJunk:  Thank you for your response.  
            1)  Will the Checkbox Group allow me to Problem>Advise<Products ?
            2)  If three tables, how do I link the record number between the tables?  It's not a field in Relationships.

            • 3. Re: Any reason to link tables with virtually unlimited fields?  Record ID# on table?
              philmodjunk

              1) I'd need to know more about what you are trying to do in more detail.

              2) To define a primary key in FileMaker, you define a number field in the table as an auto-entered serial number or, if using Filemaker 12, an  auto-entered calculation that uses Get (UUID ). You can then link this field to a number field (not defined as a serial number field) in the related table in order to establish a relationship.

              • 4. Re: Any reason to link tables with virtually unlimited fields?  Record ID# on table?
                CorreenFrappaolo

                PhilModJunk:  Thank you again for your response. 

                1)  Customers will be choosing from a list of 1-100 checkboxes.  Their selection (and they can select one or more) will then take them to the advise that our company will give them for their problem.  On that "solution to their problem" page (besides the advise) will be products that we want to suggest that the customer buys, but rather than hand-typing all of the individual products (we have near 500 of them) for each of the problems, my boss would like to just check off the ones that she wants the customers to see per problem.  I am trying to make this easy-data-entry on our end -- we just want it to work, not be super-fancy in the setup.

                2) I got as far as "Options for Field" and checking "Serial number" -- do I select "On creation" or "On commit" (I tried both, but nothing was assigned to the records that have already been entered).

                Thank you!!

                • 5. Re: Any reason to link tables with virtually unlimited fields?  Record ID# on table?
                  philmodjunk

                  2) On create is the most useful option in my opinion. You often end up with a portal to another table that won't work until you get a serial number in the field. Auto-enter options will not enter data into existing records--only new records or when a referenced field (if an auto-entered calcualtion) is modified--and then only within certain limitations. To update existing records, you can click/tab into the field and use Replace Field Contents with the serial number option to load the existing records with serial number values.

                  1) You appear to have two "many to many" relationships here. A selected "problem" could link to more than one "advice" item and a given advice item could link to more than one problem. Likewise, an Advice item could link to more than one recommended product and a recommended product could be linked to more than one advice item.

                  Is that correct?

                  If so, the standard "textbook" relationships (I'd use these in Access much like I would in FileMaker) would be:

                  Problems---<Problem_Advice>-------Advice-------<Advised_Product>-------Products

                  Problems::__pk_ProblemID = Problem_Advice::_fk_ProblemID
                  Advice::__pk_AdviceID = Problem_Advice::_fk_AdviceID

                  Advice::__pk_AdviceID = Advised_Product::_fk_AdviceID
                  Products::__ProductID = Advised_Product::_fk_ProductID

                  Given the number of items in your check list, this is likely to be your best option, but in FileMaker, it is also possible to use a text field of return spearated values to match to multiple records and a checkbox group formatted field can be set up to edit such a field.