Instead of those separate fields for each ingredient, you need a separate related table with one record for each ingredient.
Ingredients would be the table with your unit pricing
A portal to Recipe_ingredient would be used to list ingredients and quantities required for a given recipe.
The match fields:
Recipe::__pkRecipeID = Recipe_Ingredient::_fkRecipeID
Ingredients::__pkIngredientID = Recipe_Ingredient::_fkIngredientID
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
And this then gives you a simple relationship to access those unit prices for each ingredient so that you can do cost calculations for each recipe with calculation fields defined in the Recipes table. A field, cCost, in Recipe_Ingredient can be defined as Quantity * Ingredients::UnitPrice. A field in recipe can then be defined as Sum ( Recipe_Ingredient::cCost ) to compute the cost figure for the recipe.
Thanks for the reply.
ok wow, so I have made the tables and the relationships. I am beginning to understand how a separate table will hold the ingredients listed as records rather than fields. But I have no idea how to equate fields across tables based on the input of one. like if Recipe ingredients :: Ingredient = "Almonds, whole" then lookup record for "Almonds, whole" in Price list :: items and display Price list :: ID. I am also assuming that "recipe ID" field or something like it should be included in the recipe ingredients table so that I can use one table for many recipes. won't this make my recipe ingredients table much larger than my price list table pretty quickly? Also, I am looking to use this set up as a means to a simple graphical interface for adding, updating and Printing standardized recipes with costing information. I can not visualize (as I am a visual learner) a method to input new records across both tables while keeping the new entries in the Recipe ingredients table associated with a recipe simultaneously. Could you provide a quick example of how it all fits together? or take a look at what I'm working with?
Could you provide a quick example of how it all fits together?
Take a look at the Invoices starter solution that comes with Filemaker 12. Think of your Recipe table as the invoices table in this starter solution with the portal to Invoice Data corresponding to a portal to Recipe_Ingredient and the Products table then corresponds to your ingredients table. The only real difference is the names used. You can even find examples of how the invoice cost is totaled up that is quite similar to what I have recommended for cost calculations on your recipe records.
You can also find method for printing out invoices that could be used to print your recipes.
I am also assuming that "recipe ID" field or something like it should be included in the recipe ingredients table
The Recipe_Ingredient table would be used for all of your recipes and a Recipe_ID field would be one of the fields in Recipe_Ingredient. If you take another look at my first post, you'll see that I have such a field described there: _fkRecipeID
won't this make my recipe ingredients table much larger than my price list table pretty quickly?
Most likely, but it's needed to store the data that you need for your recipes and why is this a problem?
Some of these links are useful - attempts to distill relational database design.
Some of the free open templates are good guidelines, some use naming conventions that are confusing
The youtube videos are also a grab bag.
A final note - searching on Google can be limited to one (or more) sites.
Google general search "ingredients filemaker" is limited to these forums by "ingredients site:forums.filemaker.com"
Thanks guys! looks like I've got some reading to do.
In the invoices solution in "Invoices :: CUSTOMER ID MATCH FIELD" there is the calculation "=$$CURRENT_CUSTOMER_ID" where is that defined?
Oh wait I got it. Scripts... Thanks again! pretty sure I got it from here.
It's for use on the iPhone and iPad layouts. If I remember correctly, an OnRecordLoad trigger on the customer layout performs a script to assign a value to that global variable. That allows you to have a layout where a new record on the Invoices layout is automatically linked to the customer from whose record you just came when you tapped a button to go to the invoices layout and create a new invoice.