Foreign keys

[RCB]

I own a small food business in which we change our menu every month. My database structure is:

Customer >-< Order >-< Unit Meal >-< Meal Info -> Meal History (please see attached sample file). My goal: to be able to add a new order and be able to see only the menu items available for a particular month (and input quantities of meals).

Thanks in advance for any help!

RCB

Food_Biz_Sample.fp7.zip

[Ender]

Welcome RCB!

Your structure is just a bit off. The Order Item should be related to the Menu Item by the MenuItemID (see attached). For selecting a Menu Item for the current Month, use a global or unstored calc in the interface table (probably the Order table), then relate the interface to another TO of the Menu Item table via the Month. You can define a portal or a conditional value list to show only those Menu Items for the selected Month, and use that for populating the Order Items.

[RCB]

Ender,

Thank you for your quick response. I've seen your helpful posts all over this forum and am pleased you've taken the time to consider my question.

To use your table naming, giving "Menu Item" a month attribute would not be good design for my case. I repeat menu items several times over the year. Now, I give each item an unique ID and use a separate table to couple the MealID to various months. If I gave my "Meal Info" table a month attribute, I would create much duplication. Without this, I believe the rest of your suggested solution will not work. Am I missing something?

A little more about my business and my database--we produce meals to-go based off of a menu of 15-20 selections that change monthly. I use my "Meal Info" table to track the various meals we offer (generic). I use "Unit Meal" to track individual meals. Unit Meal also serves as a join table to "Order." (There is a many-to-many relationship between "Meal Info" and "Order.")

What I really have is a small manufacturing business. Perhaps I should study sample relational design for a manufacturing process. I just thought I was missing something simple and obvious in trying to use the key "month" to present only the items available for a particular month when entering a new order.

RCB

[comment]

trying to use the key "month" to present only the items available for a particular month

That shouldn't be too difficult.

Food_Biz_Sample.fp7.zip

[RCB]

comment,

Thank you for your help. Your solution helps me preserve my table structure.

What I would really like is a form that would present all of the meal options for a particular month with associated quantity fields which I could tab through. I can not think of a way (without scripting) to accomplish this. Any ideas I would much appreciate!

RCB

[comment]

A portal to the AvailableMeals TO will show what meals are available for the selected date. I don't quite follow what you mean by "associated quantity fields". Currently there's no quantity associated with available meals - only with meals actually ordered.

[RCB]

Exactly.

I should be more clear in my request. There is no association between meals available and quantity. I would like to display a list of a month's meals and have blank fields adjacent to fill. I envision pressing "enter" and having the "OrderItems" table populate correctly. My instinct is to code (script) this using variables and find requests. However, this approach in Filemaker is rather ugly and cumbersome. I thought there might be a clever way to use relations and portals to achieve the same result.

I hope I am being clear. Thanks again for your help.

[comment]

OK, I get it now. I am afraid you are up against one of Filemaker's weak spots. There are ways to simulate this, but there are also drawbacks. The simplest way is to import ALL available items into OrderItems whenever a new Order is created. This means a new Order MUST be created by a script. It also means that an order of 1 meal has 6 related items (5 of those with no quantity).