1 Reply Latest reply on Jan 6, 2014 7:28 AM by philmodjunk

    Help with an inventory database



      Help with an inventory database


           In my continued quest to learn Filemaker the old fashion way (just dive in head first!), I have got stuck again.  


           I am attempting to make a typical inventory database that consists of recipe ingredients for Dry Rubs.   I have a table and have successfully created the ability to take a receipt and enter the data off the receipt to calculate "inventory in".  Now the twist.  I have created another table to add recipes to the database, using a portal to add related records to each recipe at a given amount for each recipe.   I would like to create a button/link to push "make" recipe and have the ingredients calculated for a given size of recipe and those results be taken out of my inventory.  Eventually I would like to calculate the cost of each ingredient and therefore how much it would cost to make a given rub at a given size.   


           I am trying to make a portal in the "prepare_rub" that would use the data from the "rub_recipe" table and make and amount used field for calculation in the ingredient table to make an inventory.    I have reached my limited level of knowledge and can not make it work.  


           The specific question I have is that I want to prepare a rub (with a button).   When I do that I want it to import all the associated ingredients with that rub.  I do not have a problem with changes in price over time, because I have a weighted average of how much each ingredient costs.  I know that is going to change with time, and I do not have a problem with that.   I am more worried about keeping track of inventory then I am price.    Once I import all the associated ingredients, I want to be able to dictate how much rub I am going to make and then be able to calculate the exact quantity of each ingredient to make a "recipe" card.    I am stuck on the part of importing the specific ingredients.  My vision is to be able to select the rub, and the records will be imported based on the recipe.  I then type in how much I am going to make, and press calculate and it commits the record and calculates the amount removed from inventory.  


           My data base is uploaded here:  http://fmforums.com/forum/topic/90648-a-typical-inventory-database-with-a-twist/


           Below is a screen shot

           Below are my TO



        • 1. Re: Help with an inventory database

               So line items adds a new record each time that you purchase an ingredient?

               If so then I don't see any purpose to linking it to rub recipes 2 in your second table occurrence group.

               What you have is actually a classic inventory management problem as needed for a manufacturing system. In one part of your database, you need to specify your "unit recipe", in Another you would plan or "schedule" production. (Next Monday, shift 2 will manufacture 1000 units of item A on line 5...). And in another part, you'd log the actual production numbers ( On that Monday, shift 2 produced 998 units of item A on line 5.)

               It loos like you are working on the second part, the production planning/scheduling part. To get the amounts to prepare for each ingredient given a specified production volume, you need to be able to bring together all 3 amount fields from all three tables for each ingredient. There are several different ways to do that one way is to import the records from rub_recipes_ing into a new table where you use the other two amount fields to calculate new amounts for each ingredient and you link the records in this table to one record in prepare_rub so that each record in prepare_rub links to a different set. Another option is to set global fields to the other two amount fields and then a calculation field i rub_recipes_ing can use those to calculate the amount of each ingredient needed. This second method avoids the need for an added table an importing records, but you can't readily save the results for future reference, it functions more like a "calculator" to compute the quantities needed.