5 Replies Latest reply on Nov 4, 2013 1:51 PM by philmodjunk

    Finished Product Database

    inker

      Title

      Finished Product Database

      Post

           Hello FMP Gurus!

           I am needing a database that will house information for a Finished Product.  The finished product uses one (of many) recipes, and a recipe uses many ingredients.  

      I have the Recipe >------< Ingredient relationship working fine via a join table.  It shows each line of the recipe with the correct amounts.

      However, I am stuck on the Finished Products table.  I need it to display all of the ingredients for a recipe too, but it will also need to hold information for each ingredient that is specific to that finished product.  Because many finished products can contain the same recipe, but they will vary on size (think different sizes of potato chip bags).  

           Here is a straw man version of my file in hopes this explains the situation a little better:

           https://dl.dropboxusercontent.com/u/7530915/simplified%7Erecipe%7Edatabase.fmp12

            

      Screen_Shot_2013-11-04_at_11.28.28_AM.png

        • 1. Re: Finished Product Database
          philmodjunk

               So you need a list of each ingredient and the ingredients/quantities needed to produce X finished products?

               Would this be for inventory management purposes? (We plan to produce X number of Finished Product size Y, that will require the following ingredients in the following amounts...)

          • 2. Re: Finished Product Database
            inker

                 Thank you for the response!

                  

                 

            So you need a list of each ingredient and the ingredients/quantities needed to produce X finished products?

            Yes.

                 

            Would this be for inventory management purposes? (We plan to produce X number of Finished Product size Y, that will require the following ingredients in the following amounts...)

            It might be called inventory management...I am just unsure of the semantics.  You are definitely describing what I need to accomplish.  :)

                  

            • 3. Re: Finished Product Database
              philmodjunk

                   In a production system, this actually takes place twice--the first time it's when scheduling/planning a production run and the data represents the predicted production of new inventory and the consumption of ingredients. The second time around, it's the actual reported production and consumption--where wastage, shortfalls and overruns have to be accounted for instead of the original "ideal" quantities specified in the recipe.

                   Typical production systems have 3 different subsystems: Specs--the recipe for producing one unit of product, Scheduling--the predicted production/consumption figures and Inventory Management--the actual changes produced by the production run.

                   So using your Potato Chips example, your Recipe might specify the ingredients and Quantities for a 1 ounce quantity of finished product. In the specifications subsystem, you might then have records for both a 16 ounce bag and a 32 ounce bag of potato chips. In the scheduling system, you might setup a 3 shift production run of 30,000 32 ounce bags of potato chips and the Inventory Management subsystem might then record the production of 32,000 32 ounce bags with actual amounts produced and consumed that will vary to some degree from that scheduled due to either more or less wastage than specified in the "recipe" and the 2,000 bag overrun.

                   For both "spec" and "scheduling" sub systems, I can think of two different approaches that can be used. One is to set up yet another Join table that is populated by a script that loops through the current recipe's list of ingredients and quantities to produce a set of join table records with adjusted quantities. The other is a more sophisticated relationship supported by a script and a global field to enable calculation field in the existing join table to access data in the current finished product record in order to show correctly adjusted quantities:

                   FinishedProduct----<Recipe-------<RecipeIngredient>----FinishedProduct|Selected

                   RecipeIngredient::gfkFinishedProductID = FinishedProduct|Selected::__pkFinishedProductID

                   FinishedProduct|Selected is another table occurrence of FinishedProduct.

                   gfkFinishedProductID is a number field with global storage.

                   You would keep it updated with the correct value by using the OnRecordLoad trigger to perform this script:

                   Set Field [RecipeIngredient::gfkFinishedProductID ; FinishedProduct::__pkFinishedProductID ]
                   Commit Records

                   This enables the following calculation field in RecipeIngredient to return the correct quantity:

                   Qty * FinishedProduct|Selected::SizeFactor

                   to adjust the quantities for each ingredient. In our Potato chip example, with a recipe for an ounce of potato chips, the sizefactor for a 16 oz. bag will be 16 and 32 for the 32 ounce size.

                   And I strongly recommend that you not use Name field as the match fields in your relationship like you have set up here. Consider the problems that might occur if you have to change the name of a product, recipe or ingredient--perhaps to correct a data entry error. That name change will break links to other related tables for the record where you change the name. Using auto-entered serial numbers instead of names as the match fields makes for a much safer system.

              • 4. Re: Finished Product Database
                inker

                     Wow, you are most helpful!  Thank you!

                     One more clarifying question for you:

                     

                ...I can think of two different approaches that can be used. One is to set up yet another Join table that is populated by a script that loops through the current recipe's list of ingredients and quantities to produce a set of join table records with adjusted quantities. The other is a more sophisticated relationship supported by a script and a global field to enable calculation field in the existing join table to access data in the current finished product record in order to show correctly adjusted quantities...

                I think if I have both options available to me, I would prefer to do the join table with a script-loop (blue text above) as it sounds much more simple.  Is one method preferred over the other?

                     If not, then any resources you can point me to for the join table script-loop are most appreciated.

                • 5. Re: Finished Product Database
                  philmodjunk

                       If you intend to log actual production and consumption for each production run, then I agree that the join table method is better as you can generate that set of records during the planning/scheduling phase and then use a different field to log the actual production and consumption. A calculation field can then compute the variance between amount that should have been used/produced and the actual figure.

                       But otherwise, I don't see much difference. It's a toss up between a more complex relationship vs. a more complex script.

                       And the script, now that I think it a bit further through, does not have to use a loop though it could. The basic outline of the script:

                         
                  1.           Capture the Finished Product ID in a variable: $FinProdID
                  2.      
                  3.           Capture SizeFactor in a variable $SizeFactor
                  4.      
                  5.           Capture planned qty to produce in a variable $QtyProduced
                  6.      
                  7.           Use Go To Related Records to pull up a found set of RecipeIngredients on a layout based on that table.
                  8.      
                  9.           Use Import Records to import this found set from RecipeIngredients into the new join table that we are discussing.
                  10.      
                  11.           Use Replace Field Contents to assign $FinProdID to the needed _fkFinProdID field of each of the newly imported records.
                  12.      
                  13.           Use Replace Field Contents with the calculation option to update the Quantity field with the needed Quantity figures. This calculation will multiple an imported qty value by the $SizeFactor and $QtyProduced variables.


                       A looping version of this script would have steps 1-4 the same, but then loops through the found set of recipeIngredient records using Set variable, set field and go to layout steps to move data from the current record in RecipeIngredients to a new record in the new join table instead of via a set of "batch" operations.