A troubling Relationship..maybe?
I am pretty new to Filemaker so I would like to apologize ahead of time..
I am trying to learn Filemaker the old fashion way, and just diving into it and asking questions when I get stuck.
I am making a data base that I plan on tracking expenses for BBQ. In this data base, I have a table called ingredients that list of all ingredients that I use. It is the parent to a table I call "recipeingredients". (ingredientid -> ingredientidfk). I also have another table called "purchaseorder". This table is a list of transactions of buying ingredients. I know that prices change over time, so this table has a list layout of all purchases sorted by ingredient with a subsummary of the weighted average of the list of ingredients. It is also the child of the ingredient table. (ingredientid -> ingredientidfk). I have another table called recipe. Lastly I have a table called recipeingredients that is a list view and sorted by recipe name. I have a relationship between recipe and recipieingredients (recipeid -> recipeidfk). I have another relationship between "purchaseorder" and recipeingredients. I have attached a screen shot to make sure I explained that correctly.
My goal is create a field in the recipeingredients table that calculates how much each recipe costs to make. I must extract the subsummary data from the purchaseorder table and multiply it by the amount used in each ingredient for a given recipe and total the value for each recipe. I have attempted to use the "getsummary" function as shown: (GetSummary ( purchaseorder::Avg Cost per Pound ; purchaseorder::Ingredients )) * amount. When I do this, the only number I get is 0. I am posting this here as I think I have a relationship problem...
I may be way off here, so please help