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


dynamic calculation based on dates held in a related table




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


FMP 10