Payroll Dilemma

[Terran]

Hi all,

I have done a payroll database recently. However, the payroll database involves taking out a standard amount of money from each person on a weekly basis. The report for this deduction is done 'monthly'. The reason I put 'monthly' in quotes is that for the purposes of payroll a month may have four weeks or sometimes it may have five, depending on how the weeks fall.

The report will have the employee's name, their employee number, and columns- week 1, week 2, week 3, week 4, and week 5. So going across the page, you will see the employee, and the contributions they paid in each week (Again stressing that this is going across the page in columns).

Now firstly, you already know that each week's payment will be recorded in a separate record (which will normally be displayed going down, not across). What I need to do somehow is do a find for the period. Then determine whether the period encompasses 4 weeks or 5 weeks. From this I need to determine for each employee which record represents which week (1st week, 2nd week, etc.) Then my thinking is to maybe assign the value to a field (if the record represents the first week, then assign the deduction amount to a first week field; if the record represents the second week, then assign the value to the second week field and so on). Then when this is done go to the report layout in a subsummary part and display the data.

This is what I am thinking, but the question is. How do I accomplish it? How do I determine how many weeks (each record has a start period date and an end period date, but you have to look at the first start date and the last end date of all the records. Also how do I determine for each employee which record represents the first week and which represents the second week and so on?

Any help ya'll can give me will be greatly appreciated. Thanks in advance.

Regards

Thomas

[Razumovsky]

I have yet to do a payroll database, but here are some things to think about:

What I need to do somehow is do a find for the period.

You can do this, right (use "..." ex. 1/1/03...1/7/03 in find mode would bring back records for these 7 days)?

Then determine whether the period encompasses 4 weeks or 5 weeks. From this I need to determine for each employee which record represents which week (1st week, 2nd week, etc.)

I would do this via a script. Something that loops through the records and sets a field (t_Week#)according to a calculation based off the WeekOfYearFiscal Function. This would accomplish both the above tasks.

So going across the page, you will see the employee, and the contributions they paid in each week (Again stressing that this is going across the page in columns).

This is unfortunately a drag to do in my experience. Luckily, there are only 4-5 columns, so wont be so bad.

I would create 5 relationships from employee file to Paycheck file:

c_week1::c_week#

c_week2::c_week#

c_week3::c_week#

c_week4::c_week#

c_week5::c_week#

c_week1 would be a calc in employee file: EmployeeID &"-"&Globalmonth&"-"&1

c_week# would be a calc in Paycheck file: EmployeeID &"-"&month&"-"&t_week#

you would type in the month you wanted the report for in global month, and then see the paycheck records in 4-5 portals across the screen, each one displaying records from the above relationships.

Use these relationships as the basis for assigning your deductions as well.

Hope that gives you some ideas.

-Raz

[Terran]

Raz,

Thanks a lot for the suggestions. They helped me focus a lot. I already knew how to do the fine. That was fairly simple, but I was definitely stumped with regards to the determining the week. Your idea sounds promising. I will take a look at it and let you know okay?

With regards to assigning the columns to portals. Brilliant! I am pretty sure that will work. Thanks a lot. I will be working on it and will let you know how it turns out okay? Thanks again.

Regards

Thomas

[Terran]

Solved IT!!!

Hey thanks for all the help! I was sitting down to hash it out as you said. It was very difficult. I did a couple scripts and got it to do the calculation. But it had some problems whereby if a person was suspended or not paid for a week then the deduction would be missing for that week. How to adjust for that? So I sat thinking and I realised that there is a way to do it without scripts or portals or anything!

I created a filemaker file to import the records to. This file had the standard fields (name, employee number, amount, date, etc.). I formed a self join relationship between the employee number in this file and itself. Then it had the fields (week1 date, week2 date, week3 date, week4 date, week5 date). Week1 date was a calculation field (Min(selfjoinrelationship: Date)). This means that the beginning date would appear in week1 date of all the records. Week2 date was also a calculation (=week1 date + 7); week3 date was (=week2 date + 7) and so on for all up to week5 date.

In this way I have the starting date for all the weeks. Now in the payroll records it has the starting date in each record. Then I created calculated fields to determine which amount belonged to which week. Week1 amount (=If (Date= week1 date, Amount,"")); Week2 amount (=If(Date= week2 date, Amount, "")) and so on up to week5 amount.

So for each record I have the amount in the appropriate week amount field. Next I created a set of calculated fields to display the total amounts paid for each week by the employee. Week1 amount summary (=Max(selfjoinrelationship:week1 amount); Week2 amount summary (=Max(selfjoinrelationship:week2 amount); and so on up to week5 amount summary.

In this way, in each record I have the total amounts paid for each week. All I do now is create a report and place the values in a sub-summary field so that I only get one entry per employee, and the report is ready with the amounts paid by each employee each week.

No scripts or portals needed!

I just thought I would post the solution so that anyone else who wants such a solution could refer to it. Thanks again though. You made me think harder.