Relationship Join Help

[TulatinChris]

I want to make a link between a calculated field in one table to a regular field in another. Can anyone help? As a M$ SQL guy, I’m used to writing raw SQL which can join unrelated tables to get at what I need. It doesn’t appear that’s available for Filemaker though, but there must be some approach that’ll work.

Here’s the business logic behind this: An employee can work for months or years, leave and come back. Employee seniority is determined by adding all hired/terminate date time spans together. Depending upon number of years served, each employee receives an award for time worked (5 year award, 10 year award, etc.)

I would like to see for a given employee if the current year is an anniversary year and what award is due.

To mimic this, I have tried to set up three tables: tblEmployees, tblEmployedDates and tblAwards as follows:

tblEmployee

EmployeeID (related to tblEmployedDates::EmployeeID)

FirstName

LastName

…

tblEmployedDates

EmployeeID (related to tblEmployee::EmployeeID)

Hired

Terminated

YearsEmployed = If ( Terminated ≠ "" ; (12 * Year (Terminated) + Month (Terminated) - 12 * Year(Hired) - Month(Hired))/12 ; (12 * Year (Get(CurrentDate)) + Month (Get(CurrentDate)) - 12 * Year(Hired) - Month(Hired))/12)

TotalYears = Summary field = Int(Total of YearsEmployed)

tblAnniversaryRewards

AnniversaryYear (related to tblEmployedDates::TotalYears)

Award

Any suggestions?

Thanks,

-Chris

[comment]

You cannot use a summary field as a matchfield for a relationship. You can, however, add a calculation field to the Employee table =

Sum ( EmployedDates::YearsEmployed )

and use that to define a relationship to the Rewards table. It's also more correct this way, because the awards go to the employee, not to any one of his/hers time spans.

[Fenton]

I would do this from Employee, and I'd just use the Days/365 (or 365.2425 :-). Also, I would not put the "current" Employee start date in the Employed_Dates table. I would consider an active Employee's start date to be a legitimate attribute of an Employee. I would only push the date to the Employed_Dates table upon termination, using a button (showing results in a portal, sorted descending), clearing DateStart in the Employee table at that time. So Employed_Dates is more of a "history" table, in concept.

The main reason for this is speed. Because calculating the current days span requires using Get (CurrentDate), which must be unstored; calculations including it will be much slower, especially if included in a relational summarization.

In Employed_Dates I'd have:

cDays_Employed = Terminated - DateStart

In Employees:

YearsEmployed =

Let ( [

dys_curr = Case ( not IsEmpty (DateStart); Get (CurrentDate) - DateStart);

dys_past = Sum ( Employed_Dates::cDays_Employed );

dys = dys_curr + dys_past

];

Int ( dys / 365.2425 )

)

You could also do this your way, with all dates in Employed_Dates, but the calculation would need to check for DateTerminated; it would need to include Get (CurrentDate), hence would be much slower.

Edited August 5, 2008 by Guest
Case

[TulatinChris]

Hmm.. I haven't thought in terms of a "push" back to the employee table.

I'll give that a try.

Thank you!

[TulatinChris]

Thanks - I'll look into this.

I appreciate the help!

[Fenton]

I would only push the date to the Employed_Dates table upon termination

I wasn't saying anything about a "push" back to the Employee table. I was talking about a "push to" an Employee_Dates history table (from the Employee table). Put "terminated" date intervals into a historical table (date end - date start). Only currently employed employees have a "Date Start" (or whatever) in the Employee table. Place the calculation which totals all this in the Employees table.