relationship/db layout question. New to FM.

[iamnotmad]

Hello, I am new to FM, and I have a question about a db I am trying to create. Should be pretty simple - but it's not coming to me. Here it is:

I am creating a recipe DB, and so far aI want to use 3 tables. 1) a recipe table (main browse/recipe entry form here). 2) an ingredients table. And 3) an Amounts table. Here is how 'd like it to work: Each recipe should have several ingredients and each ingredient will have an amount that is unique to that (recipe and ingredient). I hope that makes sense. I've tried several things unsuccessfully.

Basically the ingredients will list ingredients that all recipes will use - only once of course. The Amounts table will be the largest as it will have a record each ingredient of every recipe.

I think I pretty much got this done however, I want to be able to add records from the main recipes form, but I could not add records to the ingredients table from there. What I was doing was basically using the Amounts table as a join table for the many-to-many between the Recipes and ingredients. But I could not add ingredients from the recipes form that way (since the "shared" fields in Amounts were calculated fields to get values from Ingredients).

Let me know if I need to clarify, any help is appreciated.

[falkaholic]

What I'd do is structure it differently. Have what is called 'lineitems' in FM (aka one to many).

Have a recipe database with a one to many to ingredients lineitems, and if you need to track info on ingredients, you should make a database for them as well.

In the lineItems you have the ingredient (or the ID for the ingredients database) and have an amount field.

Then in the recipe database you can use a portal to list the ingredients and amounts in the same list quite easily.

(Note: its monday morning right now, could have missed something)

HTH

[iamnotmad]

Thanks for the response. But your suggestion would produce an ingredients table with duplicate ingredients. That's part of what I am trying to avoid. For example if there are 50 recipes that use sugar - there would be 50 lineitems of sugar with varying (or same even) amounts.

Thanks, any help is appreciated.

[Kurt Knippel]

That would be correct. This is because each recipe may or may not use a differing amount of each ingrediant. You really do need a record for each ingrediant for each recipe.

[iamnotmad]

So can this not really be done? What I would like is an Amount record for each recipe. That amount record contains the specific information about an ingredient for that recipe (like amount of ingredient). But the amount record points to a table of ingredients so there is only one of each ingredient.

I can implement that but not while being able to enter new ingredients from the recipe form (i'd have to add them first in the ingredients table)

Should I just give in and do this the simple way? Am I missing soemthingand the simple way makes more sense anyway?

Thanks!

[kennedy]

I am confused... you each seem to be saying the same thing, but using different names.

There needs to be an Ingredients database... one record for each substance you can put into a recipe... no quantity info... but all other info about that ingredient (that is independent of recipe).

There needs to be a Recipes database... one record for each recipe. It probably has a field with instructions on how to execute.

There needs to be a LineItems (aka Amounts) database... one record for each ingredient in the Recipe... that record will have the ID for the recipe, the ID for the ingredient, and the quantity of that ingredient used in that recipe.

I think we all agree on that.

So, from the Recipes form, I'd expect you'd have a Portal showing each of the LineItems in that recipe... and that you'd add LineItems in that form. When adding a LineItem, you'd need to *select* an Ingredient from the Ingredients database. When you add a LineItem, you normally wouldn't add an Ingredient. So, was the question "How do I add an Amount/LineItem?" or "How do I add an Ingredient?"

[falkaholic]

It won't really make 50 copies of ingredients. Just have the ingredientID in every line item and then have a ingredients database for all the ingredient info.

For example lineitems will be have field like this:

-RecipeID

-IngredientID

-Amount

No need for many to manys or data replication.

[iamnotmad]

Kennedy, I think you have it right there. However I DO want to be able to add a new ingredient when adding the recipe (fromthe recipe form) if it does not already exist in the ingredients table. that's really my problem.

I CAN as you specified, implement it where I just have to go to the ingredients table and add any new (ones that do not exist for the recipe i am about to add) ingredients first.

[iamnotmad]

falkaholic - that's exactly what I am trying to implement. the problem is then howdo I make a form in the recipe db where I can enter new ingredients directlyby name, without having to go to the ingredients table first and enter them? There is no direct link between recipe and ingredient. I'm essentially using the amount table as the "join" table. So in order for the recipe form to get the inredient name it has to be (according to my currently limited experience) a calculated field in the amounts table (the join table) that I can then only display in the recipes form.

Is that right?

Thanks for any help!

[kennedy]

Disclaimer: I haven't done this... hopefully a guru will correct me if wrong.

I think you establish an "always matching" relationship between recipes and ingredients. For example, add a calculated field that is simply "= 1" in each file and match between those. Then, no matter which recipe you are in, a portal on that relationship will display all existing ingredients. Allow new ingredients to be created in that portal, and then adding an ingredient will be as simple as scrolling to the bottom and typing one in in the last row (which will be empty, awaiting input).

HTH.

[iamnotmad]

How will the recipe only show ingredients for that recipe then?

Thanks for the help and suggestions!

[kennedy]

You have 2 portals:

The first portal that has the LineItems or Amounts that are specific to that recipe. It shows the ingredient and the amount. That is your list of ingredients that are specific to that recipe.

The second portal shows the Ingredients that you can choose from. You'll want some button that will add the ingredient selected in the second portal into the first portal, ready for you to input the amount. That would be the normal pattern... select an ingredient from the full ingredients list. When the desired ingredient is not found, then you can add an ingredient to the list.

Note: generally you would NOT want to allow new ingredients to be added directly in the Amounts portal. Why? Because then lazy users won't bother to find the common ingredient... they'll just type in a new one... and you'll end up with lots of duplicate ingredients.

HTH.

[iamnotmad]

ahhh i see what you are saying - i'ts kind of just a reference and data entry portal. I may go a different route now but this info has been very helpful.

Thanks again.