9 Replies Latest reply on Jul 31, 2016 12:01 PM by clayhendrix

    Need a daily/weekly overtime calc

    wklight

      Hello,

       

      I need some assistance calculating overtime hours. Id like to enter a start time and end time. Ideally the script would pay overtime if more than 12 hours/ day or 40/week...

      Thanks..

        • 1. Re: Need a daily/weekly overtime calc
          clayhendrix

          1.) Do the 12 hours always occur on the same date? Might some workers work through the 12:00am time?

          2.) What is the beginning and ending of the week? Might some workers work through the last minute of one week and the first minutes of another week?

          3.) Do you want the calculation to determine how much money the individual should receive for the over-time only, or both standard time and over-time?

          4.) Is over-time 1.5 times the standard rate of pay?

          5.) Are you importing time from a time-clock type of system?

           

          The first 2 questions might not matter depending on how the data exists in your current database, if you are adding this as a feature.

          • 2. Re: Need a daily/weekly overtime calc
            wklight

            Thanks for the reply,

             

            1). Possibly. Although rare, I'd like to be able to account for the possibility

            2). Sunday thru Saturday.

            3). Total pay, regular hours and overtime.

            4). Actually that changes depending on job circumstances and day, sundays/holidays 2x time. I'd like to be able to select a pay profile that contains the parameters.

            5). No time clock, data is entered directly into a FM table

            • 3. Re: Need a daily/weekly overtime calc
              clayhendrix

              Can you send me a screenshot or a description of the structure of the table into which the data is entered? What you want to do is very accomplishable, I just need to understand the structure of the data you are currently using.

              • 4. Re: Need a daily/weekly overtime calc
                wklight

                That part of the system is empty Clay, Here is what I'm thinking....

                 

                Employees get a base rate. they get that rate for 12 hours/day, 40 hours/week. More than 12 hours per day or 40 for the week gets 1.5x base rate, 2x on holidays.

                 

                That can change based on the customer. I'd like to use a customer profile that has those multipliers in the customer profile record in the customers table...

                • 5. Re: Need a daily/weekly overtime calc
                  wklight

                  Edit...

                   

                  So you have a employee record that has the base rate. The system should pull that rate, multiply it according to the hours worked X specific customer overtime/holiday multiplier...

                  • 7. Re: Need a daily/weekly overtime calc
                    clayhendrix

                    If you can send me the file from which you included a screenshot of the relationship diagram, that would help, but I do not know if you can do that.

                     

                    1. The following fields should be set to unique:
                      1. Employee_ID
                      2. Event_ID
                      3. client_name
                        1. you should create a Client_ID field and set it to be unique and link the Client_ID field to the Events table
                        2. you should change client_name to Client_ID in the Events table
                      4. There is some other work to do with the Event Time Track..., Expenses, and Event tables, but modifying these fields to be unique will help me visually see next step(s).

                     

                    To change a field to unique:

                    1. File, Manage, Database
                    2. Fields tab
                    3. Click on the field
                    4. Click Options... (in the bottom right area of the window)
                    5. Click on Validation Tab
                    6. Check Unique Value (two lines under Require:)
                    • 8. Re: Need a daily/weekly overtime calc
                      wklight

                      Thanks Clay,

                      Yes the Client_Name will go to Client_ID, Just late night sloppiness on my part.. Employee_ID will be PK on that table.. Most of the updating to the work record will come from a field deployed app via Get (UUID)...

                      Moving away from a static deployment onto separate apps running on iPhone/iPad and via web direct..

                       

                      I'm running FMP Advanced, I can send you whatever you'd like, Note the whole project is quite malleable at this point, with the server side deploying for testing next week so I'm wide open..

                       

                      Thanks!

                       

                      Hey Clay,

                      Send me a msg at wklight@gmail.com

                      • 9. Re: Need a daily/weekly overtime calc
                        clayhendrix

                        Send me your FMP file and I'll look at it.