4 Replies Latest reply on May 22, 2012 6:40 PM by PeterThorton

    Looping through heterogenous records and updating related data

    PeterThorton

      Title

      Looping through heterogenous records and updating related data

      Post

      Hello,

      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.)

      Order_-_Product_relations_censored.png

        • 1. Re: Looping through heterogenous records and updating related data
          GuyStevens

          I'm having a hard time wrapping my head around your struture but here's a tought. I don't know if it's applicable to you or not, And I don't know exactly where to put it in your structure, but I wanted to throw it out there anyway.

          For stock tracking. Imagine you add a few fields to your line items table (in a traditional structure of a traditional invoice)
          Normally you would have: ProductId, Amount, LookupPrice, Total (Amount * Price)

          If you add three fields: "Ordered" "shipped" "delivered"

          And each of them would be a calculation field. The calculation being that if the status of this order (or line item) is "Ordered" then that field (the "ordered" field) get's the value of the field "Amount".

          This is the same for the other fields; If Status = "Shipped"  -  then Shipped gets the value "amount" otherwise it remains empty ("")...

          Then you create three sum fields and you can always see the total amounts of "ordered" "shipped" and " delivered" products you have.

          In your products layout you can create a portal to your line items table and show the records for that particular product.

          Then you can see per product how many hou have in order, how many you have ever delivered, and how many you have "shipping"

           

          I don't know if this is helping you or not, but I was staring at your relationship graph and not getting any wiser. So that's all I've got  :)

          • 2. Re: Looping through heterogenous records and updating related data
            philmodjunk

            Like DaSaint, I have more questions than suggestions.

            What is the purpose of having both "OrderLines" and "OrderItems" tables. This seems redundant here.

            "Package", "Boxed Set" or "Kit" type inventory items are usually linked in such that the Package table functions as a join table between two occurrences of Products:

            PackagedProducts-----<Package>------Products

            Products::ProductID = Package::PackageProductID
            PackagedProducts::ProductID = Package::ItemProductID

            Where PackagedProduct and Products are occurrences of the same data source table. I don't see that second occurrence of Products here and thus don't see how you would use the data in the Package table to update inventory levels. And it can be an interesting "business rules" decision whether you count assembled "packages" as separate inventory items or just inventory their component parts.

            • 3. Re: Looping through heterogenous records and updating related data
              PeterThorton

              I was hoping for some insight, but sadly we don't seem to be on the same page :-)

              Never mind guys, I'll tackle this one on my own.