1 Reply Latest reply on Oct 19, 2009 9:52 AM by philmodjunk

    Linking Supplier to Manufacturers



      Linking Supplier to Manufacturers


      Good morning!


      I'm looking to link suppliers to manufacturers in our product database.


      I've created a new table called supp_manu and created a serial number index called supp_manu_id.


      The other fields I've created are supp_id and manu_id intended to link the supplier to the manufacturer and a discount filed to indicate the discount from the supplier in relation to the manufacturers product range.


      I want to create a script/selection tool to allow the user to be able to be able to either link the supplier to the manufacturer from the manufacturer layout or the manufacturer to the supplier from the supplier layout without the user seeing the id index fields!


      Any help would be grreatly appreciated!

        • 1. Re: Linking Supplier to Manufacturers

          First, to check for understanding:


          You have three tables? Supplier, Manufacturer and a Join table, supp_manu?


          This would enable you to link many suppliers to a given manufacturer record and many manufacturers to a given supplier record--a classic many to many relationship.


          If so, your relationships should be:

          Supplier::SupplierID = Supp_Manu::SupplierID // enable the Delete and create records via this relationship options for Supp_Manu

          Manufacturer::ManufacturerID = Supp_Manu::ManufacturerID // enable the Delete and create records via this relationship options for Supp_Manu


          If you haven't used portals, look them up in filemaker's help for more info.


          Define two value lists:



          Specify Manufacturer::ManufacturerID as the first column. Manufacturer::ManufacturerName as the second column and hide the first column.



          Specify Supplier::SupplierID as the first column. Supplier::SupplierName as the second column and hide the first column.


          On a Supplier layout add a portal that refers to Supp_Manu.

          Place the Supp_Manu::ManufacturerID field in this portal and use Field/Control | Setup... to format it as a pop up menu with Manufacturers as its value list.


          Do the same on a Manufacturer layout, but use Supp_Manu::Supplier ID and the Suppliers value list.



          This will only work consistantly if Supplier and Manufacturer names are unique. I'd add a validation rule to both fields to enforce that.

          The pop-up menu--2 column value list with column 1 hidden is what hides the ID field values from the user.