Filtered portal data into calculated field

[Greenman]

I have a nutritional database I downloaded from the US Government in text format.

I have foods table with an NDB_No as the key.

A nutrients table has key Nutri_No, and related to foods table by NDB_No.

The nutrients table is related to nutrient_def table by NDB_No and Nutri_No.

I can create a filtered portal on a foods table layout that includes a list of all nutrients for the food with the same NDB_No and Nutri_No.

So everything from Daily Fiber to Total fat show up on the filtered portal for that specific food item. Great.

But the data needs massaging. For instance, I'd like to filter the portal on the sort order for nutrients in the nutrients_def even though the portal is listed for the nutrients table. I don't see a way to do that.

Also: I need to calculate Calories from kilocalories for the specific food. I cannot figure out how to extract just the kilocalories from the nutrient list for a specific food, just so I can divide it by 1000.

The equivalent in SQL would be something like:

SELECT n.Amount AS kcal

FROM nutrients AS n, nutrient_def AS nd 

WHERE n.NDB_No = 1234 <specific key id of food from food table> 

AND n.Nutri_No = nd.Nutri_No AND nd.Desc = 'kcal_energy' <specific text limiting for the description of kcalories on nutrients_def table>;

I think I want the calculation field to appear in the foods table. For instance, Calories = <kcal from sql statement above>/1000.

How do I accomplish this in FMPro 11 Advanced?

Again, I'm not asking how to connect to an SQL db. I need to know the equivalent process in FMPro.

Is there a better way to do this than put the calculation field on the foods table?

[comment]

For those of us who are not familiar with the subject matter, please explain some of the basics: it seems one food has many nutrients, but a nutrient has only one parent food. This would suggest that 'nutrients' is merely a join table between 'foods' and 'nutrient_def' - but then this part makes no sense:

The nutrients table is related to nutrient_def table by NDB_No and Nutri_No.

If NDB_No is the unique identifier of a food, then it has no place in the 'nutrient_def' table.

In general, filtered portals are great for displaying data, but not for much else. To work at the data level, you should use filtered relationships.

[Greenman]

For those of us who are not familiar with the subject matter, please explain some of the basics: it seems one food has many nutrients, but a nutrient has only one parent food. This would suggest that 'nutrients' is merely a join table between 'foods' and 'nutrient_def' - but then this part makes no sense:

If NDB_No is the unique identifier of a food, then it has no place in the 'nutrient_def' table.

In general, filtered portals are great for displaying data, but not for much else. To work at the data level, you should use filtered relationships.

You're right. Sorry! There are 7000+ foods listed in the foods table. There are about just over half a million nutrients (between 16 and 200 nutrients per food item) in the nutrients table. The Nutrient Defs table contains correspondingly about 300 definitions of nutrient types. Each nutrient is related to one of the 300 possible definitions: Protein, Total Fat, Carbohydrates, Total Sugars, Vitamin A, Vitamin C, Energy in kcalories, etc. (There is yet another table with weight and volume of the portion of food measured for nutrients, but I've left it off to simplify this example.)

As an example, this is what I'm trying to do, except using FMPro only:

SELECT f.Long_Desc AS Food, d.NutrDesc AS Nutrient, n.Nutr_Val AS Value, d.Units 

FROM foods AS f, nutrients AS n 

LEFT JOIN nutrient_defs AS d ON d.Nutr_No = n.Nutr_No 

WHERE f.NDB_No = n.NDB_No 

AND f.NDB_No = 12014   // pulled from each row in foods

AND d.Units = "kcal"; // added in manually as a filter on the string 'kcal'

This gives me the following list when plugged into mysql:

Food                    Nutrient   Value   Units

Seeds, pumpkin, dried   Energy     559.00  kcal

Obviously, I would narrow my result to just one field, 'SELECT n.Nutr_Val AS Value FROM...etc. ' to get just the nutrient value in kcals. I could then use a calculation field to derive Calories from kcal.

It's not immediately apparent how to create a filtered relationship in FMPro without resorting to portals. When I F1 and do a search in the help file on 'filtered relationship' I'm not able to find anything specific about filtered relationships. When I access the relationships I created to make the portal list, I only see the ability to sort, and create and delete associated records when you double click the relation connection between tables. I'm obviously missing something and would be grateful for suggestions. I'm using FMPro Advanced 11.

[comment]

I am afraid my SQL skills are not good enough to be sure I understand what you are trying to accomplish. I think you want to get the sum of food's nutrients whose unit is "kcal". This can be accomplished in several ways - I'll mention two:

1.

Add a summary field to the 'nutrients' table, defined as Total of [the field that contains the amount of units].

In the 'nutrients' table, find the items of interest: I think in your example you would perform a find for:

nutrients::NDB_No = 12014

and

nutrients::Units = "kcal"

but you could also do this for ALL foods at once, by finding just:

nutrients::Units = "kcal"

and sorting the found set by nutrients::NDB_No. Show the results in a layout of 'nutrients' with a sub-summary part by nutrients::NDB_No and - if you prefer - no body part.

2.

Define a new relationship between 'foods' and another occurrence of 'nutrients' as:

foods::NDB_No = nutrients 2::NDB_No

AND

foods::gUnits = nutrients 2::Units

where foods::gUnits is a global field where you can enter "kcal" to "filter" the relationship. Alternatively, you could use a calculation field with a hard-coded text string for this purpose.

Add a calculation field in the 'foods' table =

Sum ( nutrients 2::Amount ) 

to get the total amount of kilocalories in food.

[Greenman]

Again, thanks for your reply.

Maybe a picture or two will help.

This is the relationship for the tables involved:

The only datum I'm interested in from the Nutrients table is Nutr_Val (or Value from previous posts).

I can only know what the datum is measured in by cross-referencing with the Nutrient_Defs table.

This is easily accomplished using a filtered portal, but I want to wrest and manipulate specific values out of that portal list.

This is the database with its filtered portal showing specific nutrients for each food:

Each unique food by NDB_No will have only one item from the Nutrient_Defs table measured: total fat, total fiber, etc.

Unless I've misunderstood your reply, a summary field to the nutrients table may not actually produce the result I'm looking for.

I'd like to derive field "Calories" (kcal/1000) from the Energy in kcals and use "Calories" from each food in further calculations in the foods table.

I attempted creating a calculation field in Foods table like this:

Field Calories =

If ( Nutrients::NDB_No = NDB_No 

and 

Nutrients::Nutr_No = Nutrient_Defs::Nutr_No 

and 

Nutrients::Nutr_No = 208; 



Nutrients::Nutr_Val/1000;

"")

When I try to change it to a stored calculation, I get this:

[comment]

If you are talking about the USDA National Nutrient Database for Standard Reference, then there is only nutrient_def record that has kcal as the units - I believe its Nutr_No is "208".

So, for the first option, perform a find (in the Nutrients table) for:

Nutrients::Nutr_No = "208"

instead of:

Nutrients::Units = "kcal"

which is not possible. You could also search the Nutrients table for:

Nutrient_Defs::Units = "kcal"

but this will be slower.

For the second option, make the relationship:

Foods::NDB_No = Nutrients 2::NDB_No

AND

Foods::gNutr_No = Nutrients 2::Nutr_No

and enter "208" into the global field.

[Greenman]

Comment -

Thank you.

Ironically, I just realized that kcalories = Calories.

However, I will absolutely still need to base other calculations on the isolated value of kcalories, for instance, adjusted Calories based adjusted weight/volume, etc.

Option 2 is most suitable for my needs.

Cheers!

Chris