If you put all of these fields on a layout (can be layout just for this script and it can be kept hidden from the user), you can use go to next field to loop through the fields on the layout and then a combination of GetField, Get ( ActiveFieldContents), Get ( ActiveFieldName ), Get ( ActiveFieldTablename ) and Set Field By Name can be used to produce what you describe.
There may be a better way to do this if you were to restructure your table(s). I don't definitely know that from such a generalized post as this, but scripts of this type often can be symptomatic of a need for a different design approach.
Thanks for the ideas. I will try it. I would love for a better design solution. Let me see if I can explain what I have for your thoughts.
Table: Nutritional Values for a given Food Item. For example a Banana. The fields that describe the item's nutrition include calories, protein, fat, vitamins, micronutrients, etc (most come from USDA research tables).
Table: Recipe. A recipe is a collection of Food Items. Each table entry identifies the Food item, Food Item Qty (servings) in the recipe and the number of servings for the recipe. A recipe also is a Food Item and is present in the Nutritional Values table.
Eg: Recipe Table entries:
Recipe Name: Fruit Bowl Servings: 2 Food: Banana Qty: 4
Recipe Name: Fruit Bowl Servings: 2 Food: Strawberry Qty: 4
Nutritional Values Table entries:
Banana nutritional values per serving
Strawberry nutritional values per serving
Fruit Bowl (each nutritional value is accumulated from the primary (banana and strawberry), divided by the number of servings. Net: nutritional values for a serving of Fruit Bowl.
Therefore in order to compute the the Nutritional Values entry for Fruit Bowl, one must add the sum of the ingredients. There are 37 different nutritional values for each food.
My original question was looking for an easier way to accumulate each nutritional value on each food item. My original implementation literally added each of the 37 items line-for-line. I am now revisiting this db and would like to reduce the maintenance.
Can you give an example of the "37 different nutritional values" and how they would be summed into a single value here?
I would think you could set up a calculation that correctly computes your totals for each recipe without the need for any such scripting, but as always, the devil is in the details and I don't quite see how/why a single food such as a banana would have "37 different nutritional values" that you'd sum up into a single value for the recipe.
Not a single number. A number for each nutrient. In the Fruit Bowl example we need the calories per serving, protein per serving, etc. So when the user selects the Food Item Fruit Bowl we have the 37 nutritional values for Fruit Bowl, each of which is the the sum of the individual nutrients for each of the Food Items in the recipe. Make sense?
Ok, but how do you link the individual recipe records to these values? Do you mave multiple records in "Nutrional Values" that would all link, say, to "banana" and another set that links to "strawberry"? Or just one record for banana with multiple fields (one for potassium, another for iron, etc.) for each nutritional item?
Either way, I'd set up this relationship:
One record in Recipe links to the individual food items in RecipeItems, each of which then link to data in Nutritional Values.
Using your example, one record in Recipes would stand for "Fruit Bowl" and it would link to two records in RecipeItems (banana and strawberry). These, in turn would link to the nutritional value data.
Sum functions in Recipes can then compute total nutritional values for the entire recipe.
Actually, the relationship you suggest is the one that exists.
For each recipe there are only 4 fields: Recipe Name (relates to Nutrition Table Food Item);
Number of servings (for the recipe)
Ingredient (food item found in the nutritional values table)
Quantity of the Food item (in the recipe).
The nutritional values table is the only place that has all 37 nutritional values in each food item. It is the core table to look up nutritional values.
The original post was trying to figure out an easy way to create a Nutritional Values record (for a RECIPE) with the sum of each of the nutrients. There are 3 different ways to create a Nutritional Values item, Recipe is one of them.
So, I think I will take a shot at your original suggestion. While I haven't researched how to use each function, you've given me enough to make a run at it.
I appreciate your inputs. As, I've told you before - I think you bring a great deal of level-headed problem solving.
I don't think so.
You seem to be combining data from Recipes with data from RecipeItems.
Number of servings for the recipe would be in Recipes. Ingredient would be found in RecipeItems. Quantity of the food item would be in RecipeItems and not in Recipes. In Recipes, you have one record for every recipe. In RecipeItems, you have one record for every food item listed in that recipe.
What you are describing as Recipes is really an entry in Nutritional Values (it becomes a single food). (technically, the only relationship between the two tables - Recipes and Nutritional Values exist at the time of creation of a Food Item (recipe) in Nutritional Values. There is no on-going relationship).
What makes this confusing is that Recipes is something of a stand-alone function. The whole idea is to accumulate nutritional values for each of the ingredients (food items in nutritional values) in a recipe so that a recipe can become a food item in nutritional values. As one-off process. (End Product: Nutritional Values is THE list of available foods that are selected by a user).
I am sure that my inadequate descriptions are causing much confusion. If you are willing, able, and have the desire, I am happy to provide a copy of the database to you (even though I am still working out kinks). I will put it in a dropbox and send you a link (will need an e-mail address). It will not hurt my feelings if you choose not to. I remain grateful for your ideas and inputs.
If I understand the concept, it makes sense. A recipe can itself be used as a food item in another recipe?
Keep in mind that you've described very little about the structure of your database. I'd still use the structure I have recommended, it's just that nutritional values and Recipes can be different table occurrences of the same data source table. And the data stored in nutritional value can be looked up (copied) or dynamically referenced from other records in the same data source table via a 'self join'.