Please help a noob!

[chrisandclaire]

hi guys,

Please go easy with the noob! Im creating a database which formulates recipes. I have created drop down lists for selecting the ingredients from and a value list containing ingredients, what I would like to happen is have a user select an ingredient and then have my database automatically show the cost per gramm of including it in the recipe

for example, when sodium bicarbonate is the ingredient selectedfrom the dropdown, I want the database to automatically then display the cost in a field next to it.

Any ideas as to how I can link the value list in such a way?

any and all help would be greatly appreciated.

thank you in advance,

Chris

[Ron Cates]

What is your table structure? Is the dropdown triggering a lookup in an ingredients table? Or is your dropdown just a static list of ingredients?

[chrisandclaire]

my dropdown is currently a static list of ingredients

[Ron Cates]

In order to do what you want and going forward be setup to do all the things you don't know you want yet your solution has to start with the proper table and relationship structure.

There have been many threads in this forum about recipe and ingredient tracking. You may want to take a look at these.

http://fmforums.com/...gredients-used/

http://fmforums.com/...ield-in-portal/

http://fmforums.com/...data-tunneling/

You can also do a find for recipe in the forum search to pull up others

[chrisandclaire]

hi,

thanks for that. I think im almost there.

I have 2 tables,

one: Recipes

two: Ingredients (which contains a cost per gram field)

I have my recipe layout set to display 10 drop down boxes from which you can pick ingredients from the ingredients table

I have set a relationship between (recipes) ingredient 01 = (Ingredients) Ingredient name

I then set up a field on my recipes layout which displays the "cost per gram" data from the record in my ingredients table

I can get it to work for dropdown one, eg I select cocoa butter from the list and it changes to display the correct cost but I can't for the life of me set up dropdowns 2-10 to do the same. Any ideas?

many thanks :)

[Ron Cates]

First you will need a third table that represents when one Ingredient is used in one Recipe. This is called a join table ( This would usually be named in a way to reflect the tables it joins, such as Recipe_Ingredients ). Think of it this way. One Recipe can have many ingredients, One Ingredient can be used in Many Recipes. With a join table you can assign one ingredient to one instance of it's use in a Recipe.

Secondly, each table should have it's own ID field set to auto enter unique serial number. These fields are used for relalationships rather than names and other data because data can change, serial numbers don't. Therefore your relationships will remain valid if you change the name of a Recipe or Ingredient.

You should have Recipes related to Receipes_Ingredients By the RecipeID and Ingredients related to Recipes_Ingredients by the Ingredient ID.

Note that the ID fields are Primary Keys which are auto enter serial numbers, the IngredientsID and RecipesID fields are Foriegn Keys which are not auto enter serials, they recieve the value of ID from the records they are related to. When you create records through a portal these fields are set automatically. Otherwise to relate a record in Recipes to a record in Recipes_Ingredients you would enter the ID from Recipes into the RecipesID field in Recipes_Ingredients. That matching value is what tells the database that it is realated.

So in a record in Recipes_Ingredients you will have the ID of the Recipe in the RecipeID field and the ID of the Ingredient in the IngredientID field.

See the table structure in the attached image.

Once you have the table structure in place, using a layout based on Recipes you would place a portal with allow record creation on, on that layout. The portal would be setup to show records from Recipes_Ingredients. A dropdown set to pull the ingredient ID but show the Ingredient name and only show second field will give you a dropdown where you select an Ingredient name but it populates the field with the Ingredient ID. The dropdown should be attached to the IngredientID field from Recipes_Ingredients placed in the portal.

Now in your Recipes_Ingredients table you would use fields with auto enter calculation set to be equal to the ingredient information ( such as price ) that you want to pull from the matching Ingredient record. Any info from the Ingredients record that will remain the same on all recipes can just be displayed in the portal by placing the field ( such as name ) from the Ingredients table in the portal on the Recipes layout.

[Ron Cates]

Here's a quick sample demonstrating the structure. You will need to unzip it to open.

Click in the first empty field of the portal to add an ingredient.

In the sample, notice that the ingredient name field in the portal comes directly from the Ingredients table, but the price field comes from the Recipes_Ingredients table. The price field in Recipes_Ingredients pulls the price from the price field in Ingredients allowing you to edit the price for that Recipe without changing the price in the original Ingredients record. This may not be needed but I was trying to demonstrate that any data from Ingredients that will be different from Recipe top Recipe should be pulled into Recipes_Ingredients.

Recipes.zip

[chrisandclaire]

Thank you so much for all your help - I've been working on it since you posted and I've finally just cracked it. Your help and the demonstration file made all the difference. Thank you for taking the time :)

[Ron Cates]

My pleasure. Good luck and welcome to the forum. There are some incredible people here that are always willing to help. :)