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