I am looking for some advice on the best way to import a file containing child elements to a related parent table.
I have an application to generate a purchase order used to buy rolls of paper. The purchase order is a unique id. Each roll of paper has a unique roll number. Each of the rolls of paper will be an inventory item that I want to associate with the purchase order number, price, purchase date and supplier etc. I can receive many rolls of paper for each purchase order. The purchase order contains the number of pounds desired, but the supplier can provide many rolls to get as close to the number of pounds desired as possible. For example, I need 40,000 pounds, the supplier might send 20 rolls of paper that weigh roughly 1,800 – 2,000 pounds each. Each purchase order can include multiple types of paper meaning I can have two or three types of paper per purchase order – for example, 20,000 pounds of type 1, 30,000 pounds of type 2 and 40,000 pounds of type 3. This program is in its infancy stage and I can change it so that each purchase order can only be for 1 type of paper if that is easier (which I might do anyway).
I made a “receiving form” where I can manually input the roll number, the weight of each roll and the diameter of each roll. This works. Image is below. In the image I put arrows indicating the data required for a manual input. The roll information is a portal to the purchase order line items table.
Some suppliers – not all – can provide us an EDI file that contains the information I need to manually input. I can convert the EDI to a csv file. I know I can import the csv file into the purchase order line items table directly. The csv file would contain many roll numbers, weight and diameter – each roll number would be a record.
What is the best way that I can import the csv file so that each roll is associated with the purchase order number, supplier etc?
The relationship graph is below.
Thank you for any help.