Need help to figure out total hours worked per Employee, per Week, per Project... as a field, not a summary report.

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered
Hi guys,

I'm pulling my hair out trying to figure this out, so any help you could provide would be very much appreciated! 

Here's the scenario: My employees may work on 10 different Projects in any given week. To calculate Overtime for that employee is fairly easy. Just summarize the total number of hours worked during the current week. Easy peasy. That will give us the calculation for what WE should pay that employee. 

But, here's the hard part. We bill these employees time to our customers on a per Project basis. If Employee X worked 60 hours during that week across multiple Projects, but only worked on Customer Y's project for 39 hours, we can't bill Employee X at Overtime rates for Customer Y's Project. 

Also, I should note that I need this in a field on the Time Card table so I can use the Overtime Hours as a column on the Invoice generated by an Exact Form (Exact Forms Plus). See attachment for example record on the Invoice. 

Here are the tables that I currently have: Projects, Employees, Time Cards. Projects is the parent to Time Cards. Employees is the parent to Time Cards. 

I can't use a summary of all the hours worked for that employee that week, becuase he may have worked on several different projects. I can't use a summary of all the hours worked on that project because they are'nt broken down by employee. So I'm stuck!!!!  :-(

Here's a poor example of an Overtime calculation that demonstrates what I'm trying to accomplish.

If ([Employee]’s [Total # of Hours this week] on [Project] >= 40, (([Total # of Hours this week]-40)*[Overtime Rate]), null)

Can anyone help point me in the right direction? I'm going crazy! ;-)

Thank you,

-Chase-

Photo of Chase

Chase

  • 0 Points

Posted 3 years ago

  • 0
  • 1
I have done something similar in other apps.  You will need to have a table with a compound key such as the hyphen separated concatenation of Related Employee, Related Project, and the Sunday if the week. Then that table will hold the hours you are looking for and these can be looked up down to other records.

The manual solution is to create a report of time records not linked to these Parents, and create URL formula button to create those Parents, one at a time. Say create that parent and refresh the report until there are no more time Reid's left on the report.

Once that is working, and you have been able to integrate those totals into your time cards, then the next step will be to at least semi automated create those records.

As I really don't do much script, I found a way to use relationships and API RunImport to get them created with a few successive button pushes. But that's more complicated than I can think through here on this forum as to how to do that for how your app is built, without having access to the app. We could carry on this conversation off line if you like.
Photo of Chase

Chase

  • 0 Points
Thanks Mark! I think I'm following you. Any chance you could hop on a call today? I'll send you a PM.