2 Replies Latest reply on Oct 1, 2014 9:34 AM by EricBrown_1

    Creating an export report for staff with multiple departments/pay rates

    EricBrown_1

      Title

      Creating an export report for staff with multiple departments/pay rates

      Post

      Ok, here's my problem:

      I'm attempting to create a time clock dbase that relates to staff members. Staff can clock in to multiple departments that they are assigned to and may have variable pay rates based on the department they're working for. These were done by a join table:  Staff -< Wages >- Dept, using appropriate primary/foreign keys.

      Staff pay cycle varies by position and can work bi-weekly or monthly cycles. Overtime is calculated based on department schedule (either 8 hr or 10 hr shifts) and/or 40 hr work weeks. When a staff member starts a new cycle, a new pay cycle record is created, a new time card is created and the related punch data. The tables are related:  Staff  -< Cycle -< Time Card -< TC Data.

      Everything is working correctly, but now I'm having trouble creating a report to export for the payroll company to process.  When I generate the report, I need to show 1 line item for each employee showing the total worked in a specific department during the current pay period. Example:

      Name               Dept                Hrs Worked     Total Pay

      John Doe         Kitchen            30                    300.00

      John Doe         Pantry              10                   120.00

      I'm guessing that the report needs to be based on TC Data, filtered by pay cycle and then sorted by employee and then department. Then I need to have a running total, by department for that employee of their wages earned and probably involves a GetSummary evaluation. Any suggestions would be greatly appreciated.

        • 1. Re: Creating an export report for staff with multiple departments/pay rates
          philmodjunk

          Your guesses sound correct and for export purposes there's a "group by" option that allows you to export subtotals and data for a group of records as a single row of exported data.

          If that wasn't the "nudge" that you needed:  What does one record in TC Data record? What does one record in TimeCard Record?

          • 2. Re: Creating an export report for staff with multiple departments/pay rates
            EricBrown_1

            Thanks Phil, 

            I figured you'd be the man with the plan. When an employee clocks in using IPad layout, a script determines if a new Pay Cycle record & Time Card record need to be created. The created TC Data record records In / Out time, whether the staff member ended a shift or took  a break and calculates pay for the shift and whether the staff member entered OT during that shift. 

            Originally, Time Cards tracked when the week begins/ends, Hrs Worked in Week, Reg Time, OT, calculated pay, but much of that was moved to TC Data to deal with the multiple departments/rates issue and to facilitate running a report based on the TC Data. Now I just need to figure out how to apply the correct sort/filters to the report.  

            I think your ' ..."group by" option that allows you to export subtotals and data for a group of records as a single row of exported data... ' was the clue that I was missing to complete the necessary report. That's what I was drawing a blank on when it came to generating the export from the report.