A Troubling Relationship

[Jason Hefley]

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

 

Jason

 

 

 

 

 

 

[comment]

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.

 

Do you intend to calculate the cost of an ingredient by taking into account all purchases of that ingredient - no matter how far back?

 

 

---

For my own reference:

recipes -< recipeingredients >- ingredients -< purchases

[Jason Hefley]

Wow great question....I think the answer is yes.  As I purchase product the weighted average will change, and I am ok with that.  To answer your question, yes.

[comment]

Well, then assuming that the purchases table has a cost_per_pound field, define a calculation field in the ingredients table =

Average ( purchaseorder::cost_per_pound )

Then add a calculation field in recipeingredients that multiplies the required quantity by the average cost field from ingredients.

 

 

To clarify, summary fields summarize the found set. In order to do calculations with aggregated related data, you need to use aggregate functions instead (with some exceptions - but I am trying to keep this as simple as possible).

 

---

Alternatively, you could do a single calculation in the recipeingredients table =

Quantity * Average ( purchaseorder::cost_per_pound )

I think (I am doing this in my head, so untested).

Edited December 27, 2013 by comment

[Jason Hefley]

I understand that now, but now trying to use aggregate functions, it show that the calculation is unstored.  How do I change this as it is  giving me the same result (blank field)

[Jason Hefley]

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."

[Wim Decorte]

That's normal.  Because the purchase orders can change at any time, the calc field needs to update whenever needed.

 

If you want the field to be stored (and get better performance) then you can set it as part of a scripted workflow when new purchase orders are entered.

[Jason Hefley]

I am pretty new to this so I apologize for my ignorance, but I have no idea what you are talking about.   When entering date on a purchase order, I should have a script that runs the calculation and it will become stored data that can be later used in calculations.   

[Wim Decorte]

Let me take one step back then: you mentioned that you want to change the field from "unstored" to "stored".  Why is that?

[Jason Hefley]

In the Recipe table I am trying to make it so when I create a recipe it calculates the cost of said recipe.   I must use a weighted average of all the purchase I make of a given ingredient as the price of ingredients change over time.   

[Wim Decorte]

That does not answer my question about why you were wondering repeatedly how to make the file stored and not unstored.

 

Stored or Unstored, FM can work with the result.  Depending on your design and the # of records involved, using an unstored calc may carry a performance penalty (FM has to calculate the result whenever you need it, instead of just reading the stored result).

[Jason Hefley]

The problem I am  have having, or maybe this is my misunderstanding, is that when I am trying to use the weighted average for a given ingredient's price the result is zero.  

I have a layout that is all the ingredients used, and the all the purchases of that ingredient.   I have a subsummary part that calculated the weighted average of all the transactions of a particular ingredient based on the quantity of the ingredient and the actual purchase price.   I am then trying to take that subsummary part using the getsummary function and multiple it by the amount used in a recipe to give me the cost of making a given recpe.  I think my biggest misunderstanding is how to relate the tables and what function to use.  

[Rick Whitelaw]

Are you trying to use an unstored field as a match field on the child side of a relationship?