I have a seemingly simple need, however the solution continues to elude me and I'm hoping the community can help!
Simply put, I have a database containing Sales Orders and another containing Shipping Orders. I am using FM Pro 11.
I need to COPY portal data from a Products table that is displaying via a portal in a Sales Orders layout to a Shipping Order table without touching the original data. The product line item data record contains:
-Quantity of Product Ordered (3 fields are used, but 2 are blank depending upon whether the line item is ordered via BAG, SACK or BULK)
-Description of Each Product
-Weight of Each Product
-Tons Ordered of Each Product
A single product record contains these fields (and a few others):
Sacks, Bulk, Bags, Size, Lbs, Tons
I need to copy these fields to a Shipment Order table without touching the original Sales Order data because:
a) multiple shipments can be made for each order and must be tracked as to their status (open or closed)
b) each line item can be made up of multiple lots that need to be tracked (so each line item needs to link to potentially multiple lot numbers in another table) I created a concatenated field to do this using order number, line number and lot number that "should" work...
I have tried several scripts that I created plus some that I located online but none seem to loop and copy properly. I can't use a portal to the same Products table because changes in the portal change the original order data.
Essentially, I want to be able to enter in the order number into a portal to the Shipping Orders table and have the product line data from the related Products table copy over to it by way of a button/script. The items are related by both order number and line number.
Since it may take multiple shipments to fulfill an order, by entering the order number in the portal, all original order data is brought over and a running total is kept in the Shipping Order database as to what has already shipped and whether or not the order is complete. I can either duplicate the shipping order and modify it since it will be unique but related to the original, or go back to the original order and create a new shipment.
I have the running totals working, but cannot get more than one line item to come over. I'm pretty sure the problem is either in the relationship of the 2 tables or the copy script, but I have been unable to get it to work. I have an order field and a line number field, and created a concatenated field to link over to the shipment table.
I can provide more specifics on the table layouts if need be; my copy script is attached.
Thanks in advance for any insights...