3 Replies Latest reply on Jul 17, 2012 3:01 PM by philmodjunk

    Very Specific Request

    MCRichmon

      Title

      Very Specific Request

      Post

      I'm trying to build a chemical product to customer/supplier database. I have my product database and my company database already formatted into one contact database. They are broken into groups as customer or supplier. However, I'd like to build unique relationships between the product and customers/suppliers dealing with those products. 

      For example, if I searched Isopropyl Alcohol, there would be one button saying "Customers" that would link all the customers that buy that product, and one button saying "Suppliers" that sell this product. Do I have to link the buttons to a specific different database? I would like to just pull from the already formatted mega detabase of my contacts. Is this possible? 

        • 1. Re: Very Specific Request
          philmodjunk

          Much depends on the current design of your database system and that design is not fully described in your initial post. I'm deliberately not going to use the term "database" in the rest of this post as that term can refer to different things depending on context.

          It would seem that you have two tables in your system: Contacts and Products. And it appears that a given product can be purchased by many customers and may also be provided by many supliers. I'd set up table occurrences in relationships like this:

          Suppliers-----<Supplier_Product>-----Products------<Customer_product>----Customers

          Suppliers and Customers would be two occurrences of the same contacts table.

          The relationship details would look like this:

          Suppliers::__pkContactID = Supplier_Product::_fkSupplierID
          Products::__pkProductID = Supplier_Product::_fkProductID

          Customers::__pkContactID = Customer_Product::_fkCustomerID
          Products::__pkProductID = Customer_Product::_fkProductID

          Fields that have a name starting with __pk are primary key fields that should be designed as auto-entered serial number fields.

          From this set of relationships, a layout that based on Products can have two buttons. One can Use Go To Related Records to bring up a found set of Customers and the other can use Go To Related Records to bring up a found set of suppliers for the current product.

          Note: a table occurrence is what we call any of the "boxes" found in Manage | Database | relationships. You can select an occurrence box by clicking it and then create a new occurrence of that table by clicking the duplicate button (two green plus signs). You can then double click the new table occurrence to open a dialog where you can change it's name like I show here in this example.

          • 2. Re: Very Specific Request
            MCRichmon

            PhilModJunk,

            I'm new to this job, and honestly NOT qualified for this program. I've had very little coding training, so I'm having to learn as I go. I believe I understand everything you've described. And you're correct, database was not the correct word. Layout is more appropriate. I understand how the relatioship works, but embarrassingly enough, I wouldn't know how to assign the above fields in the fields database so that I could create such relationships. Know what I mean?

            But total kudos for understanding my mangled question.  

            • 3. Re: Very Specific Request
              philmodjunk

              What I've described are two many to many relaitonships between customers and products on one hand and between suppliers and products on the other.

              The fields named with names starting with __p, should be defined as auto-entered serial numbers. Your data base will assign those values for you. For existing records in your database, you can use Replace field Contents to assign serial numbers and update the serial number settings (entry options) at the same time.

              The real heavy lifting here is setting up the two join tables: Supplier_Product and Customer_Product with the needed _fk values to link records correctly. I'll refer to just Supplier_Product for the rest of this post, but the same methods can be used for Customer_Product.

              If you place a portal to Supplier_Product on a Supplier layout and enable "allow creation of records via this relationship for Supplier_Productin the Supplier to Supplier_Productrelationship, you can link a given supplier to each product that they supply by putting Supplier_Product::_fkProductID in the portal row formatted as a drop down list that lists both ProductID's and a ProductDescription. By selecting a product in this drop down, you create a new record in Supplier_Product and link it to the current Supplier record and to the Product you selected in the drop down. You can repeat this for as many products as this supplier supplies for you. In similar manner, a portal to Supplier_Producton a Products layout can be used to list all suppliers that supply that product. If you prefer, you can use such a portal on the Product layout instead of the one I just described on the Supplier layout.

              Here's a demo file showing this basic set up in a many to many relationship: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7