8 Replies Latest reply on Nov 13, 2013 10:30 AM by SkippDink

    one works but the other does not... Table relationships

    SkippDink

      Title

      one works but the other does not... Table relationships

      Post

           I'm Still Pretty new but I have a database with three tables organized as such

           Pricelist>-----<Recipe_Ingredients>----Recipes

           with match fields being:

           Pricelist : item = Recipe_ingredients : Ingredient

           Recipe_ingredients : Recipe_id = Recipes : Recipe_id

           I use a portal to create recipes in my layout

           I have Recipes : Recipe_id set to auto generate a serial number and all ingredients in the portal successfully copy the value from Recipes : Recipe_id to Recipe_ingredients : Recipe_id using a setvariable script.

           The tables Pricelist and Recipe_ingredients also contain the fields "Ea" , "floz" and "#" I am able to populate these fields in table Recipe_ingredients using a lookup.

           this is all perfect.

           the problem fields are my yield and desired fields. 

           for each ingredient I add to a recipe in the portal i want it to copy these to pieces of information from the main recipes table for price calculating purposes but a lookup results in no match.

            

           is the problem my   ">---" (many to one?) relationship on the last two tables? how can I change that?

           also when I am adding data to my fields, the one with calculations do not automatically update as information changes or becomes available. first things first though.

        • 1. Re: one works but the other does not... Table relationships
          philmodjunk
               

                    the problem fields are my yield and desired fields. 

               

                    for each ingredient I add to a recipe in the portal i want it to copy these to pieces of information from the main recipes table for price calculating purposes but a lookup results in no match.

               I suggest posting an example of the type of calculation that you are trying to get to work.

               

                    when I am adding data to my fields, the one with calculations do not automatically update as information changes or becomes available.

               Just guessing here, but that sounds like you have a field with an auto-entered calculation where the field should, instead be a field of TYPE calculation. Number or text fields with auto-entered calculations will not update when date from a related table is changed. Fields of type calculation will update in those circumstances.

          • 2. Re: one works but the other does not... Table relationships
            SkippDink

                 You were absolutely right about the calculation fields! Thanks.

                 So I am looking at the lookup fields "yield" and "Desired" it seems as though the lookup does work but the it is executing before the record has been assigned the recipe id (or simultaneously?) so when I look in table view the correct recipe id has been assigned to the ingredient but the yield and desired fields are not correctly populated unless i remove and re-enter the data in the recipe id field. is there a way to ensure the lookups in those fields occur after the ingredient has the id assigned?

            • 3. Re: one works but the other does not... Table relationships
              philmodjunk

                   Please describe how the Yield and Desired fields need to work. I am guessing here, but it seems that you have number fields for this data defined in recipe and fields in RecipeIngredients that looks up (copies) that data from Recipe.

                   If so, that's not a good way to set this up as it requires that you enter data into these fields before adding your related records in RecipeIngredients. If you then change the value in either field in Recipe's, the updated values will not appear in the recipeIngredient fields.

                   But you can define your calculations in the recipeIngredients table to be fields of type calculation that refer directly to the Yield and Desired fields in Recipes. Then the values returned by the calculation fields will automatically update to refer to the current data in these fields.

              • 4. Re: one works but the other does not... Table relationships
                SkippDink

                     What should happen:

                     I have a layout, at the top is "recipe name", "yield", "yield unit" and "desired quantity"

                     below that is a portal in which everything fills out properly when adding ingredients except the yield and desired fields (which do not show in the layout). all that needs to happen is for them to copy the data from "Recipes : Yield" and "Recipes : Desired Quantity" respectively

                     within the "Recipe_ingredients" table I intend to use these fields to calculate scaled amounts of individual ingredients and costing information which is also correctly populating from the price list. 

                     currently, I have "Recipe_ingredients : Yield" set as a calculation as follows "= Recipes : Yield" 

                     this seems to work sometimes but not consistently. the lookup worked like I said before but only if I re-entered the recipe_id data

                • 5. Re: one works but the other does not... Table relationships
                  philmodjunk

                       Then my answer in the previous post is the correct answer.

                       Remove the "yield", "yield unit" and "desired quantity" fields from the recipe ingredients table. In calculations defined in RecipeIngredients, make sure that they are of type calculation, not auto-entered calculations and then refer directly to the fields from Recipes in order to access the current data in those fields.

                  • 6. Re: one works but the other does not... Table relationships
                    SkippDink

                         Thanks Phil you're always super helpful!

                         So, this works for the most part. 

                         When I am first entering a new recipe the fields "scaled amount", "Serving Cost" and "Recipe Cost" in table "Recipe_ingredients" all show question marks until I navigate away from the recipe entry page. However, when I return, they are all properly calculated. this is however only a minor annoyance.

                    • 7. Re: one works but the other does not... Table relationships
                      philmodjunk

                           Presumably you are dividing by a value from Recipe that is blank or zero until you commit the record--which will happen when you leave and return.

                           Try just clicking a blank area on the layout outside the portal and see if your fields update correctly.

                           The calculations could be modified with if statements to not return a value if the divisor is zero or empty. That's purely a cosmetic fix.

                           If clicking the layout background updates the fields, you could put script triggers on the recipe fields that commit the records automatically when you edit the value in the field.

                      • 8. Re: one works but the other does not... Table relationships
                        SkippDink

                             Works perfectly with a script to commit the record on modify, thanks! I'm developing more questions so stay tuned ^_~