1 Reply Latest reply on Jun 11, 2014 7:12 AM by philmodjunk

    Ingredients list



      Ingredients list


           Its been awhile since I have to wrangle databases, and am trying to learn FMP.  I have done quite a bit of SQL and access, but for some reason am struggling with this.  What I have is an ice cream manufacturing company.  What I am trying to do is keep a central list of all item and their ingredients in one table and then be able to "build" my flavors of ice cream from that list.  I need to be able to update the main list when something changes, then have all flavors that use that ingredient update.  Ultimately what I need is to output a report that contains each flavor and an "ingredient label" for said flavor.  What is the structure that I need to set up?  I am not tracking production or product on hand, just ingredients.  

           An example....   our vanilla ice cream uses a 19% butterfat base, which has say 5 ingredients, then we add vanilla extract, which has three ingredients and then it has vanilla specks.  So the final label would need to look something like this...



           Cream, milk, non-fat milk powder, ingredient, ingredient, VANILLA EXTRACT(ingredient, ingredient, ingredient), vanilla specks.


           Thats a pretty simple example, some flavors have up to 10 main ingredients.


           Any help would be awesome!

        • 1. Re: Ingredients list

               What you are describing is essentially a Recipe database where you list your products and their ingredients in the same table. This is also the same structure used in Manufacturing to set up a BOM (Bill of Materials) table. There are a number of threads in this forum that discuss this topic.


               Inventory::__pkInventoryID = Ingredients::_fkInventoryID
               Inventory|Ingredients::__pkInventoryID = Ingredients::_fkIngredientID

               Where Inventory and Inventory|Ingredients are two Tutorial: What are Table Occurrences? with the same data source table.

               A portal to Ingredients can then be placed on your Inventory layout and used list the ingredients for each IceCream record.

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained