Portal data tunneling

[georgewash]

How do you get individual line items to populate a portal which is referencing another portal?

Here's my situation:

Products:

kp_product name

kp_Product ID

ProdRecipe Join:

kf_Recipe ID

kf_Product ID

Recipes:

kp_Recipe ID

MenuItems:

kf_Recipe ID

kf_Meal ID

Meal Planner:

kf_Meal ID

Grocery List Generator:

kp_Grocery List ID:

Meal Planner 2:

kf_Grocery List ID

In Recipes there is a ProdRecipes portal to select Products:kp_product name, amount, and units

In Meal Planner there is a MenuItems portal to select kf_Recipe ID, kp_recipe name (via Recipes TO), kf_Product ID. This TO creates a new record for each day of the week and meal of the day (breakfast, lunch, dinner) and within each record can have multiple recipes and product names.

In Grocery List Generator I want to list all of the product names from the Meal Planner from both recipes and product names.

I don't understand how the portal displays data through another portal since portal rows are not individual records. I tried creating a Meal Planner 2 portal referencing the Meal ID since that holds the records of each days meals. The problem seems to be how to extract all of the data contained in that ID.

Do I need to create individual records of recipe and product chosen per meal? Also, how do I get the product names out of the recipes?

I will do some more searching relating to portal records, but please stop me if I'm barking up the wrong tree.

Thanks.

[mr_vodka]

Try this. Create a new calc field cMealRecipies = List ( MenuItems::kf_Recipe ID )

This will give you a list of all the recipies for that list.

Now create a relationship from this calc field to a new table occurrence of ProdRecipe Join. This will now show you all the related kf_Product ID for any recipies that you have for your Meal. If you put yet another new table occurrence for Products joined to that new table occurrence that you just created for ProdRecipe Join, you can now reference the product name.

[georgewash]

Thanks, I'll give that a try.

[comment]

I haven't really followed your description, but this caught my eye:

I don't understand how the portal displays data through another portal since portal rows are not individual records.

Portal rows are ALWAYS individual records in the table to which the portal points. You can place FIELDS from other table occurrences in the portal, but the number of portal rows will always equal the number of related records in the portal's table.

[georgewash]

I'm not having too much luck so far.

Which TO should I put cMealRecipes in?

Which TO is the portal related record from?

I'm sure this is pretty straight forward, but I'm getting confused with all the multiple secondary TOs.

Here's my file if you want to take a look. There is a lot of junk that needs to be cleaned up, but hopefully you can see where I am at so far.

Thank you very much.

Products.fp7.zip

[mr_vodka]

cMealRecipies should be a calc in the MealPlanner table.

Here is what I have understood from your posts.

You basically need to have multiple recipies for each meal. Each recipe has multiple products of course. You want to see all the products for either all the recipies for one specific meal, all of them for all the meals of a day, or all of them for a week correct?

[georgewash]

You are correct. I want to list all products for the week by store and aisle in a sortable grocery list.

Thanks.

[georgewash]

Mr Vodka (and any other kind souls),

I put cMealRecipes in the Meal Planner TO. I then linked cMealRecipes to kf_ProductID in ProdRecipeJoin 2, which is now linked to kp_Product ID in Products 2.

I created a portal using Meal Planner 2 in the Grocery List Generator TO, which are linked by the Grocery List ID. I placed the cMealRecipes field in the portal to check if the recipes are showing properly.

All of the recipes IDs show up in the portal, except for one problem. For meals with more than one recipe both IDs show up in the same field in the new portal.

I also added the kf_Product ID field from ProdRecipes Join 2 and the kp_product name field from Products 2 into the portal. These return

First, is there a way to separate the recipes for the same meal into individual portal rows?

Second, where are my relationships messed up? I know something is off, it shouldn't be hard to get the products listed if I am able to get the Recipe IDs.

Thanks.

[georgewash]

Comment,

Do you think I should be using a relationship via each record in the portal?

I added an ID for each portal record that is created in each Meal ID, but I am getting really funky results when I references these in my portal in Grocery List Generator TO.

BTW, I seem to be having difficulties really comprehending FW relationships work (the use of second, third, etc. TOs) is giving me fits. Any recommendations on a good book?

Thanks.

[comment]

I'm afraid you have taken on a rather difficult subject.

In a nutshell, given a structure of:

Meals -< MenuItems >- Recipes -< Quantities >- Products

each Meal record can "see" the products that go into it, and can gather their data by using the List() function, e.g. a calculation field in Meals =

List (Products::ProductName )

will return a list of all the products used in the preparation of this meal.

If you have another table that groups meals by week, for example, you can make the calculation from there, returning a list of all products that will be used that week. Such list, however, says nothing about the required quantities of each product.

To get the products together with their quantities we could point our List() function to the Quantities table instead of the Products table. Using a suitable calculation field in Quantities, this would enable us to return a list like:

Milk x 4 oz

Salt x 1 ts

...

However - and here lies the difficulty - since a single product can be included in many recipes, and a single recipe can be included in many meals, such list can very easily have duplicate entries for the same product. Getting a list where each product would be listed only once with the total of quantity is not trivial at all.

See also:

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

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

[georgewash]

Ugh. It didn't seem like it would be so difficult when I started all of this.

I will look at the links you provided.

Thanks.

[mr_vodka]

Are you going to use this to generate a report for the week?

[georgewash]

Yes. I want a grocery list based on the Store (each store has the products it carries), and all the products from the weeks Meal Plan based on if there is not enough Inventory, and also any Weekly Items that can be manually added. The list should be sortable by store aisle.

Thanks.

[georgewash]

Boy after reading these I am more discouraged.

Do you think it will it help if I don't worry about aggregating the quantities? If I can just get a list of all the products from the Meal Planner and filtered by Store it would at least give me something worthwhile.

The two links left me the impression that I will not (without maybe using a recursive technique) be able to accomplish my goal. It sure seems pretty straight forward relative to other abilities within FM.

Any suggestions on how to re-think this whole project would be great.

Thanks again everyone.

[comment]

As I said, getting just a list without quantities is easily accomplished by using the List() function. I am not sure what you mean by "filtered by Store" - there's no mention of a Store table or field anywhere in your description.

The two links left me the impression that I will not (without maybe using a recursive technique) be able to accomplish my goal.

Filemaker is relatively easy to use, but - as with any other discipline - there are things that are easier than others. You cannot expect to jump ahead of the learning curve.

Since you are after a report rather than a "live" summary, you could probably make this a little simpler by gathering the required data through a looping script. But that too would be a rather complex undertaking, hardly suitable for a beginner.

[georgewash]

The Store Filter hasn't been mentioned because I've been trying to work out each piece, bit by bit.

Maybe it will help if I spell out exactly what I am trying to have each TO do in English.

Products:

record of each individual product

assign category and sub-category to each

assign whether it is a weekly item

Recipes:

serving size

all ingedients (products) included

quantity of each ingredient

units of each ingredient

Inventory:

amount in stock of each product

reorder level

location

Store:

store name

each product the store carries

the aisle where each product is

Meal Planner:

each day's 3 meals

each meal can contain multiple recipes and individual products

recipe multiplier (to change serving size)

product amount

Grocery Store Generator:

choose store

list of weekly products that can be selected to add to list with quantity

list of products that are below inventory re-order level and ability to add to list with quantity

list of all products from Meal Planner that it's quantity would be below re-order level

Grocery List

a list of all the products from Grocery List Generator that the store carries

shows quantity

shows aisle location

sortable

[georgewash]

As I said, getting just a list without quantities is easily accomplished by using the List() function. I am not sure what you mean by "filtered by Store" - there's no mention of a Store table or field anywhere in your description.

So far I am only able to get a list of Recipe IDs with the List() function using the direction from Mr. Vodka. I can't get the correct recipe name with the ID. So I definitely can't get the products from the recipes to list yet.

I am able to get the products from the Meal Planner, but when I have more than 1 on a meal I get only 1 product (which I guess makes sense). I tried making a new field cMealProductName with a List() function but that didn't work.

I am getting all twisted around. I should probably step-back and break each part down and really understand what I have and how it works. I am afraid I have gotten somethings to 'work' and not done so in the proper steps that is causing problems now.

Filemaker is relatively easy to use, but - as with any other discipline - there are things that are easier than others. You cannot expect to jump ahead of the learning curve.

I completely understand. I am trying to learn things and build upon them - not too successfully so far!

Thanks.

Edited September 17, 2008 by Guest

[georgewash]

I'm still stuck on this. Any other suggestions or push in the right direction would be great.

Thanks.