Do you plan to use the BOM table that I recommended? If so, I can tell you how to import this data.
yes, my plan is to use the BOM table to connect Finished Products table to Inventory Parts table :)
Then this data needs to appear in two tables, Parts and the BOM. If you have a "master list" file of all parts, you can import that file into parts. If you do not, let me know and I'll show you how to use these individual excel files to set up the records in Parts without getting any duplications.
But to import into the BOM table, here's the steps for a manual import. A scripted import will be very similar:
Go to a layout based on the BOM table.
Select Import Records | File from the File Menu
Use the dialog that opens to find and select the Excel file from which you want to import this data.
Keep the default settings to add new records, but only map the column with the itemID to an ItemID field in the BOM table. If there is a qty, column in the excel file map this to a Qty filed in the BOM. No other data is likely to be needed for this import unless you have some sort of cost figures included that are specific to this BOM (as opposed to a cost figure that is the same for a given part no matter where it is used. That cost figure should be imported into the parts table.) Any name or description field need not be imported here as that data will be drawn from your parts table.
Import your records.
Then, if the ProductID field in the BOM is not present on your layout, add it to your layout (or to your table view).
Click into this field on one of the records just imported and type in the Product ID.
Then use Replace Field Contents from the Records menu to copy this value to all the parts records.
So 1st I need to import the finished product table and the parts inventory table, adding in the pk for both tables so that when I set up the BOM table, it will have the fk for both right?
That raises a whole new issue and is the right question to ask here. "ItemID" is, apparently, data being imported from your excel file. Yet the __pk and _fk values in a database are normally internally generated values. The trick is to use ItemID in a special, temporary use relationship to pull in the needed __pk value from Parts. The __pk value from Products isn't as big a deal as you only need to manually enter it once and then use Replace Field Contents to assign it to the other newly imported BOM records.
Here's how to do it for __pkPartID
Make a new table occurrence of products and link it in like this:
BOM::ItemID = Parts|ItemID::ItemID
with the correct data imported into Parts (enable auto-enter options so that the __pkPartID will generate a serial number value in each imported record), you can import your records and use:
Replace Field Contents on the BOM::_fkPartID field to copy over the value of Parts|ItemID::__pkPartID into each newly imported BOM record. Once you have all your data imported, you can delete the BOM::ItemID field and also the Parts|ItemID table occurrence.
Just had a major Brain Short Circuit with that last post. I've corrected the error here in the forum. but if you are reading these responses as email, you won't see the corrected copy until you pull this thread up in the forum.