So far I have created 15 fields of Ingredient01 ... Ingredient15
Instead of separate fields, define a related table with each ingredient listed in a different related record--all linked to a specific recipe record. This then allows you to link each record in this table to your ingredients table in order to access the correct cost value for that ingredient.
Start with these relationships:
Recipes::__pkRecipeID = Recipe_Ingredient::_fkRecipeID
Ingredients::__pkIngredientID = Recipe_Ingredient::_fkIngredientID
You can place a portal to Recipe_Ingredient on the Recipes layout to list and select a Ingredients record for each given Recipes record. Fields from Ingredients can be included in the Portal to show additional info about each selected Ingredients record and the _fkIngredientID field can be set up with a value list for selecting Ingredients records by their ID field.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
Thanks for the tips. That makes it more clear.
See attached screenshot.
I have setup the table :: field relationhips as you suggested. And I added the portal to Recipe_Items on the Recipe layout.
And I have the corresponding fields from Recipe_Items.
But when I click in the portal, nothing happens. How do I add a record? I set the Item as a drop down list using the value list I created to lookup values from __pkIngredientID.
I tried the Add Item script based on a FM template. But I couldn't get that to work either.
Any suggestions how to get the portal to add items to the list?
If you go to Manage | Database | Relationships, find and double click the line linking Recipes to Recipe_Items, you'll get a dialog where you can select "Allow creation of records via this relationship" for Recipe Items. (You don't need to select this option for Recipes.)
The Add line item button accomplishes the same thing in a script. You'd need to set up the script to create the new record and link it to the current record in Recipe Items. A script for this typically captures the current Recipe::__pkRecipeID value in a variable using set variable, changes layouts to a layout based on Recipe_Items, creates a new record, uses set field to copy the value from the variable to Recipe_Items::_fkRecipeID and then returns to the original layout. You probably do not need both methods.
Wonderful! That worked.
I had spent several hours on trying to resolve this issue myself.
Thank you for your help.