4 Replies Latest reply on Mar 29, 2010 11:22 AM by philmodjunk

    Switching sources when value hits 0

    erob

      Title

      Switching sources when value hits 0

      Post

      Hey guys,

       

      So we own inventory. When we don't have enough to cover a job we rent. I've set up a database with 3 tables: inventory, orders and lineitems. People can pick inventory and compile orders. It's all working fine but I guess we didn't think it through enough and instead of the solution I'm now aiming to create we did the following: each item we own has an entry in the database, and so do all items that are rentals, even if they are duplicates of something we already own.

       

      E.g. we own a broom and if we need more then we order from a Vendor. So there are 2 entries for brooms in the inventory DB, one as a broom that is owned by us, and a second broom that can be order from the Vendor.

       

      What I'd like to do is have a single entry and create a mechanism that automatically adds items to the order according to what we have in stock. So if we have 1 broom but need to then the lineitem record would show how many was available in stock and how many were rented from where.

       

      So my question is this: how do I set up a calculation that will only grab as much as is in stock and add the remaining part of the order to a field indicating rentals? I'm sure it's simple but I can't quite figure it out.

       

      Also, would I need to restructure my tables to make this work? I think I can leave everything as is but maybe I'm wrong.

       

      Thanks all, your help is always greatly appreciated.

      Best,

      Rob

        • 1. Re: Switching sources when value hits 0
          philmodjunk

          How have you set up your database to track inventory? There is more than one approach for this and I would need to know how you've set this up before I can recommend much here.

           

          "how do I set up a calculation that will only grab as much as is in stock and add the remaining part of the order to a field indicating rentals?"

          Something like this might work for you:

          Let's say the amount ordered on the line item is entered in a number field called Qty.

           

          You could set up two calculation fields:

          Amount Purchased : Min ( Qty ; OnHand )

          Amount to Rent : Qty - Amount Purchased

           

          That assumes you can get to an inventory field, OnHand that correctly computes the current amount of this item on hand and that depends on the design of your inventory system.

          • 2. Re: Switching sources when value hits 0
            erob

            Hey Phil,

             

            Thanks so much. I do have a field named Instock which tracks on hand inventory. It is on the inventory table and calculates WeOwn minus OutOnJobs.

             

            WeOwn is a plain number field that we update manually. OutOnJobs calculates the total of all items requested for jobs (requested field = Qty field and is in lineitems table).

             

            I'll do good ol' trial and error test runs of your suggestion. Thanks!

            • 3. Re: Switching sources when value hits 0
              erob

              With the current setup, if I have 3 items in stock and request 6 for a job, then the Amount To Rent field give me 9 as a result. The reason for this is because the Instock field is updated simultaneously and becomes -3 when the requested amount is entered into the Qty field.

               

              I'm guessing I'd need to have a different way of tracking instock inventory but I have no idea how. Or maybe I need a different Formula? Not sure...I don't knowhow the min (field) function works...but I'll try moving things around a bit. Please let me know if you can point me in the right direction.

               

              Thanks so much for your input. Greatly appreciated.

              • 4. Re: Switching sources when value hits 0
                philmodjunk

                Min stands for minimum and returns the lesser of the two values. I assumed OnHand would never return a value less than 0 since you can't have a negative inventory. (It tends to make your auditors rather testy :smileywink: )

                 

                It also should be a looked up value that copies the current inventory level (not including this current transaction) from your inventory table so that subsequent orders don't change the results of this order by changing your current inventory counts.