How to retrieve previous record by date

[stockingup]

Hi, Its been a while since I have posted, and I like to think I've learnt quite a bit since I last did! However, I am trying to solve a problem which I just can't seem to get my head around. I have read through most of the Filemaker 9 Bible, but still can't seem to solve this...

Essentially, I have an events table which logs every time a customer is visited - with fields including Date and Event Description (among others). This table (called Events) has a portal showing every machine located at that customer - the portal points to "machines_Line_Items". This table in turn is linked to a table called "Cash" by a many-to-many relationship with the fields "Service_ID", "Customer_ID" and "Machine_No". My problem is as follows:

On this cash table I show each product in that machine (its a vending machine), the "price" each product is sold at, the "current level", and the "previous level".

However, I need the "previous level" to be retrieved from the previous visit by date. For example, if today is the 13th, and the last time we visited that particular customer was on the 5th, I need it to get the "current level" from the 5th and display that as the previous level for the 13th. How can I do this? My problem is retrieving the previous record information when sorted by date, considering the "Cash" table has records for every product, in every machine, for every customer, on every visit...

All help is greatly appreciated, and I thank you for your continued support!

Cheers,

[mr_vodka]

Here is s hint. You may want to look int othe GetNthRecord function in conjunction with a self join.

[stockingup]

Thanks for the reply... I had been looking into the GetNthRecord() function but I can't seem to identify what to set as the record number... I have a self join with a less than relationship on the date field, so it relates to any records from the same customer, for the same product, in the same machine, but not for the current visit.

however, how do I retrieve the level from the most recent record (using a calculation)?

Thanks again for the reply.

[stockingup]

Nevermind! figured it out. I set it to a calculation field GetNthRecord(fieldname; 1), created a self join only related to any records with a date prior to the current record, and sorted them by date ascending.

Thanks again for your help B)

[comment]

If you sort your related records by date, descending, then the previous visit will become the first related record - so you can refer to it directly in a calculation.

[stockingup]

Yep, thats exactly how I managed. thanks for the advice.