Accessing records by date

[emerywang]

Hi guys. How can I make a calculation that accesses a previous record by date? In my database, I make a new record once a month, to update the price of an item. I need to compare this price to what it was in December of the previous year. So for example, from Jan. 2003 thru Dec. 2003, I need to compare the price of an item that month to its price in Dec. 2002. Then from Jan 2004 to Dec. 2004, I need to compare it with its price in Dec. 2003, and so on.

So how do I tell Filemaker to make a calculation based on the current value in a field, compared to its value from a specific previous record (the December record of last year)?

Thanks!

-Emery

[ESpringer]

The key is in the fact that these are price records for the same product, right?

I assume you have a ProdID field, and also a Date field for the start of the month for each record (like 11/1/2003).

You need a calculated text field that includes the product name together with the date (start of month)

ProdID&"_"&DateToText(Date)

Then, each month's record should include a calculation (call it cLastDec1) designed to show which record it will be compared to:

ProdID&"_"&DateToText(Date-(DayofYear(Date))-30)

So every record will include a field with its product ID and the most recent Dec 1.

Then set up a self-join relation between each record and the record indicated by its cLastDec1 field.

-ESpringer