I apologize if this has been answered, but I can't find it.

I have a file that uses a record ID and date. I have a self join relationship for matching record IDs and the date sorted as desc.

FileName = Fiber

Relationship = Fiber Shearing (match to Record ID in Fiber, sorted by date desc)

Example:

Fiber Shearing::Date in current record = 7/1/09

Fiber Shearing::Date in prior record = 6/5/08

I need a calc field (Prior_Date) to retrieve 6/5/08 in the current record.

I tried:

GetNthRecord ( FIBER Shearing::Date_Shorn; Get (RecordNumber) + 1 )

but that does not use my sort and retrieves the newer date into the older record.

I know this has got to be simple, but I can't seem to get there.

Thanks.

I assume that "Record ID" is some sort of category field, and not a unique identifier as the name would suggest?

If so, you should make your relationship follow this pattern:

Table::Category = Table 2::Category

AND

Table::Date > = Table 2::Date

Now if you sort the related records by Date, descending, the previous record will become the first related record and you can refer to it directly (assuming no two records in the same category have the same date).

That did it... I knew it had to be easy.

Thanks.