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!