9 Replies Latest reply on Jul 26, 2011 8:02 AM by philmodjunk

    Dynamic use of layout fields.

    KarlBrowning

      Title

      Dynamic use of layout fields.

      Post

      I've been unable to figure out how to continue to be lazy.

      Situation:

      I have a table with a very long list of computed fields.  The best I've been able to figure out to-date is write a long list of script commands to act on each field independently.  Lots of coding.  Lots of maintenance opportunities.

      Would like something of the nature of:

      Get the list of fields from a layout

      loop (for the number of fields in the list)   exit when complete

      set valueof_thisField (loopcount) to ( valueof_thisField (loopcount) * 110% )

      increment loopcount 'til done

      Get (Fieldname) seems to maybe? get me there but I do not see how to parse it in the fashion I described above.

      Any help you are able to offer is sincerely appreciated.

      Karl

        • 1. Re: Dynamic use of layout fields.
          philmodjunk

          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.

          • 2. Re: Dynamic use of layout fields.
            KarlBrowning

            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.

            • 3. Re: Dynamic use of layout fields.
              philmodjunk

              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.

              • 4. Re: Dynamic use of layout fields.
                KarlBrowning
                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?
                • 5. Re: Dynamic use of layout fields.
                  philmodjunk

                  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:

                  Recipes---<RecipeItems----<Nutritional Values

                  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.

                  • 6. Re: Dynamic use of layout fields.
                    KarlBrowning

                    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.

                    Karl

                    • 7. Re: Dynamic use of layout fields.
                      philmodjunk

                      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.

                      • 8. Re: Dynamic use of layout fields.
                        KarlBrowning

                        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.

                        • 9. Re: Dynamic use of layout fields.
                          philmodjunk

                          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'.