5 Replies Latest reply on May 2, 2012 2:03 AM by cortical

    Adding Salary Table to Time Card  how to select week to add salary to

    ian.moree

      Hello Again everyone;

       

      In this Third and hopefully last part of this very simple task, i am trying to add a salary table which was recommended to be added by a member.

       

      OK!

       

      In my salary layout, i need to be able to:

      • drop down my current employee list √
      • choose the work week ( cant seem to do this without screwing up current data on Time Table)
        • using drop down lists here dont work, they are messing up - Perhaps i need a lookup field ? i dont think necessary, but ??
      • So after choosing the week, i would like to add the salary,

      The real issue to me is i think i need to be adding the calculations for salary on time card entry:

       

      then basically Put the data in the salary table from within the Time card layout? any ideas or am i just thinking too much.

       

      Main issue is where shall i calculate the salary ( which table )?

       

      &

       

      i want to report on Employee's work week , sorted by month / year

      as well as Salary / deductions, holiday pay / overtime ,etc on 1 report

      Thanks

       

      -i

        • 1. Re: Adding Salary Table to Time Card [ how to select week to add salary to]
          cortical

          I use a dedicated LUT (lookup table)  of weeks of year, with week start and week end dates, pay_dates

          Useful for tuneled rels, portal list of weeks of the year, click select and display payroll summary portal...

          I code the Weeks primary key into the Pay (time card) table

           

           

          time card is invoice,  to EmployeeWorkedDays  line items  ( unless a repeate field perversity is being used)

          Line items can thus conditionally use multiple rates for split shifts, overtime, holidays... and calculate a salary contribution for the DAY (EmployeeWorkedDays ie Daily)

           

          ie Pay / PayLines

           

           

           

          so salary is in  timecard (Pay)  , a sum of related EmployeeWorkedDays (PayLines)

          tax etc is then calculated in TimeCard (Pay)

          • 2. Re: Adding Salary Table to Time Card [ how to select week to add salary to]
            ian.moree

            Cotical:

             

            thank you for that explanation; However I really dont understand what you said! Sorry!

             

            at the end i assume that you mean for me to

            • put salary information within the TimeCard Table
            • and calculate the pay within TimeCard based on PayLines? which is a Join table i assume connecting TimeCard with Employees?

            thanks

            -i

            • 3. Re: Adding Salary Table to Time Card [ how to select week to add salary to]
              cortical

              yes; weekly salary and deductions calculated in time card table

              if different rates apply, penalties etc, then these are calculated in the TimeCardLines ( daily hours worked) child table.

              If different rates do not apply, then the parent TimeCard record only has to sum the total of the line item hours, and multiply by the stardard hourly rate.

               

              PayLines is not really a join table, it is a line items table.

               

              A join table usually (should) only has the join table primary key, and the 2 foriegn keys of the tables it joins.

              The employee table could be related to the pay lines tables,  via employee_id, and used to show a portal off ALL days worked by the employee

               

              More usefully a rel between employee and TimeCards, shows all the weeks worked and salary payments received

               

               

              Employees

              PK : employee_id

               

               

              Pays - the  master table of all weekly  pay dates for the company

              PK : pay_id

              pay_date

               

               

              TimeCards ( in your example)

              the weekly summary of days worked for  given employee

              PK: time-card_id

              FK: employee_id

              FK: pay_id

              calcs to total the daily salary for days worked for the week, and deduct tax etc

               

              TimeCardLines - daily hours

              PK : time_card_line_id

              FK : time-card_id

              date

              hours...

              calculate daily wage

               

               

              The TimeCard - TimeCardLines ( parent - child)  allows different shifts and houly rates (penalty rates etc) to be calculated in the child table

               

              The TimeCard - TimeCardLines  is the same  parent - child construct as an Invoice-invoiceLines situation.

               

              A relational structure is always more versatile than a repeat field implementation. But repeat fields, if you are using them,  often seem easier initially, if your database bulding experience is relatively limited ( and I do not know if it is or not).

              • 4. Re: Adding Salary Table to Time Card [ how to select week to add salary to]
                ian.moree

                Well. This is helpful and will try/ start to implement:

                 

                A relational structure is always more versatile than a repeat field implementation. But repeat fields, if you are using them,  often seem easier initially, if your database bulding experience is relatively limited ( and I do not know if it is or not).

                i am using repeating fields for simply dates( day1 , day 2, etc ) i didnt see the need to normalize this because it is only adding +1 to each day from the week_Begin field.

                 

                thanks

                 

                -i

                • 5. Re: Adding Salary Table to Time Card [ how to select week to add salary to]
                  cortical

                  <<

                  i didnt see the need to normalize this because it is only adding +1 to each day from the week_Begin field.

                  >>

                   

                  until you want to know how many total man hours per day etc. ( yes:  get repn something etc variations and their demon ilk are plausible to those who use that kind of thing)

                   

                  To normailse or not to normalise. That is the question.

                  The answer is always normalise. It is also a philosophy - not everyone's I know.