AnsweredAssumed Answered

dynamic calculation based on dates held in a related table

Question asked by jms_1 on Apr 29, 2010
Latest reply on Apr 29, 2010 by jms_1

Title

dynamic calculation based on dates held in a related table

Post

Hi,

 

Not sure the title is all that accurate???

 

I am new to FM, but learning FAST!!!

 

My current problem is as follows

 

I have a PRODUCTS table that holds info about the products I purchase, and I have a PURCHASE_MAIN table that holds the main info about a purchase (Who it was from, Date purchased, Date paid, total tax for that specific purchase)

 

These tables are joined with the PURCHASE_DETAIL table - that holds the info on what products were purchased and for how much - it also holds an unstored calc to reduce the price paid for a product to the price / smallest unit for that line(record)

 

What I want to do is populate the Products table with a 6month Ave, 3month Ave and Latest price per smallest unit - based on the history in the PURCHASE_DETAIL table - the reason for this is I then want to calculate a RECIPE price based on the amount of each product used in a recipe.

 

I have created a global Get(CurrentDate) field g_prodmain_currentDate and then a prodmain_6monthdate(Current date number - 182) field both are numbers. I can get an average based on all instances of "productX" held in PURCHASE_DETAIL, but I can't seem to limit the average calc to purchase dates that are > the 6 month date (I have also created a field in PURCHASE_MAIN, that is the number version of the date.

 

I would like to do this via a calc at table level, as I want the price info to be dynamic and not have to have something like a "update prices" script.

 

Any pointers would be greatly appreciated.

 

Thanks a mil

Mark

FMP 10

 

 

Outcomes