9 Replies Latest reply on Apr 29, 2010 12:30 PM by jms_1

# dynamic calculation based on dates held in a related table

### 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

• ###### 1. Re: dynamic calculation based on dates held in a related table

In your PRODUCTS table, define a Calculation field c6MonthsAgo =

Let (
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
AND
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.

• ###### 2. Re: dynamic calculation based on dates held in a related table

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

PRODUCTS:

• ###### 3. Re: dynamic calculation based on dates held in a related table

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

Cheers

Mark

FMP 10 (Mac SnowLeopard)

South Africa

• ###### 4. Re: dynamic calculation based on dates held in a related table

1. The field PURCHASE_DETAIL::PurchaseDate is a Date field.

2. You do have purchases of the selected product in the last 6 months.

• ###### 5. Re: dynamic calculation based on dates held in a related table

This is the summarised setup of the 3 tables

PRODUCT_MAIN

product id (autoenter)

product name

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

purchase detail id(autoenter)

fk product id

fk purchase id

pack size

pack unit

qty packs bought

line price

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

PURCHASE

purchase id(autoenter)

purchase date

company

VAT total

Invoice Nr

I have set up a layout  with the following fields

PRODUCT_product name

PRODUCT_date 6 months ago

PRODUCT_6 month average

PURCHASE_DETAIL_cost per unit

PURCHASE_DETAIL_purchase date

PURCHASE_purchase date

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

cheers

Mark

FMP 10 (Mac SnowLeopard)

South Africa

• ###### 6. Re: dynamic calculation based on dates held in a related table

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.

• ###### 7. Re: dynamic calculation based on dates held in a related table

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

Mark

• ###### 8. Re: dynamic calculation based on dates held in a related table

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

• ###### 9. Re: dynamic calculation based on dates held in a related table

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

cheers

Mark