6 Replies Latest reply on Mar 7, 2017 3:34 PM by kaotishe

    Time Card Pay Roll Solution.

    kaotishe

      I need to create a solution that will track hours worked for an hourly employee and create a weekly pay roll pay amount as well as relate to a previous Jobs table and create weekly pay amounts for two independent contractors (three if you count me, I am on the same pay schedule).

       

      So I need

       

      1) An easy time card solution. This I am pretty sure I know how to do with one record being one day of work for one employee. I could even put the ability to have a split shift into this table, though at this time that is not needed. But I am open to thoughts on the best way to do this.

       

      2) This part gets tricky for me - It then need to total by week and tell me how much to pay each employee (which at this time is one but will hopefully be more in the future). So one payroll record per week, per employee. This table would ideally run a script to email the employee at the time I mark that week as paid and send them a line item of the hours worked and the check amount.

       

      3) And this is the part that is really confusing to me as to how to set up. The payroll table needs to also relate to a jobs table (that I can do) and look up record pertaining to that weeks time period and get data from three different fields and place those amounts and positions on each independent contractors payroll record. Let me explain this in a little more detail. The jobs table tracks work by position 1st shooter, 2nd shooter, photo editor. and the amount of that job that is paid to the contractor for whichever position they worked.

      So on in that time period job 001 I may be the 1st shooter and get paid $500 while Robin may be the photo editor for that job and may be getting paid $125

      Job 002 Robin may Be the 1st shooter and get paid $480 Spencer the Second shooter and get paid $185 and Robin may also be the editor and get paid $105

      SO this section of the payroll database needs to give me three records for that week

      Brendan with job 001 1st shooter $500 total $500

      Robin Job 001 Photo Editor $125 job 002 1st shooter $480 Photo Editor $105 Total $710

      Spencer Job 002 2nd Shooter $185 total $185

      And then email each of us respectively, again when jobs are marked as paid, the line items for that weeks pay and the total.

       

      And to complicate this all a little further, it is possible that at some point in the future an independent contractor could also have a day or two where they were doing office work and were payed hourly... So that would also need to be a line item on that weeks payroll record for them.

       

      In the past I have been tracking all of this on multiple spreadsheets, and we are starting to get busy enough that figuring out a filmmaker solution is less of a headache then tracking this on spreadsheets and in some cases having the bank send a contractor multiple checks in one week...

       

      SO, how do I get started?!?!?

        • 1. Re: Time Card Pay Roll Solution.
          philmodjunk

          To start, each time card record record the date that the work was done. Then another field can calculate a date for the first day of the same week:

           

          date - dayOfWeek ( date ) + 1

           

          You can use this field as a match field in relationships, in finds, and to group records in summary reports by week.

          • 2. Re: Time Card Pay Roll Solution.
            kaotishe

            Ok I have the basics of a time card table built and included a field with the above calculation. Should I also add it to the jobs table so it can be used for grouping there as well?

            • 3. Re: Time Card Pay Roll Solution.
              philmodjunk

              Do you need to group jobs by week? If so, yes, if not, not.

               

              I don't have a completely clear picture of how you plan to use that Jobs table. What does one record in Jobs represent?

               

              Will you need to produce a "break down" of the hours worked in a given day by the type of Job being done during each part of the day. (Had to charge my hours against multiple project numbers when working as a Tech Writer in the Aerospace Industry....)

               

              Note that with this field, you can set up a summary report and sort the records by this week field to group them by week. A sub summary part and a summary field can then report totals based on all the records for a given week.

              • 4. Re: Time Card Pay Roll Solution.
                kaotishe

                The Jobs table tracks total sales and labor (pay) associated with a photoshoot. Photographers are paid a percentage of sales and the photo editor is paid a flat rate for each image edited. The Time Card is solely for tracking overhead cost of clerical work. There actually ends up being three tables that figure into Payroll as there are some significant differences between weddings and family photos verses the big event shoots that we also do for martial arts schools.

                 

                So what I need the report to generate is a list organized by week, by employee that itemizes the work  performed listing job, task, pay and then gives a total pay field. I need it to email the information to each employee essentially giving them a pay stub at the time i generate the report. So week one's payroll report would be generated on the Wednesday of week two - this can be initiated manually. It gives me a total for each person so I can have the bank mail paychecks and it emails each employee their pay stub so they know what to expect and can confirm that it accurately lists work preformed. I had been doing all of this with a mess of spread sheets before and we are now getting busy enough that it is not practical to do that any longer. And quite frankly it was a headache to do.

                • 5. Re: Time Card Pay Roll Solution.
                  philmodjunk

                  So each "photo shoot" is a job record?

                   

                  And you have three different types of jobs with a table for each?

                   

                  But "1st shooter"; "2nd shooter"; "editor" are tasks performed for a given job?

                   

                  And is it only one task per person per job?

                   

                  Or might a person perform two different tasks for the same job?

                  • 6. Re: Time Card Pay Roll Solution.
                    kaotishe

                    Yes, each "photo shoot" is a job record.

                     

                    Two different types of jobs plus the hourly office work for a total of three tables

                     

                    yes "1st shooter"; "2nd shooter"; "editor are tasks on the two jobs tables

                     

                    No it is possible for a person to have more than on task per job.