My Recipe database needs help...

[Mary Butterfelder]

Hi all, I'm new! What a wonderful forum! This is my first post.

 

I am running a social club that is a test ground for chefs in a number of restaurants in my area that want to have choices for celiacs (that are gluten free), pescetarians, vegans, etc. and some with food allergies. We have small groups of people come for dinners and a participating chefs cook test meals to receive feedback for the restaurant they work at.

 

The primary purpose is the social aspect but more so to help those of us that cannot tolerate gluten, or have life choices that don’t include meats, fishes, cheeses, etc. We sign chefs up from various restaurants and they volunteer their time to have a forum for our guests to critique their creations. As many of you already know, these types of choices are becoming mainstream. Guests pay a fee to cover costs to purchase food. This is also where the database also plays a role to calculate costs.

 

MY SURPRISE AND EMBARASSMENT

I thought I could handle this database because I was just so good with Bento, but goodness me, I had no idea the scope and breadth of knowledge you experts must have to make a good filemaker database work properly. Bento is…I don’t know really…but my hat is off to you ladies and gentlemen who use filemaker. All niceties aside, I would appreciate some help.

 

WHAT I’VE DONE SO FAR

Went to the filemaker support forum

Read the articles on relationships

Tried to get this thing going, but epic fail (my daughter’s language, sorry!) on my part…

Called Filemaker and they said try this forum

Searched for recipe databases and found nothing on the forum that was any help.

Looked at what other people post, so I am going to describe what happens in the real world.

I have started the database with some data, attached it, and I am asking for someone to help make it work.

 

WHAT HAPPENS IN THE REAL WORLD

 

The database is done in FMPro and will run on an iPad.

I need to find and/or create a Chef, see their recipe list in a portal, then go to that specific recipe to refer to it, or create a new recipe for that chef using a list of ingredients. I can get the first layout (Chefs/Recipes) to sort of work, but I can't get the second layout (Recipes/Ingredients) which shows recipes and ingredients to work. Pretty sure my structure is wrong (obviously) and I'm afraid this is the part of a filemaker that I don't quite understand right now. I also have the FMPro 12 Missing Manual, but I can't glean enough from that. I've posted a screenshot of the relationship structure and the actual file.

 

The Ingredients table will hold all the price per quantity data as the restaurant wants us to calculate the cost per dish based on the amount of ingredients used per plate. I'd do these calculations in the Recipe/Ingredient layout? in the Recipe table? or the Ingredient table? But I can try to figure that out if I can get a structure to work first. I think I can do the calcs as they seem pretty simple math.

 

For the structure, I've tried multiple table occurrences, different relationships links, but I think that this part is just far beyond my level for now.

 

and how to get rid of the login requirement? RIght now I have the login 'Admin' with no password entered.  

 

Any support would be most appreciated!

Thanks!

Mary

 

RecipesAug26_C.fmp12.zip

[Fitch]

For starters, you will want a table in between Recipes and Ingredients. This is because a recipe has many ingredients, AND an ingredient may be used in many recipes. It's a many-to-many relationship, and the table in between is generally referred to as a "join table." The join table needs to store the ID of the recipe, the ID of the ingredient, and then the quantity and other details that pertain to the way the ingredient is used in this recipe.

 

Contrast this to a one-to-many relationship such as Chefs to Recipes: each chef may have many recipes, but a recipe (we'll say for simplicity) only belongs to ONE chef.

 

Welcome to the forums, Mary.

[Mary Butterfelder]

Thanks for your welcome Tom and thanks for your help...I'm trying to piece this together given your advice so I have changed the structure as in the attached graphic. Sorry, I have questions as that did not get me very far I'm afraid 

 

Once I create the join table between recipes and ingredients, I assume that the Recipes/Ingredients Layout uses records from the join table I just created? I cannot get the recipes that are created in the Recipes table for a particular Chef to appear in the portal in the Recipe/Ingredients layout...so it follows that I also cannot get the ingredients to display for any one recipe too...I must be missing something...

 

[Matthew F]

Your relationship graph shows a little T-stop on the connections to Recipes_Ingredients.  I think this means that the records can't be indexed and the relationship will break.  Did you set the Key fields on this table to have global storage or to not allow indexing?

[Mary Butterfelder]

Yes, when I first set it up, I had global storage turned on in both. I have changed both fields to 'indexing all' in the middle table, but still can't get it to work.

 

Trying different things...it seems this is more trial and error than anything at this point. 

 

 

[Mary Butterfelder]

It seems to me like I have this totally wrong. Can anyone offer some advice? 

 

It seems to me that a recipe is simply a summarized view or preview of a bunch of ingredients that I assign a recipe name to, and to which is owned by a chef.

 

Isn't that right? So, if I create a recipe and assign it a name, I can just add ingredients and as long as I assign it to the same recipe name? 

[Lee Smith]

I played with your file a little, and changed a few relationships. See if this helps you out.

RecipesAug26_C1.fmp12.zip

[Fran_g]

I haven't got a copy of FM12 but have had a quick look at your file and its questions on the layouts on another system.

Attached is a quick v11 file that may be of some help to get you started. You can open it/convert it to v12 format.

recipes.zip

[Mary Butterfelder]

Uhhhmmmm, thank you for your help!  

[Fenton]

Fran_g has added the most critical table, "recipe|ingredient", which can be called a "join" table. It has 2 ids, one from the "recipe" and one from the "ingredient" table. 

 

Needed because the each "ingredient" is not unique to a recipe, the same one will appear in many recipes. Most of data about a ingredient will be in the Ingredients table.

 

It is possible that you might need one more table, "supplier|ingredient".* You might need this for a few reasons. A ingredient can be gotten from only certain Suppliers, and you'd want to know how; you'd want to produce a Value List showing only those who have it; this could be done via a relationship to such a table. Also, you might want to know what the price is for the same ingredient at those who have it.

 

Such a table would be in the "table occurrence group" of the Relationship Graph (which Fran_g built well, with colors).**

 

* I sometimes use both "|" and single type names, as it shows me clearly that "this is a join table; a record has (always) only one of each of "parent" tables.

 

** I'd increase the naming scheme; i.e. "REC__Recipes" -> "rec_Chefs", etc.. This allows them to show sorted correctly anywhere you see them. But each of us "names his own" :-]

 

P.S. Mary, you do have FileMaker Pro 11? As otherwise you couldn't see Fran_g's file. We could dupe it to 12 for you.

[Mary Butterfelder]

Thanks Fenton and many thanks to Fran for doing the file, which is what I am trying to accomplish. I do have FMPro11 and bought it last year when I was going to try this out, but never got around to it. upgraded it since but thanks for thinking of me! Thanks too to others for your input. 

 

I will give your suggestions a go Fenton, as yes, different ingredients will have different suppliers with different costs...I think if I follow the logic Fran has applied, then I should be okay...learning curve is steep though...it still amazes me how one is to learn the nuances and I guess it goes to show how valuable expert consultants are in the development process. It's certainly not something I can just pick up and do...kind of amazing what it takes to make something work properly...

 

I will look more closely at the naming scheme too as this is a good way for me to understand the logic in the relationship graph...I have been playing around to see how things behave, so am slowly getting there. Now I need to figure out how to write a script to create a new Chef/Recipe...it's not as easy as hitting New Record, but this baby step will be a good challenge for me to begin to wrap my brain around how to do things. 

 

I will pipe in if I have any further questions and again, I do appreciate all your help! 

 

M.

[Fenton]

First, the main reason we help others is that we all knew little when we first started with FileMaker, and all have learned from others. Some of us have just been on it longer. So always feel free to ask for help.

 

Here is just an idea of how I would do the naming of the Relationship Graph, with its three "table occurrences groups".  For the names, the first one has short phrase, then 2 "__", then it is used again for each "table occurrence" in the group, but with only 1 "_". The reason for this is to make sure they all sort correctly (as possible). Because you will often need to choose from the list, often first sorted by "group", which makes it easier. But sometimes you will be given the whole list of all table occurrences; such as for a Go to Related Record script step. In that case a clear sort by name will make a big difference.

 

I also like to use the a single name, when the relationship is from on ID to another ID, and the resent is always one record. For example: "rec_Recipe|Ingredients" is a "join" table, from one Recipe, to one or more Ingredients. Though, once you use a single name, you must continue with it, for as far as you go to the right.

 

 

[Mary Butterfelder]

Okay, thanks for that. It's great to know that there is support for this application beyond what one can glean from the online and printed materials...which all seem far too simplistic to really make a difference in real world applications. 

 

I've changed the naming scheme and have added the join table to SUP__Supplier linked to sup_supplier | Ingredients linked to sup_supplier | ingredients_Ingredients linked to SUP_Supplier 2. Is that correct? I'm trying to follow the same logic as what was created for the Recipes TOs.

 

Another question Fenton...I need to do add fields to ingredients for:

 

max size of dish (e.g., 1000 grams) which I think goes into the Recipe table as it's the the total mass of the recipe per dish as restaurants have limits based on the primary food (e.g., ribs vs salmon)

amount of each ingredient (it'll be standardized to grams and will be like 400 grams of Salmon or 100 grams of Quinoa)

cost per amount

total cost per dish

other stuff...which is all easy but some are simple calculations and others are summary fields...

 

my question is this...

 

I think it's obvious that anything related to an ingredient must be stored in the Ingredients table, which is Sup_Ingredients and will appear in other Ingredient TOs

 

At present, the layout I'm using to assign ingredients is the REC__Recipes table, and the portal is showing related records from the rec_Recipes | Ingredients join table...but the way the portal was set up initially, the field IngredientName is coming from the rec_recipes | ingredients_Ingredients table, according to your naming convention, which I have done very carefully (In the initial chart, the TO was rec_recipes | ingredients_Ingredient). I've tried a few things now and can assign the amount of any one ingredient using this scheme, but only the first record in the portal shows the calculation (a volume percent calculation) working, the rest in portal remain blank...

 

Thanks for your help...if you need me to post the file, I shall...or is this easy to follow? If I'm asking for help, I need to make it as easy as possible in the hopes of getting help...

 

Thanks again...

Mary

[Fenton]

It is a little tricky for me to understand exactly what the "grams" is (weight?). In any case, it seems that you want to enter the number in the Ingredient table's field. Since it is just a number field, it seems you should be able to see this from anywhere that can see the Ingredient table, via a relationship, such as "rec_recipe|ingredients_Ingredient".

 

You actually want to bring this value into the Recipes | Ingredients table's record. So, you could either bring it in via an option via a calculation, of via a Lookup. I'll use the later (as I'm a old-time type).

 

Once you have it, I assume you'd want another number field to say "how many of this ingredient" (assuming you sometimes have >1). Then you'd want a calculation field to multiply them to get the real Grams for this record. 

 

Then, in Recipes you need a calculation field to Sum ( rec_recipe|ingredients::_cGrams_cnt )

 

Lookup, for join table, and final calculation field of Recipes:

[Mary Butterfelder]

Thanks!

 

Yes, sorry, we're in Canada and grams is a metric unit of mass as pretty much everything we purchase in bulk is sold in 100 gram quantities. In the states it's by the once or pound. Up here, primary ingredients like meats and fish are mainly sold by the Kilogram...we are also in Seattle, but we are not tracking what's going on with costs in Seattle as the restaurants are a little behind us.

 

okay, I'll have to fiddle a bit...thanks again...I will likely have to come back with questions...I hope I'm not becoming a bother! 

 

Mary

[Mary Butterfelder]

Hello Fenton. I want to thank you so much for your help thus far. I have now got all my needed calculations working as you have indicated above. Working fine! woohoo! Thanks!

 

For the past few days, I've been trying to get the supplier | ingredients relationship to work and have failed and I'm afraid I need your help once again! 

 

here's my thinking...

 

One Chef has one or more Recipes

One Recipe has one or more Ingredients

One Ingredient has one or more Suppliers

 

A recipe can have ingredients from different suppliers and there are different costs from each supplier

 

I couldn't get my first try to work so started from scratch trying to learn from the TOs for Rec__Recipes, since a supplier can have many ingredients just like a recipe can have many ingredients

 

I tried a relationship and new TOs for suppliers and ingredients but couldn't get it to work. 

 

Do I just need to build on the relationships in the TO for Rec__Recipes or have a new set of TOs for the many suppliers to many ingredients portion of the database?

 

I’d like to give it a shot but need some direction…if you could please?

 

 

Thanks!

[Fenton]

I think the "Ingredient has one or more Suppliers" is a little confusing, to anyone; because there's two ways to do it, either of which would work, yet neither of which seems "perfect." Often the hardest thing to deal with in a database is not the things that are "very different," but those that are "almost the same; but not quite." 

 

An ingredient could have only one supplier. Hence you think "one ingredient table. But what about them that have more than one? There are two methods.

 

A. Only 1 Ingredients table. Just add a new record for each "same" ingredient, and add the "each" supplier (id) to show "different."

This seems OK, but there's two questionable things:

1. If there's a lot of info which is the SAME for both (or more) of the "same named ingredient," then it's awkward. 

2. If even some of them have multiple suppliers, it gets kind of bloated to use.

 

B. Let the Ingredients table be just that, info, picture(s), etc.. Create a new "Ingredient|Supplier" join table. It would have an id for both of its "parent tables," with a new record for a new match between them. It would be the table you would use in most cases to "choose" an "ingredient" for a "recipe"; whether you chose which "supplier" for that (right them or later, depending on factors).

1. The "odd" thing about this method is that you'd have to create a new Ingredient|Supplier record for every addition to a recipe, even if you didn't know what supplier; because it would be the portal where you were showing them. 

[ Unless you showed only the ingredients in the Recipes portal view of them, and handled their chosen supplier in a separate view. But that seems tricky; more work at any case.] 

2. You would need to see (or have) the name of the ingredient (and its ID) in the portal, and in the Value List. This could be gotten from a Value List from the Ingredients table.

 

[ The Value List for the Supplier could also be gotten. But I think you need another table for each unique Supplier|Ingredient join. Because you would want to have these connections set up before you get too far. You'd want a Value List of suppliers who HAVE a particular ingredient. We can help you with that later. ( Don't want to freak out yet :-0 ]

 

I would definitely use "B" above, as I find it easier to have another table than to do tricks to handle "two in one." Also because of the "info and/or picture".

 

I'm sorry if the above seems a bit confusing. Remember, even going to the store to buy food for dinner requires much similar skills :-| 

[eos]

Hi Mary,

 

have a look at this version; it shows a possible implementation of the Ingredient/Supplier correlation.

 

It does about what Fenton described in his point B, and it adds the business rule that an Ingredient always needs a supplier, even if that supplier is unknown; so we add a supplier named ”Unknown“. Therefore, an ingredient is not added directly to a recipe, but always as a reference to an IngredientSupplier entry (and that's where the unwieldy table name comes from …)

 

This example has a UI for recipes, plus a basic one for ingredients; you'd need similar layouts for other entities. On this note, you may want to add another table for Categories, and a join table for IngredientCategories; this will help you filter down those long lists, and it puts more of your logic into the open, i.e. records, instead of value lists or other dark places ….

 

The scripts demonstrate how you can perform integrity checks before adding new records, plus some other functionality.

IngredientsAndSuppliers_eos.fmp12.zip

[Mary Butterfelder]

thanks Fenton, thanks Eos...you guys amaze me!  okay, I'll study this...

[Mary Butterfelder]

okay, I've had a bit of a chance to look this over and eos, you resolved the ingredients/suppliers problem very nicely! thanks! 

 

now here is my next problem:

 

I have the solution working on the Chef Recipe Ingredient side with the portals, etc. thanks to Fran and Fenton. Now the supplier side is more complicated so, with my rudimentary knowledge, will it be tricky for my to integrate the two? and if not, I imagine that it would be better to add what I have for Chef Recipes Ingredients to the more complicated Recipes Ingredients Suppliers that you just finished? I know it's hard for anyone to advise when you can't see both, but any suggestions? I don't think I"ll be able to combine Recipes Ingredients Suppliers with what I already have for Chef Recipes Ingredients...or it's going to get messy! 

 

Thanks! 

M.

[eos]

[…] eos, you resolved the ingredients/suppliers problem very nicely! thanks! […]

 

You're welcome. Next time I'm in Canada, feel free to have one of your chefs bake me a tasty cheesecake.

 

I'm not sure if according to your rules a chef can have many recipes; if so, then all you need in addition is a Chefs table and a ChefRecipes join table, which gets its second foreign key from Recipes. If not, then you'd only need a key field in Chefs for the one recipe ID. There, you're done. (You wish!) But I'd recommend going the join-table route. If your endeavor is successful, then chefs will have return engagements, and customers crave novelty; therefore …

 

See here what I mean: Chefs, Recipes, Ingredients and Suppliers.

CRIS_eos.fmp12.zip

[Fenton]

If a Chef has multiple Recipes, but a Recipe has only 1 Chef (ever), then (I don't think) you need a ChefRecipe "join" table. You can just set the Chef ID (from the Chef table) into the Recipe that is his. You would see all his in a portal on a Chef form view, with the relationship on Chef ID to Recipes.

 

Yes, you'd have quite a few portals on that layout, if you'd want to see everything about a Chef from there. The method eos used, of setting global ID fields would be how you'd show the details, starting from left to right. You'd likely want to clear those to the far right after a change to the left (or else they'd show irrelevant data). This may should complex, but really, they are much the same, so if you can do one, you can do others. The "clear the globals to the far right is also not that hard, globals can be cleared more easily (and safer) than regular fields.

[Mary Butterfelder]

wow, my jaw just dropped...so I will work with this and incorporate what Fran and Fenton had done earlier...

 

and yes if you do get to Vancouver or Seattle or Portland (a long way from where you are!), you have four reservations waiting for you for dinner at no cost of course. But, I must warn you, this is ultra healthy-gluten free-no processed sugar-no dairy kinda food!!! so I'm sorry to say, no cheesecake...but we may have a recipe for a dairy free cheesecake by then...using goat milk instead...working on that now! I would certainly send you the recipe!

 

Same goes for Fenton and Fran...we will be in Portland very soon...just signing up restaurants now. I thnk you guys are further south though. 

Thanks Fenton!!! 

[Fenton]

Well, good recipes (especially "tastes good") for healthy-gluten are likely needed. I am living with my daughter, who's skin is allergic to regular bread, etc., and her two boys, the youngest one has some problems also. She has some trouble with restaurants. But we are in Santa Cruz, which, for its size, has more "healthy" food than anywhere I've seen (and I've seen down to San Diego).

[Mary Butterfelder]

Hi Eos, I asked this question about the filter for the portal...I don't think it's workable in FMGo. What would we do alternatively? if anything? The list of ingredients is about 970 long and bound to grow...but I don't think it's the size, I think it's just the way FMGo handles the filter script by popping up the keyboard, etc. 

 

Thanks,

M

 

http://fmforums.com/forum/topic/89640-filtering-a-portal-and-behaviour-in-fmgo/

[jeanyves]

hello everyone

It 's with very great interest that I read this post.
I myself am very interested in this type of application. But being a beginner I encounter some difficulty to reach my goals ...

Files and posted notement the eos are really impressive.

I'd like to ask you if from one of these files, it was possible to treat a sub recipes?
Let me explain, a pizza is a recipe for dough, a recipe sauce and sausage for example. So I have 2 recipes and 1 ingredient. It mean that the recipe can be itself a part of another recipe.

Is this possible?

Thank you very much for your help and congratulations for this site
excuse me for my english pathetic, I'm french ...sorry!

[Matthew F]

You talked about making Pizza so I thought you were Italian!  

 

Perhaps you want to add one or more portals to the Recipe layout.  One would show recipes for items used by this current recipe (dough and sauce on the Pizza recipe).   Another portal could show which recipes use the current recipe (e.g. pizza and spaghetti on the tomato sauce recipe)  To do this you would want another "join" table (RecipeJoin)  that connects two instances of Recipe on the relationship graph.  RecipeJoin would have only two fields parentRecipeID and childRecipeID.  Does that make sense?

[jeanyves]

You talked about making Pizza so I thought you were Italian!  

 

Perhaps you want to add one or more portals to the Recipe layout.  One would show recipes for items used by this current recipe (dough and sauce on the Pizza recipe).   Another portal could show which recipes use the current recipe (e.g. pizza and spaghetti on the tomato sauce recipe)  To do this you would want another "join" table (RecipeJoin)  that connects two instances of Recipe on the relationship graph.  RecipeJoin would have only two fields parentRecipeID and childRecipeID.  Does that make sense?

Hello Matthew F and other ...

No, I'm not Italian ... but I love good pizza .... I am a pastry-baker  chef in France, near Toulouse ...

Thank you for your reply. I tried to advance my project with your directions ... but I already have a few questions .... you recall I am  beginner

I create a table "RecipeJoin" with 2 field  parentRecipeID and childRecipeID. But how must be filled these 2 values​​?

Then I create an instance table "RECIPES_Recipes" called "recipes_RecipeJoin" and I created links:

_recipeID_pk --- <parentRecipeID and childRecipeID> --- _recipeID_pk

Is that correct?

thank you very much

@ +

[Matthew F]

Hi jeanyves,

Take a look at the attached.  I've revised Eos' database with a join table, portal, and button to go to the related records.  You can add a second portal if you like.

 

CRIS_eos(mf).fmp12.zip. 

[HJS]

Dear All, is this thread still alive and are the members still here? I was following this post very closely and do have some questions being one of those new users starting at zero...

thanks for any answer, I would than start to aks my questions!

br

HJS

[doughemi]

Better to start a new topic with your specific questions.

[Lee Smith]

Hi HJS, and welcome to the FM Forums,

Although an older thread may sound like your need, or similar to it, it is rarely spot on to your own need. I agree with Doug that it's better to ask your question, in your own words  explaining what your question is.

2 hours ago, HJS said:

s this thread still alive and are the members still here

BTW, the only one who has visited in the last year is me.

I wrote you a Private Message.

Lee

[HJS]

OK thx, I will start a new post in a respective forum...

br

HJS