In your PRODUCTS table, define a Calculation field c6MonthsAgo =
today = Get (CurrentDate)
Date ( Month ( today ) - 6 ; Day ( today ) ; Year ( today ) )
Set the calculation's result type to Date, and the field's storage to "Do not store…" (NOT global).
Define the relationship (using another occurrence of PURCHASE_DETAIL) as:
PRODUCTS::ProductID = PURCHASE_DETAIL 2::ProductID
PRODUCTS::c6MonthsAgo ≤ PURCHASE_DETAIL 2::PurchaseDate
A calculation field (in PRODUCTS) =
Average ( PURCHASE_DETAIL 2::PurchasePrice )
should give you the average price in the last 6 months.
Thanks for your reply - I was on the same path, just a slightly more round about way - ie reducing all dates to number format
Suffice it to say
The let function populates the date as of 6 months ago, and all the other bits fit together - just when I filter the relationship with the
Just created a test layout with the relevant fields - it seems the problem is one of Indexing - but as yet I am not sure how to fix this - I have done the obvious and tried to force indexing on the fields - but the calc fields that refer to related tables can't be indexed??? Any thoughts
FMP 10 (Mac SnowLeopard)
Please check that:
1. The field PURCHASE_DETAIL::PurchaseDate is a Date field.
2. You do have purchases of the selected product in the last 6 months.
This is the summarised setup of the 3 tables
product id (autoenter)
product other info
Date 6 months ago (calc results in date- as per your 1st reply)
6 month average - calculated reffering to the Cost per RecipeUnit in the TO PURCHASE_DETAILPricing
<<related one to many>>
purchase detail id(autoenter)
fk product id
fk purchase id
qty packs bought
cost per unit - calculated from pack size, unit and price
purchase date - calculated from PURCHASE :ie: PURCHASE_DETAIL::purchase date = PURCHASE::Purchase date (results in Date)
<<related many to one>>
I have set up a layout with the following fields
PRODUCT_date 6 months ago
PRODUCT_6 month average
PURCHASE_DETAIL_cost per unit
This displays as a list, all instances of all products purchased their dates of purchase and cost per unit - all correct for the small amount of test data I have
The 6 month average just displays an average of ALL the instances of a particular product
if I set up the filter as suggested above - the list "looses" records from the PRODUCT side"correctly" ie according to the date filter,AND the 6month average feild is empty accross all records, but the corresponding records from the PURCHASE_DETAIL and PURCHASE tables are sitll visible
(I have tried the filter from the other side of the relationship, and that "looses" records from the PURCHASE table correctly, with no influence on the PURCHASE_DETAIL records.
Hope that gives some insight - I have probably committed a horrible FM crime to create this odd situation?????
Thanks so much for the insight and guidance!!!! I am now going to try the only scenario left - and that is to try and filter the PURCHASE_DETAIL table through itself.........
FMP 10 (Mac SnowLeopard)
Here's the problem: the field purchase date in PURCHASE_DETAIL is an unstored calculation, and cannot be used as the matchfield on the "other" side of a relationship.
It would probably be best to change this field to type Date and have it lookup the date from the parent purchase order. There are other methods, but I believe this would be the simplest (and fastest in performance).
There is one issue with this workaround, though: due to a Filemaker buglet, a lookup from the parent record doesn't work if the parent hasn't been committed. Make sure to commit a new order before you start creating the line items.
You are the MASTER!!!! - thank you for sticking with this one
The commit buglet is no problem as the prices are not going to be updated at short intervals, so they'll just update in the background and track changes to recipe cost over time. I have put in a script trigger to commit the PURCHASE prior to entering its line items to cover all bases.
on the same scenario, could you provide insight as to how to return the price per smallest unit from the last purchased instance of the product? ie - there'll be 2 prices stored in the PRODUCT table, the 6 month average and the price based on the price of the product when it was last purchased - for some products that will be a day ago, and for some a month or so ago?
Cheers and once again THANK YOU
I am afraid I don't quite follow: I believe there is only one "last purchased instance of the product" - it can be accessed through the Last() function (provided that records are entered in chronological order, and that the relationship is not sorted otherwise).
Nope, you did get it - I can just use the original relationship I use for entering purchases sorted by purchase date and use the Last() function to return the last entered purchase of the related product.
Brain fried with a day of trying to get the 6month ave to work.
Once again - many thanks for your input - very much appreciated