Calculation to show prior record

[AlanP]

I hope someone can help me on this:

I need to have a field calculation that shows the PRIOR record in a particular field. For example in table 'X' field 'PAYMENTS' has three records showing:

1

2

3

with '1' being the most recent

I need to have this field calculation display '2' since it is the second most current.

Also, I can't modify my relationships because of how integrated the database is now.

Thank you in advance to all who can help me out!!!

[comment]

You need either a relationship - or version 8.

[AlanP]

You need either a relationship - or version 8.

Our IT won't allow us to buy filemaker at all anymore so we are stuck with 7.

As far as relationship, ok, how can I do this? Any type of relationship and calculation I'm familiar with will only show the last new record but not the second to last new record.

I appreciate the help

[comment]

There are several ways to do this. Can you provide more details about your structure and the purpose of this requirement?

[AlanP]

Absolutley, thank you!

I need to show on a report what an employees prior assignment was (table: EMPLOYMENT HISTORY) in field EMPLOYMENT HISTORY::Assignment.

This report is run after we update the employees assignment. We need to run this report to update their security levels in a seperate mainframe system (we see where they are now compared to where they were).

There are several related tables in this database, however most all layouts use table: PERSONNEL FILE. (for example when we do updates to assignment, it is via a portal showing records in EMPLOYMENT HISTORY. The report, however, is showing records directly from EMPLOYMENT HISTORY.

[comment]

Well, of course, if you only need this for a report, you could do it by script - no additional relationship required. Roughly, the script would:

Go to Related Record [ EMPLOYMENT HISTORY ; Show only related ]

Sort Records [by Date, descending ]

Go to Record [2]

Set Field [ PERSONNEL FILE::PreviousAssignment ; EMPLOYMENT HISTORY::Assignment ]

...

(I presume there IS a date field you can use for this; if not, use an auto-entered serial number field instead.)

But if you want a "live" calculation, define a relationship between EMPLOYMENT HISTORY and a new occurence of the same table (i.e. a self-join), which I will call PREVASSIGNMENT:

EMPLOYMENT HISTORY::EmployeeID = PREVASSIGNMENT::EmployeeID

AND

EMPLOYMENT HISTORY::Date > PREVASSIGNMENT::Date

(Same note here about the date field.)

Sort the relationship on the PREVASSIGNMENT side by Date, descending.

Go to Layout mode, and place the field PREVASSIGNMENT::Assignment on the layout of PERSONNEL FILE.

[AlanP]

Wow thank you! This works great!

Now that you explained it, this makes sense .

Have a great weekend!