Sorry, I forgot to mention I'm using FM 12.
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.
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.
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.
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.
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)