Jump to content

Calculation to show prior record


This topic is 5696 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 5696 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.