8 Replies Latest reply on Jul 19, 2011 2:54 PM by JantzSelk

    Inventory On Hand Calculation



      Inventory On Hand Calculation


      I'm having a little issue tracking inventory due to some amount complications. I have multiple ingredients that are displayed in a portal along with their amounts.

      This is a hypothetical example of what this portal looks like for a total quantity of 50 units. 

      Ingredient #1----> 10 units

      Ingredient #2----> 5 units

      Ingredient #3----> 15 units

      Ingredient #4----> Qs to 50 units

      The problem being that "ingredient #4" does not usually display the amount of 20 units, which would make the total of 50 units but instead says "Qs" meaning bring to final volume. Without going into too much unnecessary detail the reason for this "Qs" is to account for variation's in the other ingredient amounts. So i have the proper calculations and script triggers set to subtract the amount used from "inventoryOnHand" the problem comes when trying to subtract the "Qs" amount. Has anyone dealt with a situation like this or could point me in the right direction? Any help is appreciated!  

        • 1. Re: Inventory On Hand Calculation

          Reminds me of my recipe forbiscuits were I "add enough milk to 1 T of vinegar to make one cup"Wink

          I assume you can only have one ingredient with this designation for any specific item produced.

          All your ingredients on this list should have a common foreign key to appear in the same portal so you have at least this relationship:


          Products::ProductID = Ingredients::ProductID

          and I wouldn't be suprised to learn that Ingredients is a Bill Of Materials Join table between Products and your actual Ingredient records here:


          Either way, add an additional table occurrence of your portal's table linked by ProductID to produce this relationship:

          Ingredients|Bom::ProductID = SameProduct::ProductID

          To add an extra table occurrence of an existing table, select the table (Click Ingredients or BOM), then click the button with two green plus signs. You can then double click this new table occurrence to open up a dialog where you can rename it to a more descriptive name like I show in this example.

          Put your "QS to 50" in a different field so that you can leave the regular Units field empty.

          Then QSField - Sum ( SameProduct::Qty ) will compute the amount to be added.

          • 2. Re: Inventory On Hand Calculation

            Hey PhilModJunk, Yes you are correct there is a line items join table between the products and ingredients. So far i have created a second join table and related it to the product table. I start to get a little lost when i have to leave the amount field empty and create a new field for the QS amount... is there a way to leave the "QS to 50" in the amount field and do the calculation in the background? Your solution may be exactly what i need...but i might need a little more explanation Smile

            • 3. Re: Inventory On Hand Calculation

              If you leave it in the same field, Sum ( SameProduct::Qty ) will include the '50' in the sum and the calculation produces a negative number.

              You could keep in in one field with a more complicated relationship.

              This requires a serial ID field in your table to uniquely identify each record in it.

              BOM::ProductID = OtherIngredients::ProductID AND
              BOM::BOMID ≠ OtherIngredients::BOMID

              Then your calculation becomes:

              Qty - Sum (OtherIngredients::Qty)

              • 4. Re: Inventory On Hand Calculation

                Ok, i got both the relationship set up along with the calculation. However, how do i specify for Ingredients #1-3 to subtract their amounts from the 4th Ingredients QS amount... because as it stands right now the calculation results in zero because Product::Amount - Sum (BOM::Amount) will always result in zero. I also tried Product::QtyToPrepare (which is a field that shows the total amount to prepare "50 units")- Sum(BOM::Amount) but it results in a negative number because it subtracts the "Qs 50 units". 

                • 5. Re: Inventory On Hand Calculation

                  That's why I suggested NOT putting "QS to 50" in the same field as the other quantities. Please read my last post. You either put the value in a different field with the Qty field empty so that this value is not included in the Sum calculation or you modify the relationship like I showed in my last post so that this record is specifically excluded from the relationship.

                  • 6. Re: Inventory On Hand Calculation


                    "QS to 50" has to be included in the same field as the other values, for my purposes it wouldn't make sense otherwise. I did set up the relationship the way you outlined in your previous post but i still don't see how that exludes the ingredient with "QS to 50" amount. I think i understand what that relationship is supposed to do, but in my mind there has to be some sort of tag that targets the ingredient with "QS" in the amount...I must be way out in left field so i apologize for my lack of experience! 

                    • 7. Re: Inventory On Hand Calculation

                      BOM::ProductID = OtherIngredients::ProductID AND
                      BOM::BOMID ≠ OtherIngredients::BOMID

                      is the relationship. It requires a serial ID in your BOM table that uniquely identifies each record in it. The second pair of fields:


                      specifically excludes the current record due to the "not equals" operator used.

                      Note: BOM::Qty - Sum ( OtherIngredients::Qty ) must be evaluated from the context of the BOM (child)  table not the layout's (Parent) table.

                      Also, I have been assuming that you need this calculation in terms of unit quantities--the amount of material needed to produce 1 product.

                      • 8. Re: Inventory On Hand Calculation


                        Thanks for further explaining your solution! To make a long story short i was trying to work on "amounts" that had been previously committed, therefore the "not equals" operator would not work because the record was not current. I applied your solution to the correct tables and it is working like a charm!