Payroll database problem

[mappler]

I am new to FM7. I understand databases and have done most of my work in SQL Server, MySQL, or MS Access. I am trying to move one of our databases to FM7. It is very possible the layout I am attempting to use is foolish for an FM7 database, and I am more than happy to change that. Let me describe what I am trying to do.

I have 3 tables right now. 1) List of all employees in the company. 2) A table that consists of EmployeeID, EffectiveDate, Salary. 3) A table of all valid paydates.

I require 2 views of this data. 1) For any employee, look up their salary history. This is easily accomplished with portals, and works great.

2) The ability to look up any paydate, and show the payroll information that is "in-effect" for this date.

#2 I can't figure out how to do in FM7. The effective dates could be any date, and may not be contained in the paydates table. Paydates are currently dates at a 2-week interval for 30-40 years.

So, for a given paydate, I need to see only ONE salary for each employee, and that salary would be the maximum effective date that is also less than or equal to the paydate.

Thanks in advance,

-Matt

[RalphL]

You could use the Last function with a relationship that the effictive date is less than or equal to the paydate.

[mappler]

You could use the Last function with a relationship that the effictive

date is less than or equal to the paydate.

Where do I put the Last function?

-Matt

[RalphL]

In Table 1

Salary = Last (Table2:: Salary)

The relationship is Table1::Paydate >= Table2::Effictive date AND Table1::EmployeeID = Table2::EmployeeID.

[mappler]

First, thank you VERY much for all your help.

I think I am close. Table 1 is PayDates, Table 2 is EmployeeRates, and Table 3 is a list of all employees.

My relationships right now are:

Table 1::PayDate >= Table 2:Effective Date

Table 2::EmployeeID = Table 3::EmployeeID

I have a field in Table 3 called "CurrentSalary" which is equal to Last(Table 2::Salary).

The Layout is done on Table 1 showing 1 PayDate at a time. I put a portal on Table 3, showing FirstName, LastName, CurrentSalary.

The problem is that it then shows me the last value in Table 2, but not with an effective date <= paydate. So, if employee John Smith has a salary of 50,000 on 10/10/03, and 60,000 on 10/24/03, it will ALWAYS show John Smith as having a salary of 60,000.

Again, thanks for all your help. I think I'm very close.

-Matt

[RalphL]

You turned the Tables on me. I used Table1 as the employee table, Table 2 as the Rates table, per your first post. Why do you have a portal in Table 3 (Employees)?

My relationship is from the employee table to the rates table. It is an AND relationship. When you make this relationship you drag EmployeeID in Employee to EmployeeID in Rate. Then you drag PayDate in Employee to EffictiveDate in Rate. Click on the = sign in the box to open the relationship dialog box. Change the relationship PayDate in Employee to EffictiveDate in Rate from = to >=. After this the box will have [X] in place of =.

[mappler]

Got it. THANK YOU SO MUCH!

It is so completely different working in FM7 than it is in Access or another SQL based database. My brain seems to be actively working against seeing obvious solutions.

The reason for the third table was to allow scrolling through valid pay dates, and seeing the listing of payroll at that point. I think the way to properly do this however must be to create a global variable in the Employees table, and set that variable with a field on the Layout. I would then populate that drop box, etc. with fields from a list it seems.

Again, thanks for the help. I believe I have the functionality I was trying to get and it should just be a matter of formatting now.

-Matt

[Barbecue]

It is so completely different working in FM7 than it is in Access or another SQL based database. My brain seems to be actively working against seeing obvious solutions.

I think familiarity with traditional SQL-based database systems may actually make it more difficult to learn FileMaker. FileMaker's approach is pretty minimalist, and for people used to working within more rigid structures, the flexibility of FileMaker can actually seem like a negative. But as I keep telling the Oracle guy, just because it's different doesn't mean it's wrong.

[mappler]

I think familiarity with traditional SQL-based database systems may actually make it more difficult to learn FileMaker. FileMaker's approach is pretty minimalist, and for people used to working within more rigid structures, the flexibility of FileMaker can actually seem like a negative. But as I keep telling the Oracle guy, just because it's different doesn't mean it's wrong.

Yes, I would agree with you. It is very difficult to make the transition because the basic, atomic elements that I have learned to accomplish database tasks are just different in Filemaker. It is like learning a completely different language, HOWEVER a language without some of the same basic atomic elements, or rather with different basic atomic elements.

-Matt