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.
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?
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.
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
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.
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.
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.
Works perfectly with a script to commit the record on modify, thanks! I'm developing more questions so stay tuned ^_~