From here, it seems like you have Products and Recipes as two tables where they could actually be a single table--simplifying your datamodel by one "occurrence box" in your relationship graph,
But whether or not you make that change, you can use Go to Related records from your Order layout to pull up a found set of RecipeLines records on a layout based on RecipeLines. You can then sort these records, if desired, to group them by MaterialID so that each item is listed only once and a combination of summary fields and calculation fields can compute combined quantities for each material specified in the recipe for an ordered product.
I will try to use the function to get the recipelines in relation to orderID in the new layout and then to make the calculation via the formula:
recipeLineQty / recipeSum * orderQty and I hope it works.
Thanks for the hint!
One more thing I cannot find to get relation.
Should I make some additional filed in recipeLines?
The system do not know in any way how to link the recipeLines with the order. The connection is 2 tables away
in ORDERS there is a connection to PRODUCTS via join table ORDERLINES. The product keep the connection to RECIPES. But there is no relation between RECIPES (and RECIPESLINES) and order.
RecipeLines is already linked to orders in a relationship according to your description of your database. Go to Related Records is not limited to those table occurrences immediately connected to your layout's table occurrence. You can use it to refer to RecipeLines from your Orders layout to pull up all related recipeLines records so long as you have the chain of relationships as you have described.
It is working! I need to play with recalculations based on the quantities in OrderLines!
Is there any chance to populate the quantity from OrderLines to RecipeLines? If I have this value I can make a recalculation of material needed.
Each record in RecipeLines is not specific to any one order.
We'd need to use either ExecuteSQL or set up an additional relationship between RecipeLines and a new occurrence of OrderLInes such that a summary field or Sum function can compute the total quantity needed for that one RecipeLines specified material. Neither option will be particularly easy to do.
The relationship method would require that you use a script to populate a text field in RecipeLines with the IDs of every record in OrderLInes that specifies a product that links to that recipelines record.
Hmm, I thought it would be easier (like calculation field based on the field from reated table via relation graph)
exactly it seems to be a hard way. Even the additional relation is hard (no specific field to make relation. The only way would be to replace the product with the recipe (not to order the product but to order the recipe). But the thing is I made such structure in Ms access and it worked. Here I do not have an idea how to move on.
Any way I appreciate your help and patience.