Shopping List

[akeslady]

Hi,

I have made a simple data base for my recipes and would like to create a shopping list for 1 week.

I have the recipes and would like to add selected recipes to a Menu and out of the menu create a shopping list.

I have absolutely no idea how to do this and are hoping someone can help.

Thanks in advance!

[Genx]

:P... yay... basically add a global field... we'll call it gList... now this will work assuming each of your shopping items have a unique name...

anywho, on your layout... put a button for adding / removing the current item from the list... this button will execute the following script...

If[Position ( ¶ & Table::gList & ¶ ; ¶ & Table::ItemID & ¶ ; 1 ; 1 ) > 0]

SetField[substitute(Table::ItemName ; Table::ItemID & ¶ ; "" )]

Else

SetField[Table::gList ; Table::ItemName & ¶ & Table::gList]

End If

Replace the word Table with the table in which these fields are in, and the word ItemName with the appropriate field aswell...

Now what the script does basically is it says If the current ItemName exists within gList then substitute it out, else add it...

good luck

~genx

[akeslady]

Thanks for your reply!

I am sorry, but I don't get this at all. Is there something that is a little bit easier. Maybe I didn't make my self clear.

On my recipe layout I have a check box (add recipe to menu) and when I put an x in that box I want that recipes ingredients to be copied over to my shopping list.

Maybe it should be done in anohter way. I know there are many cook book programs out there but there are always some features that's missing so that's why I am trying to make my own.

Thanks again!

[Genx]

especially for you... this is an example of how you could show all required ingredients...

pardon my spelling and my dismal recipies... i dont cook that often

~genx

cookbook.zip

[comment]

This is a rather complex issue, especially when recipes can be repeated during a week.

Perhaps you could post a sample of your file, to clarify your current structure?

[Genx]

... twice in a week eh? i never thought of that, yeh do what comment said, he's smart

~genx

[akeslady]

I really appreciate your help!

I have been working with this for several month and are not getting anywhere - too complicated for me. As mentioned earlier, I have no idea how to handle this.

Thanks!

Recipes.fp7.zip

[Genx]

... i like the pictures but i cant say i love the structure, anyway its 2:30 in the morning so im off to bed but if no ones helped you out by this time tommorow ill throw something together for you...

~genx

[Fenton]

akeslady, you are correct that it is complicated. To build something that can actually keep track of "inventory" requires several tables. What you have now is a flat file. Useful enough as a way to store recipies; but that's all it can do. Even a little relational structure would help immensely; Recipies and Ingredients.

Those are really just "stock" tables. To track what is actually used you would need another table, to record what was used/eaten and when. The amounts used could then be decremented from the stock level of each ingredient in Ingredients (which is where you get your shopping list from).

This brings up another problem, which is measurement. A recipe will have several ingredients. Each ingredient will have a quantity used. But these quantities are often for quite different things. Often one can be converted to another, Tablespoons to Cups, etc., but not always, some are volume and some are liquid measurements. You will want to have only 1 Quantity field, but somewhere you have to deal with these different things, for labels if nothing else.

And your shopping list will want be in slightly different measurements. You don't go to the store to buy 200 Tablespoons of sugar. Though you could likely convert Tbs to lbs somehow. I imagine there are charts somewhere.

I know this because I once set myself the task of building such a thing, back in the days when FileMaker was just a hobby (obsessive perhaps -). It was written in 3 or 4, then converted to 7. It would be so much better if it was only 1 file with tables. Also, I added a whole 'nother layer, of "Food Groups" (my mother was a public health nurse :-), which further complicates the files; but is not really a big deal. And also Meals (breakfast, lunch, dinner); this was to intelligently filter the choice portals (this was before relational drop-down lists :-]

The guts of the system is not so much Recipies but Food Items. That is where you set up what to use as a label for each food, and what its "threshold" for buying is. You want to buy food before it completely runs out.

This is mostly so you can see some of the relational structure. It is neither pretty or new.

Eats7.zip

[akeslady]

Thank you very much for the to me complicated solution! I wish I could create something like this.

I will check it out to see if I can learn something...

Thanks!

[akeslady]

I am appreciating all input I can get! (I told you I am a beginner...)

Thanks

[comment]

I agree with Fenton: there's a lot of cleaning up to do, before you can move on. The Recipes table should hold only information pertaining to a recipe (I haven't seen a recipe with email yet...).

The ingredients could also benefit from being moved to a related table, and split into Quantity (number), UnitOfMeasure, and the ingredient name (or ProductID from a related table of Products).

Likewise, the scheduling of recipes cannot be done in the Recipes table - you need a separate Scheduling table for this, and another table to plan/view the week.

I have implemented the scheduling part, and also constructed a "shopping list" for the week, but it exhibits precisely the flaws that Fenton predicted. The way it is now, it is not possible to aggregate ingredients in any meaningful way.

I should add that both the scheduling and the counting of recipes repeated during the week are not beginners' stuff, so take your time with this.

RecipesDD.fp7.zip

[akeslady]

Thank you very much!

This is almost too good to be true and almost what I had I mind. Would it be possible to add a check box in front of all ingredients in the shopping list? Then It would be great to use on my Palm. I tried to add check boxes but it didn't come right. I tried to upload a picture but it didn't work.

What I don't understand is why there have to be "scheduling". Why can't I do the scheduling directly on the planner?

No wonder I haven't been able to create a solution like this, it is WAY TO COMPLICATED to me!

Thanks again!

[comment]

You can't do scheduling in the planner, because planner has only one record. This record "floats", i.e. it generates the 7 days of the week of the global date. The planner is essentially a viewer - it browses the records in scheduling: when it sees a record in scheduling whose date matches one of the dates in the chosen week, it channels it to one of the 7 portals.

This way we don't need a record for every possible date. Scheduling a recipe creates a record in Scheduling, noting which recipe on which date. That is information. A record of date with no event is redundancy - it doesn't tell us anything we didn't know already.

You could view the same information directly in the Scheduling table, but it would be just a list of dates and recipes. The planner builds the calendar-type display.

The checkbox is possible, but I'm afraid it's a kludge, again because of the incorrect structure. It requires adding a self-join relationship in Recipes, in order to get a value list of this recipe's ingredients. And it has the flaw of being entered directly into a recipe, so it needs clearing every time a shopping list is generated. And checkboxes don't slide, so it's not going to print as nicely (though this might not bother you on a Palm).

RecipesDD.fp7.zip

[akeslady]

Hi again,

This is so great! You are so smart! I can not believe I have a shopping list... BUT, the ingredients for 6 dishes prints on 3 pages - lots of empty space in between each dish ingredients. Just imagine how many pages it will be if I have something for 1 whole week... And when transfering it to the palm it isn't coming in as a list. It is coming in as 6 records and that way I can not check off what I have put in my shopping cart. I can only put a check mark for each record. I hope you understand what I mean.

I appreciate your input!!!

[Genx]

The list issue is most likely the fact that comment only generated the report layout only for reporting functionality. If you want a list, just generate a new layout, make it pretty, then define that layout to use list view.

As for the report itself that generates 3 pages for 6 ingredients. I assume this is because there is a large gap within the body of the report. Go to the report page, and just click and drag the body upwards, hopefully reducing the space that each ingredient will take up.

~Genx

[comment]

The ingredients of 6 dishes ARE six records (six recipe records with an ingredients field each), and there's not much I can do about this with the current structure.

Adding the checkboxes means the field has to be as tall as the longest list of ingredients in any recipe. So if the list is any shorter, there will be space (as I said before, checkboxes do not slide).

I don't know what you can do on Palm (or even how you transfer the list to it, and to what application), but in Filemaker, in order to have a checkbox against each ingredient listed in a field, you need a relationship and a value list based on the relationship.

It would be possible to write an elaborate script that loops between the recipes of the week, and creates records in another table - one record per ingredient. But that would be a patch, not a solution.

What we have here is an example of things getting more and more complex as a result of starting wrong. Everything starts by the ingredients being lumped together in a single field. This is a violation of the "one fact per field" rule, and the root of all the subsequent problems.

[akeslady]

Hi,

I have FileMaker Mobile 8 and I can HotSync the shopping list to the Palm. It is a plus if I could put a check mark in the items I put in my shopping cart.

Thanks!

[akeslady]

Hi,

Your solution is actually pretty neat, but how do I print ONLY the "shopping list" so only the ingredients prints? Should I have the shopping list on a separate layout?

Thanks!

[comment]

Here's an example of what *could* be done - if the ingredients were in a separate table, with separate fields for quantity, measure and product.

Note that if a recipe is repeated, the shopping list automatically adjusts the required quantities.

RecipesDD1.fp7.zip

[akeslady]

Hi,

I can't thank you enough - this is working just beautifully! I wish you could see the shopping list on my Palm! I just wonder what "Accessible Power", Powerful Scripts" in Layoude Mode is... I have never seen that before.

Thank you sooo much!

[comment]

"Accessible Power", "Powerful Scripts" etc. is gibberish that Filemaker puts in empty fields when you turn on the Show Sample Data option in View.