Different values in multiple instances of same field in portal

[georgewash]

Hello,

If there is already an answer to this, please point me to the link, my searching came up empty.

I am trying to build a Meal Planner where I can select Recipes and Products for each meal for every day of the week.

Here are my tables:

Products:

- kp_product name

- Product ID

Recipes:

- kp_recipe_name

- Recipe ID

Join Table:

- kf_Product ID

- kf_Recipe ID

Meal Planner:

- Meal ID

In Meal Planner I want to be able to pick the recipes and products that go into each day's breakfast, lunch, and dinner. My thought was to create a portal for each day's meal and use a drop down menu to make the selections. This worked fine for the first portal, but each of the the subsequent portals end up switching the fields to the latest choice. The fields match up from portal to portal, ie. Sunday Breakfast Recipe include: oj, bacon, eggs. If I make Monday Breakfast Recipe: coffee, toast, the eggs in Sunday will remain.

I have thought about repeating fields, but that doesn't seem like it would work. I can't imagine I will need individual Join Table for each day/meal.

Any help is appreciated.

[dreamingmind]

George,

It sounds like you are just referencing a wrong relationship somewhere. One common result of such a problem is that the first related record gets operated on no matter which portal row you change.

Check the portal, the fields in the portal row and any script steps that reference the relationship and you will probably find an unexpected and incorrect reference in one of them.

Regards,

Don Drake

[georgewash]

So, I should be able to have 2 separate portals with the same fields and be able to select different values from a drop down menu?

I checked my fields and they are set-up exactly the same. For example, the 'Recipe' field is displaying data from the Join Table using values from the Recipe ID which is defined to use values from the Recipe table: kp_recipe name.

I am getting the same value populated on both portals, which seems to make sense since. I am sure there is a way to accomplish what I need, I just am unsure how to do it!

[dreamingmind]

George,

Here is a working sample.

Don

thingy.fp7.zip

[comment]

You have two many-to-many relationships: Meals to Recipes, and Recipes to Products. Therefore you need two join tables.

[bcooney]

You should have:

Products:

__kP_ProductID(ae serial, can't change)

ProductName

Recipes:

__kP_RecipeID (ae serial, can't modify)

RecipeName

ProdRecipe:

__kP_ProdRecipeID (ae serial, can't modify)

_kF_ProductID

_kF_RecipeID

Qty

UnitMeasure

use this table as a portal on your recipe form, adding products to the recipe.

Meals:

__kP_MealID (ae serial, can't modify)

MealName

MealRecipe:

__kP_MealRecipeID (ae serial, can't modify)

_kF_RecipeID

_kF_MealID

use this table for a portal on your Meal form layout, planning the recipes used for the meal.

[georgewash]

Thanks for all of the help so far.

I guess I'm not clearly communicating my issue.

I can get the portal to choose multiple recipes and products for a meal.

My problem is I want the layout to have 21 meals (7 days, 3 meals a day). I have tried to create portals for each meal, but since they have the same fields each meal ends up with the same recipes and products.

I tried to create a second join table, but I still get the same result.

BTW, I believe I have 3 many-to-many relationships: Recipes to Products, Meals to Recipes, and Meals to Products.

Maybe there isn't a way to do what I want, but I would think it would be possible. If there is a different approach to achieving my desired end result I'm more than flexible.

[dreamingmind]

My problem is I want the layout to have 21 meals (7 days, 3 meals a day). I have tried to create portals for each meal, but since they have the same fields each meal ends up with the same recipes and products.

George,

Hmmm...

If you truely have 21 portals, each will naturally show the first related record. Hence, each will show the same thing.

In FM9 you can specify which portal row is the initial portal row and how many rows will show. If FM8.5 has this feature, you'll find it in the portal setup dialog.

Using this, you could have each portal show one row and each show a different row. Why not put a scroll bar on the portal and let one portal show all 21 rows though?

Regards,

Don Drake

[comment]

Well, here's one way to look at it (see attached).

I believe I have 3 many-to-many relationships: Recipes to Products, Meals to Recipes, and Meals to Products.

Only if you serve raw products, which is not recommended. Not in the culinary sense, but in the data structure sense. Either make an artificial recipe for the product, or move products and recipes into the same table and employ a recursive structure - see:

http://fmforums.com/forum/showtopic.php?tid/192677/

WeeklyMeals.fp7.zip

[georgewash]

Wow!

That is exactly what I was trying to do. Now I need to make it work within my database. I think I understand the basics involved after taking a quick look.

Thank you very much.

BTW, is there any need to select the ID and have it populate 2 fields (the ID and the name)?

[comment]

is there any need to select the ID and have it populate 2 fields (the ID and the name)?

That's not what happens. You select the ID, and you SEE the related name from the selected related record. Only the local ID (the "foreign key") is populated in the process.

[georgewash]

I am able to get it to work thanks to your sample file. Thank you very much.

I am stuck on what should be the easiest part. What type of layout is this so all the records show up on the layout? I can't tell on your example. I am assuming a list, but can't get it to work like yours. When I opened your file up it had 21 records already and every time I change the gDate, there are still 21 records, just with new dates (which is what I want).

A nudge (push?) in the right direction is appreciated.

[comment]

every time I change the gDate, there are still 21 records, just with new dates

That's basically all there is to it - it's a calendar that shows 21 permanent "slots" (7 days * 3 meals). Each slot calculates its date and its type (breakfast/lunch/dinner) from the global gDate field and from its position in the series. The slots are in List view.

Another way to do this would be to generate a real record for every possible meal from now until forever, and - in order to view a specific week - find the 21 meal records of that week, and show them in the correct order. However, I am not a fan of generating records for non-entities like days in a calendar - see:

http://fmforums.com/forum/showtopic.php?tid/176396

Because each "virtual meal" can be uniquely identified by its date and serial number, we can base a relationship to the MenuItems on these fields, and every time we "recall" the same slot it will reconnect to its own menu items and not to others.

[georgewash]

I was getting a new layout for each new record, but I fixed it, I didn't have gDate as a global field and a few other small errors.

Last question (I hope). How do you format the two cDate fields so they only show up 1 time for each date? Each of the records I created has the day and date displayed.

[comment]

The actual field on the layout is cDateLabel. In version 9, this field wouldn't be required, as the field cDate could be formatted conditionally (using the same formula as cDateLabel does).

[georgewash]

Duh. The little things get me every time.

Thanks again.

[georgewash]

Ok, I am stuck on another 'simple' thing that I am sure is obvious but I can't figure out (boy is this humbling).

When the Recipe ID is selected, the Recipe field is not automatically filling in. Nothing populates in the Recipe field.

I believe I have all the relationships correct, but it must be where there is a problem. This isn't a lookup or anything special, so I am stumped.

I've gone through and checked my tables and relationships and compared them to yours with no luck. Any ideas would be great. I feel like I must be missing some fundamental understanding, but don't see where.

I even added a Serving Size field to the Recipe table in your example and was able to add it to Slots and have it automatically populate.

Thanks.

[comment]

It's difficult to debug blind. I suggest you check again your relationships (including where creation of new records is allowed) and also whether the fields placed on the layout are coming from the correct table occurrence.

[georgewash]

I'm sure - it's hard to debug with the data right in front of me.

I don't know what I did, but I rebuilt the portal and it is working now. I may have had the wrong table checked to "Allow creation...". My other problem may have been trying to add new fields into the portal. How is that done?

Now I am on to creating a list with multiple criteria for a field (ID and checkbox), that ought to be a hoot.

Thanks again.