Transfering invoice data from one table to multiple product orders in a different table
I have a table of Invoices related to a table of invoice Line Items. Some of these Line Items will require me to place an order (or, create another record in a table named "Orders" ) with various vendors.
An Invoice may contain many Orders, depending on what Line Items there are. One single Line Item may constitute a new Order - IE - the customer bought X so we need to order X.
To complicate matters, a new Order may need to relate to multiple Line Items to have enough information to place an order (create a new record in our Orders table). IE - the customer bought X (one line item) with customization Y and Z (two additional line items).
I need to write a script that will know how analyze each Line Item and create a new record in my Orders table. I can get close by using "If" statements to check the item category and proceed based off of that information. If there are multiple Orders in one Invoice, I also have a "Order_Number" keyfield in my Line Items table to let my script identify how many line items are in each order. IE -
Line Item 1 : Product X : Order 1
Line Item 2 : Accessory Y : Order 1
Line Item 3 : Product Z : Order 2
The trouble I am running into is that I don't know how to Loop a script to make variables off of each order. It seems that I would need some sort of dynamic variable that can have the same prefix for each item, but have a different suffix for each individual order. IE - the script will create a dynamic variable like "[Product Name]_Order 1", "[Product Name]_Accessory_1_Order_1" , "[Product Name]_Order 2". I then would have to use those variables to past information in separate records for "Order 1" and "Order 2".
I may be completely off base though. If anyone has a good idea I would be very appreciative.