1 2 Previous Next 18 Replies Latest reply on Feb 28, 2011 8:50 AM by philmodjunk

    recalling data two tables away / data tunneling question

    CollynHinchey

      Title

      recalling data two tables away / data tunneling question

      Post

      Hi:

      I have a database tracking information about products our architecture firm uses in projects. Right now I have a 'Products' table and a 'Manufacturers' table. I would like users to be able to select a manufacturer via a drop-down list from the products layout, with a button to 'Add a New Manufacturer' that would take users to the manufacturer layout. I created a join table (Product_Manufacturers) to avoid creation of a new records in the Manufacturers table every time someone selected a manufacturer from the products layout.

      This works fine, except that on the manufacturer layout I can't pull up a list of products carried by each manufacturer. In the join table, I have the name of the manufacturer (from the drop-down selection on the products layout), the primary key from the 'Products' table (which is a foreign key in the join table), and the primary key from the 'Product-Maufacturers' table. Photo attached.

      Can someone help me figure out how to get the foreign key from the Manufacturers table to auto-fill based on the fact that I have the name of a manufacturer in this table? My best guess was that once I can get primary keys from related records in the Manufacturers table to show up in the join table, I can figure out a way to pull up related product records from the manufacturers layout.

      Or if there's an easier way to pull up related products from the manufacturers layout, could someone let me know?

      Thanks so much-- I've been pretty good at McGuyvering FileMaker solutions up until now but this one has me stumped!

      db2.JPG

        • 1. Re: recalling data two tables away / data tunneling question
          philmodjunk

          Generally, a many to many relationship such as yours is set up like this:

          Products----<Product_Manufacturers>------Manufacturers

          Products::_pkProductID = Product_Manufacturers::_fkProductID
          Manufacturers::_pkManufacturerID = Product_Manufacturers::_fkManufacturers.

          "Allow creation of records via this relationship" is usually enabled for the join table side of both of these relationships. There is no need to have a Manufacturer name field such as ManufacturerList in the join table as this is redundant. Any time you need that name, you can refer to the name field that already exists in the Manufacturers table.

          So make sure your basic structure is set up in this fashion.

          Now to answer specifically: "Can someone help me figure out how to get the foreign key from the Manufacturers table to auto-fill based on the fact that I have the name of a manufacturer in this table?"

          You've got that a bit backward. What you need to do is select the manufacturer's ID and then the matching name can appear in your layout.  On your Products layout, place a portal to Product_Manufacturers and place the _fkManufacturerID field and the Manufacturer name field from the manufacturer table inside this portal's portal row. Format fkManufacturerID with a two column value list. Column one should list values from pkManufacturerID. Column 2 should list values from the corresponding name field in Manufacturers. Now, when you select a Manufacturer from this value list, the ID for that manufacturer is entered and the matching name appears in the field next to it when you exit the field.

          • 2. Re: recalling data two tables away / data tunneling question
            CollynHinchey

            Holy hell, you guys are all GENIUSES. I thought I had to set up that 'ManufacturerList' field because it was going to help me pull up the Manufacturer primary keys... but the portal worked perfectly!

            Any chance you can help me figure out how to pull up Product data from the Manufacturers layout now that I have the primary keys from both Product and Manufacturers in the join table (as _fkProductID and _fkManufacturersID)? This is the sort of thing that will take me forever to figure out on my own.

            • 3. Re: recalling data two tables away / data tunneling question
              philmodjunk

              Do the same basic process, but with everything reversed. Place a portal to the join table on your manufacturer's layout and place the _fkProductID in it plus any fields from Products that you want to see and edit.

              • 5. Re: recalling data two tables away / data tunneling question
                CollynHinchey

                That worked perfectly on the Manufacturers layout, but when I tried to copy the chart onto the Products layout the portal only lists the current product being viewed. Is there a reason why the portal wouldn't work on the Products layout?

                • 6. Re: recalling data two tables away / data tunneling question
                  philmodjunk

                  Why do you list the product in the portal? Since you are on the Products layout, only that product will be listed in the portal, but now you can list multiple manufacturers of that product in the portal.

                  You should replace that field with a field for the Manufacturer name.

                  • 7. Re: recalling data two tables away / data tunneling question
                    CollynHinchey

                    I'm listing the product in the portal as part of a list, products  available by the manufacturer. I have two "Other Products by..." portals on the Product layout: one for other products by the designer, which I constructed via a self-join  relationship in Products, and one for other products by the manufacturer. That's the one from the Product_Manufacturers join table that I'm having trouble with--  though as I said, this same portal works fine in the Manufacturers layout.

                    Let's say there are three products in our  database by Vitra. Based on  the relationship you helped me build, I can  access a list of those  three products from the Manufacturers layout. But  when I go to the  Product layout for one of those three products and try  to set up the  same portal displaying all products in our database by  Vitra, it only  brings up the current product. I understand why this is  happening, but  not how to fix it.

                    • 8. Re: recalling data two tables away / data tunneling question
                      philmodjunk

                      First, I understood from your original post, that a given product might have multiple manufacturers. Please confirm that this is the case or we have built more complexity into your system than is necessary (Don't need a join table for then).

                      • 9. Re: recalling data two tables away / data tunneling question
                        CollynHinchey

                        Oh dear... no, other way around, a given manufacturer might have multiple products. Your solution works fine (maybe until now) though... I just set up the original portal per your instructions on the Products layout with only one row and no arrows so users would only select one manufacturer.

                        • 10. Re: recalling data two tables away / data tunneling question
                          philmodjunk

                          Yes, I knew that one manufacturer can produce multiple products, but I gather that any given product has only one manufacturer. that means we can change your relationship graph from

                          Manufacturers----<Product_Manufacturers>------Products

                          to

                          Manufacturers------<Products

                          Manufacturers::::_pkManufacturerID = Products::_fkManufacturerID

                          Simpler, No?

                          Then we can add a new table occurrence of products, ProductsSameMfg to get:

                          Manufacturers-----<Products-----ProductsSameMfg

                          Products::_fkManufacturerID = ProductsSameMfg::_fkManufacturerID

                          A portal to ProductsSameMfg will list all products with the same manufacturer.

                          To create ProductsSameMfg, open Manage | Database | Relationships, select Products, then click the button with two green plus signs. This creates a new "box" on your relationship graph that refers to the same data source table as does Products.

                          • 11. Re: recalling data two tables away / data tunneling question
                            CollynHinchey

                            I don't think that's going to work... the reason for the join table in the first place was so that I can select a manufacturer from the Products layout without creating a new record in Manufacturers. When I had the relationship set up like you recommend, I had to turn on the 'create new record' option for Manufacturers to get the drop-down menu for Manufacturers to show up in the Products layout... and then FM would create a new record in Manufacturers each time I selected a manufacturer.

                            • 12. Re: recalling data two tables away / data tunneling question
                              philmodjunk

                              Not if the field is defined in Products. That would only happen if you've added the manufacturer ID field from Manufacturer to your products layout.

                              • 13. Re: recalling data two tables away / data tunneling question
                                CollynHinchey

                                Not if what field is defined in Products?

                                • 14. Re: recalling data two tables away / data tunneling question
                                  philmodjunk

                                  The field you set up with the drop down menu of manufacturers. If this field is a field defined in Products, there is nothing going on that could possibly create a new manufacturer record.

                                  1 2 Previous Next