Hi everyone,

 

Hi have 3 tables:

 

Employees:

EmpID: number

First: text

Last: text

 

Salaries:

Salary: amount in dollars

Date: this is either the hire date or the last increase.

EmpID: number

 

Years:

Year: number

EmpID: number

 

On the Years layout I need to see for the given year how much a certain employee is making. So basically I need lookup the salary amount based on the nearest year to the current...

 

Let's say EmpID 1, John Doe started in

2007 with a salary of $25,000

then

2009 got a raise to $31,000

finally in 2011 got another raise to $34,000

 

If I look at the year 2013 for EmpID I need to get to $34,000 which is the last raise before 2013.

 

This is part of a much more complicated database, but the missing thing for me is how to get to the latest nearest year from a list or a bunch of related records...

I don't know how to recursively test inside a calculation field in FM.

 

Thx for your help!

Federico

 

On the Years layout I need to see for the given year how much a certain employee is making. So basically I need lookup the salary amount based on the nearest year to the current...

 

This is a question of relationships, not calculations. However, first you need to clarify what should happen if an employee has had a raise in the given year. Or two raises in the same year.

Thank you!

I'm reposting this into relationships.

Two raises in a year cannot happen.

If the employee had a raise in the given year,then that's the salary that should be displayed...

Thx again.

Federico

Here's one way it could be done:

 

1. In the Years table, define a calculation field cLastDay (result is Date) =

Date ( 12 ; 31 ; Year )

2. Define this relationship between Years and Salaries:

 

Years::EmpID = Salaries::EmpID

AND

Years::cLastDay ≥ Salaries::Date

 

In the relationship setup, sort the records from Salaries by Date, descending.

 

 

3. Place the Salary field from Salaries on the layout of Years. It will show data from the first related record in Salaries, which will be the latest amount that was recorded before the end of that year.

Hi fex, and welcome to the forum.

 

I moved your thread from "Calculation Engine (Define Fields)" to "Relationships” as you requested.

 

Please do not double post your questions. As comment posted in your other thread, just contact one of the moderators if you want something like done.

Thank you comment!

It works perfectly!

I had done but was sorting the relationship as ascending!

Thanks again.

Really appreciated.

Federico