Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×
The Claris Museum: The Vault of FileMaker Antiquities at Claris Engage 2025! ×

get the closest number to a given one from a list...


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

Recommended Posts

  • Newbies
Posted

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

 

Posted
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.

  • Newbies
Posted

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

Posted

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.

  • Like 1
  • Newbies
Posted

Thank you comment!

It works perfectly!

I had done but was sorting the relationship as ascending!

Thanks again.

Really appreciated.

Federico

This topic is 3968 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
×
×
  • Create New...

Important Information

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