Complex Layout/Report

[AllenWWII]

I am at a lost in how to even approach the solution to this issue:

FMP 7

Mac OS X

Have the following Fields in one table (Shift Table):

Employee S1 (Employee that works 1st shift)

Employee S2 (Employee that works 2nd shift)

Employee S3 (Employee that works 3rd shift)

Employee S4 (Employee that works 4th shift)

((Employee fields are populated via a Pop up list that uses values from a related table (Employee Table)). (The Employee field can be blank if day had less then 4 shifts)

Balance 1 (1st Shift Employee balance (number))

Balance 2 (2nd Shift Employee balance (number))

Balance 3 (3rd Shift Employee balance (number))

Balance 4 (4th Shift Employee balance (number))

(Balance fields are calculation that displays wether the employee was over/under/even on the money for their shift)(Balance field can be blank if no employee worked the shift)

Each of the fields above are present in a single record for a single date.

Now since all of that is out of the way this is what I want a weekly report to display.

The report criteria is a range of dates for a week (the week starts on a Monday and ends on Sunday).

I would like the report to look like this.

Shortage Report Dates: From - To

Employee(1) (Employee name if worked any shift during the week)

Date (Date Worked) Balance (Employee Shift Balance(first shift worked))

Date (Date Worked) Balance (Employee Shift Balance(second shift worked))

Date (Date Worked) Balance (Employee Shift Balance(third shift worked))

Date (Date Worked) Balance (Employee Shift Balance(forth shift worked))

Date (Date Worked) Balance (Employee Shift Balance(fifth shift worked))

Date (Date Worked) Balance (Employee Shift Balance(sixth shift worked))

Date (Date Worked) Balance (Employee Shift Balance(seventh shift worked))

Employee(2) (Employee name if worked any shift during the week(not employee from above))

Date (Date Worked) Balance (Employee Shift Balance(first shift worked))

Date (Date Worked) Balance (Employee Shift Balance(second shift worked))

Date (Date Worked) Balance (Employee Shift Balance(third shift worked))

Date (Date Worked) Balance (Employee Shift Balance(forth shift worked))

Date (Date Worked) Balance (Employee Shift Balance(fifth shift worked))

Date (Date Worked) Balance (Employee Shift Balance(sixth shift worked))

Date (Date Worked) Balance (Employee Shift Balance(seventh shift worked))

The report continues down with each employee that works at least one shift per week. There are times when an employee does not work and I don't want this employee name on the report.

If Employee(1) = Mr. Smith then display only the balances that correspond to his shift.

If Employee(2) = Mr. Jones then display only the balances that correspond to his shift.

If Employee Mr. Smith or Mr. Jones did not work then don't display anything.

How do you have FMP search a set of records in a find request. Decide that a unique Employee worked 5 different shifts during that time. Display his name once then display the balances of the shifts below his name. Then continue with the same for each employee that worked during the week. And have all this information displayed on one report.

Any help on this matter will be greatly appreciated. [email protected]

[bruceR]

Any time you use numbered field like this it hints strongly that you have a data strucure problem. And you do, and that's why you cannot build your report.

You should have fields Date, Employee Number and Shift Number.

[Ender]

Bruce,

Half an answer is probably not much help to a beginner. Why don't you give Allen the structure too?