3 Replies Latest reply on Dec 29, 2013 8:08 AM by philmodjunk

    A troubling Relationship..maybe?



      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


           Hafa Adai





        • 1. Re: A troubling Relationship..maybe?

               Your relationships look fine, but getSummary cannot be used in the context in which you are trying to use it and thus you get a zero result. You'll need to use a different method to compute your costs.

               Take a look at aggregate functions such as Sum, Count and Average. They can be used to compute aggregate values of related data from the context of RecipeIngredients. Sum ( purchaseorder::amountPaid ), if defined in RecipeIngredients would produce a total of all amounts paid for that ingredient.

               You've indicated that you are computing a weighted average so you probably cannot just use the Average function for this. Exactly how you go about getting the desired total will depend on how you are weighting your average.

          • 2. Re: A troubling Relationship..maybe?

                 When trying to change the calculation using an aggregate function it makes it an unstored field.  I am using the calculation of:


                 Sum (purchaseorder::Amount Paid)/Sum (purchaseorder::Cost per Pound)


                 It classifies it as an unstored field.   I attempted to change the field to an stored field and it gives me the following error:


                 "The calculation “total cost” cannot be stored or indexed because it references a related field, a summary field, an unstored calculation field, or a field with global storage."

            • 3. Re: A troubling Relationship..maybe?

                   That is correct. A calculation such as this which references a field in a related table cannot be a stored calculation. What problem does this create for you?