Discussions

Expand all | Collapse all

Sum hours by pay period

  • 1.  Sum hours by pay period

    Posted 01-01-2019 21:07
    Trying to sum time cards by pay period. I have one table to collect hours worked.
    Table is "Time Cards". Contains [Date], [Job], [Employee], [Hours].
    Have also built a Pay Periods table for 2019 that contains [Pay Period], [Beginning Date], [Ending Date], [Pay Date]
    Pay periods are every two weeks starting on 12/31/18.
    Pay Periods has a one to many relation to Time Cards.
    At the end of my pay period, I need to add up the hours worked by employee for that pay period.
    I can make a simple report against the Time Cards by date with a filter that prompts for beginning date and ending date, but the leads to potential errors when selecting the correct date range, so I would like to either attach the appropriate pay period number to a time card record and then group by that, or run a report on pay periods and group by the period.
    The part I'm stuck on is assigning a date to the pay period range, unless my thinking is wrong and there's a better approach? I previously did this in Excel via a VLOOKUP formula.


  • 2.  RE: Sum hours by pay period

    Posted 01-01-2019 21:50
    no problem.  FirstDayOfPeriod function to the rescue.  It was really designed for your exact use case, like a very typical 2 week pay cycle than never changes come hell or high water.

    Change the Key field of your pay periods table to be the field [Beginning Date]

    The on the details time entries make a formula field for Pay Period beginning date

    https://login.quickbase.com/db/6ewwzuuj?a=dr&r=bm&rl=w5j

    FirstDayOfPeriod([date field], Weeks(2), Date(2018,12,31))

    Use that field as the reference field on the left side if the relationship were 1 pay period record has many time entries and then make your summary field.


  • 3.  RE: Sum hours by pay period

    Posted 01-03-2019 10:23
    That works very nicely, thank you.
    Taking this one step further, on my time card form where I now see the pay period drop down box, it would be great if the value of the pay period defaulted to the correct period for the date of the time card. In other words, when I create a new time card entry and select the date I worked, have the pay period set to the correct period for that date. I want to say this should be a filter or dynamic form rule, but I can't quite make the connection.


  • 4.  RE: Sum hours by pay period

    Posted 01-03-2019 12:44
    Ok, try this

    Make a formual date field called first day of current time period.

    The formula will be firstdayofperiod(today(), weeks(2), Date(2018,12,31))

    Then have a form rule to fire that says when the pay period date is blank change pay period blank to gr value in the field first day of current time period.

    There is a chance that you will need to put that field on the bottom of the form to get that form rule to fire. Also set that checkob on t(e form rule to unchecked, to be sure it fires.


  • 5.  RE: Sum hours by pay period

    Posted 01-03-2019 21:38
    I don't know if I got myself worked around in knots on this, but I seem to have it working...not exactly as you described but I am able to confidently enter time and report on it for payroll purposes.

    I very much appreciate your help on this.
    Brian


  • 6.  RE: Sum hours by pay period

    Posted 01-13-2019 03:10
    Hi, this is my first time responding to the community, but I had the exact same problem and resolved it using the automation feature.

    Essentially, when someone enters a time card entry, the automation is triggered based on (adding or modifying) the "Date Worked" field in the Time Card table. After the date is entered, the automation has Quick Base to see if the "Date Worked" value falls between one of the (Payroll Calendar Table) "Beginning Date" and "End Date" values, each pair has an associated Payroll Period.

    Once QB determines which record ID# row (where the Date Worked fell between a begin and end date on payroll calendar), it copies the Payroll Period (and any other associated fields) to a third, many-to-many table that is linked to both the Payroll and Time Card tables.

    If you still are looking for a solution, I can provide screen shot details of the automation, relationships and tables.



  • 7.  RE: Sum hours by pay period

    Posted 01-23-2019 21:18
    I would be interested in seeing how you configured the automation and relationships. I'm getting stuck at the automation, and i didn't quite follow why you copied to a third table. Is the extra table there for something else too? I'm thinking I could just copy it back to my time card entry...


  • 8.  RE: Sum hours by pay period

    Posted 01-13-2019 11:04
    That sounds very interesting. What I ended up doing works, but still leaves open the possibility of choosing the wrong period.


  • 9.  RE: Sum hours by pay period

    Posted 06-13-2019 15:50
    I would be interested in seeing screenshots of the automation details and table relationships. I am working on a payroll app and we have a non traditional pay period set up. Employees are paid the 15th and last day of every month. Most are salary so the period is the 1st-15th and 16th-last day-which varies..so the 2 week time period concept isn't working.


  • 10.  RE: Sum hours by pay period

    Posted 06-13-2019 17:22
    I cannot figure out an automation to identify the pay period.
     I looked at dynamic form rules but don't see a way for it to change the pay period based on the date entered falling into a date range on the payroll calendar table. There was not a during option just on, before, after etc.
     I looked at automations and I just don't know how it identifies the date range and where its goes after that. The only options I get keep expecting me to be able to identify the pay period number when it is going into the table?


  • 11.  RE: Sum hours by pay period

    Posted 06-13-2019 17:28
    I do not believe that this required an Automation.  Can you explain the full question.  Are you looking to have a table for all the pay periods then a summary of the hours worked by Employee (EE)?

    If so I would create a table for all the pay periods, loading up with Excel with a key field which identified the YYYYMM1/2

    for example

    201901-1
    201901-2

    that would mean the par periods 1 and 2 for the 2019 January.

    Then have each detail time entry self calculate which pay periods they calculate to by building a formula to make that text string.

    Post back if you need help with the formula.






  • 12.  RE: Sum hours by pay period

    Posted 06-13-2019 17:37
    So far I have a table employees record [Staff Member](linked table) [Date] ...[Number of Hours] and I created a Payroll Calendar Table with [Pay Period] [Beginning Date] [Ending Date] [Pay Date](Which is always the ending date). I added a look up field on the time entry table that would allow the user to choose their pay period but I want it to assign the pay period. I tried to create a 3rd table as Damon described "Payroll" but do not understand how he got the automations/actions to match the Date in the time entry table with the Pay Period from another table to copy the 2 records into one on the Payroll table.


  • 13.  RE: Sum hours by pay period

    Posted 06-13-2019 17:41
    I am happy to try it another way. I started with trying to build it how Brian and you were working out at the beginning of this thread but since we don't use a traditional 2 week pay period I was struggling and tried to see what Damon did.


  • 14.  RE: Sum hours by pay period

    Posted 06-13-2019 17:54
    I added a field that identifies the pay periods as you described. I would need help creating a formula. I could imagine a really long formula that could result in those numbers but not one that will pull the related pay period. 


  • 15.  RE: Sum hours by pay period

    Posted 06-13-2019 18:05
    Did you set the Key field of the parent pay period table to be the YYYYMM-1/2 table?

    Then I will help you with the formula and the relationship.


  • 16.  RE: Sum hours by pay period

    Posted 06-13-2019 18:09
    Hi Michelle, When I return from traveling can provide more details (by Close of Business tomorrow) on how my time card app automatically assigns the correct pay period (based on date of time worked) from the payroll calendar.

    Damon


  • 17.  RE: Sum hours by pay period

    Posted 06-13-2019 18:21
    I set the key field to the new field as described :)


  • 18.  RE: Sum hours by pay period

    Posted 06-13-2019 18:39
    OK, here is a formula. 
    I like Automations, but I prefer to avoid them if a simple formula will work.

    Here is the formula to create on the detail time records and then the relationship to the time period table would be based on this field.



    // first we gather up the ingredients for our final formula

    var text YYYY = ToText(Year([Date]));
    var text MM = PadLeft(ToText(Month([Date])),2,"0");
    var number DayOfMonth = Day([Date]);

    var text FirstSecond = If(
    $DayOfMonth <=15,"1",
    $DayOfMonth >=16,"2");

    // now we assemble the ingredients

    $YYYY & $MM & "-" & $FirstSecond




  • 19.  RE: Sum hours by pay period

    Posted 06-13-2019 18:54
    Ok I added the formula and that generates the correct pay period id. Still unsure of how to get that to relate to the table with the YYYMM1/2 pay periods in order to summarize hours worked by employee each pay period.


  • 20.  RE: Sum hours by pay period

    Posted 06-13-2019 19:09
    You would make a relationship where 1 Pay Period has Many Time entries, and on the right side of the relationship instead of letting Quick Base create a new field, you would select this YYYYMM1/2 field.

    Then on the pay period record Form you would include the report link field which the Relationship created for you.

    Then create a an unfiltered summary report of all hours worked by employee and use that report for the form to use.

    The result will be that only the Time entries for that Pay Period will be on the embedded report.

    once that is working, we can fancy up that Summary report by having the employees listed own the page as would be normal, but if you like we can show th date across the top in columns, so it would be easy to see any gaps where no time was reported for a particular EE for particular day(s). 

    In fact you could put both the basic summary report on the form and then have that report link on the form again with a different version of the report - maybe one that had a summary with the columns I described, and then maybe a third version which as the detail entries.

    The alternate reports could be put in tabs so that the form loads quickly as embedded reports in Tabs do not hold up the initial display of the form.


  • 21.  RE: Sum hours by pay period

    Posted 06-13-2019 20:29
    That works, I was able to add the dates across the top also. I have that one displayed on the form and know how to add the others if it will be helpful. Thank you very much for walking me through!


  • 22.  RE: Sum hours by pay period

    Posted 06-13-2019 20:34
    Well done!