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

    Ingredients list

    TomPurdum

      Title

      Ingredients list

      Post

           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...

            

           VANILLA ICE CREAM

           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
          philmodjunk

               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-----<Ingredients>------Inventory|Ingredient

               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