6 Replies Latest reply on May 6, 2012 8:12 PM by digital-carpentry

    I need help to set a relationship


      I am creating a Purchase Order and need help to set up a relationship.

      I have an Excel file of ‘Suppliers’.

      This Excel file has suppliers’ info plus supplied product information such as product name and pricing etc.

      I would like to select ‘New Purchase Order” and select the supplier from a drop down box and have all the information related to that supplier appear in their fields. (Address etc.)


      In this Purchase Order I have two Tables; Purchase Orders (k_ID_PO) and Line items (kf_ID_PO).

      I have set the relationship between these two.


      I just created another Table called ‘Suppliers’ where I can import the suppliers Excel file.


      This is where I stopped to ask for advice.



        • 1. Re: I need help to set a relationship

          Sorry, I forgot to mention I'm using FM 12.

          • 2. Re: I need help to set a relationship

            If I'm reading this right, you'll want to import the suppliers table as you've set it up, but are you trying to link suppliers to line items? In creating a PO, you'll probably want a link from the PO to the suppliers (pkSupplier_fkPO), and another to the line items. It looks like you're probably missing an inventory (items) table to bring items into your PO. Your inventory might have quantities as well.


            Is your suppliers really your inventory?


            Sorry it's more questions than answers. Just trying to get a better idea of the process you're trying to support and where your data resides.



            • 3. Re: I need help to set a relationship

              No, the Suppliers (Excel) is not inventory but it does hold the product name and price etc.

              This Excel database is not designed well and was not maintained well either.

              • 4. Re: I need help to set a relationship

                Ok. So maybe a supplier price list is a better description rather than inventory.


                You're going to need a table between the PO and Suppliers to track what ends up on the PO (line items). Is a PO always from a single supplier? You'll end up with something that looks like this:


                PO - Order - Supplier where there are two foreign keys is in the Order table, one to each adjoining table. The Order might be a portal on your PO layout. Selecting the Supplier then could limit the order or the line item, depending on how you set it up. Most of your reporting, invoicing, etc., would then be easily run from the Order table.





                • 5. Re: I need help to set a relationship



                  I would say your Excel sheet actually has two tables - even though it likely is not displayed that way. One is the suppier [name, address, etc.] the second would be the products the supplier *supplies*.


                  That would be a similar set up to the one you have for purchase order and purchase order line items.


                  You will need to assign a unique ID to each supplier and each supply from that supplier will need to have the supplier's ID as a foreign key.


                  Depending upon how the SS is set up and how familiar you are with Excel, it might be easier to assign the supplier ID while the data is still in the Exce SS. Make a copy of the SS to start the process. Insert a column for supplier ID. You'll need to either manual assign the ID or write a formula to do this.


                  Once the suppliers have an ID, import the supplier data [id, name, address, etc.] into your Supplier table. Then import the supply data [the columns that hold that information] into a Supply table being sure to ALSO import the supplier ID into a foreign key field in the supply table.


                  Link the two tables together and you have the basis for the selection you wanted in your question: data in the supplier table that show all of their information. There are several methods to display the list of supplies offered from the supplier. When you get to that point, let us know.





                  • 6. Re: I need help to set a relationship

                    You need 4 tables, Suppliers, Supplies, PO, and PO line items


                    You will need relationships between:

                    PO and PO line items (to keep these together)

                    Suppliers and Supplies (to keep these together)

                    PO line item to Suppliers (so when you select a Supplier from the value list, you can pull up a list of the supplies available)


                    Then set up 2 Value Lists, one showing all Suppliers and one showing related Supplies (based on Selected Supplier)