Looping through heterogenous records and updating related data
I have another, this time slightly complicated problem. In short, I need to update the amounts in stock based on incoming and outgoing orders.
What this means is, when an order changes its state, (from "ordered" to "shipped" to "delivered" etc), I need to loop through all the items, and add and/or subtract the amount in that order to or from fields in my Product table corresponding to that particular item's record.
I need 4 or 5 such scripts, as I have fields like "in stock", "ordered", "shipped". When an order (from us to the vendor) is created, the amounts of the items are added to "ordered" field, then as the order is shipped, they're subtracted from "ordered" and added to "shipped", and finally when the shipment arrives, the numbers are subtracted from "shipped" and added to "in stock". So it's a more-less sensible life cycle that'll allow us to track our orders. But I only need to know how to do it once, the other scripts will be analogous.
As I have recently described in another post, our database has products, and it has packages. Packages 1,2 and 3 can all contain various amounts of product A. (so it's a one to many relationship) The stock numbers are only kept in the Product table, not in the Package table.
(let's say we kept the stock amounts in the Package table. 3 pieces of package_1 arrive, which happen to be 12-packs of product_1. So I set the "amount" value of package_1 to 3. Then I sell 5 pieces of product_1. My "amount" for package_1 would have to change to 2.5, which is nonsense. Therefore when 3 pieces of package_1 arrive, I increase the "amount of product_1 by 36.)
However, from the point of view of our vendors, the packages are atomic items which they sell to us. So they have to appear on orders and invoices along with singular products. I solved this using the following data structure (pictured):
Order - a single order, pretty self explanatory
OrderItem - a join table, breaks down the many to many relationship (as one order can have many items but the same item can also be on many orders)
OrderLine - contains all the data needed for a single line of an order. It stores data from either Product or Package. It has fields like
IsPackage - Yes/No
ProductID - filled only in case of a Product
PackageID - filled only in case of a Package.
Then there's Product and Package, Package has a "ProductID" foregin key field, that identifies the product it consists of. So even if the OrderLine contains a Package, it's possible to get to the actual product.
The script I need should therefore be quite straightforward:
For every OrderLine record related to Order,
If OrderLine is Product, save its ProductID as $ID
Else if OrderLine is a Package, go to the related Product, save its ProductID as $ID
Update Product with ProductID = $ID, (add, subtract, whatever) using OrderLine::Amount
Go to next record.
Sounds easy, but I really don't know HOW to do this. I can traverse through the records using a Loop, commands like Go to Record[next], and so on.
But if I've created a Found Set with all the OrderLines belonging to the Order in question, and I need to do a search for a product related to my package, in the middle of a loop, won't it break my loop?
I've been trying to figure it out on my own, but so far I've had no luck. I'll be grateful for any insight.
Also, please don't suggest I change the data structure in order to facilitate what I've just described. A lot of thought went into this structure, and a lot of other functionality depends on it (too much to redesign it now, unless there's a REALLY good reason to do so.)