How can I have a look-up field that grabs the hourly salary for an employee as it was on the date that employee worked?

  • 0
  • 1
  • Question
  • Updated 1 month ago
  • Answered

I have a WORKSHEETS table; this table has a field called Worksheet Date.

I have an EMPLOYEES table; this table has a field called current_hourly_salary. 

I have a LABOR ENTRIES table which captures the employees that worked on each Work Date, how many hours they worked, and that the cost of labor was (current_hourly_salary x hours_worked). Each record in this table has a Related Worksheet (LUF: worksheet_date) and a Related Employee (LUF: current_hourly_salary).

Labor Entries } Worksheets { Employees

The employees' salary changes at least 3 - 4 times per year.  I want my Labor Entries to calculate the cost of labor using the employee's salary as it was at the time he/she worked... based on the Worksheet Date; so that whenever we go back to look at at worksheet from January, the labor calculations will use the employee's salary as it was in January, not as it is now.

Note: I do not want users to have to manually enter an employee's current salary every time they make a Labor Entry. This would be too cumbersome and time consuming and would defeat the purpose altogether.

I realized that another table was needed... HOURLY SALARIES, to keep historical salary data for each employee. This table has a Related Employee, Hourly_Salary, and Effective_Date.

The current_hourly_salary field in my EMPLOYEES table is now a summary field from HOURLY SALARIES that brings in the latest (or current) Hourly_Salary based on the Effective_Date.

Labor Entries } Worksheets { Employees { Hourly Salaries

But this still does not resolve the issue; while I can now keep historical salary data, I still don't know how to retain the hourly salaries for LABOR ENTRIES with earlier labor dates.  As it is, my labor calculation uses the employee's current_hourly_salary as it is now because it's a look-up field which always grabs the most current hourly rate for that employee.

Any assistance you can provide will be greatly appreciated.

Juan

Photo of Juan

Juan

  • 480 Points 250 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
I would float up the most current rate and its effective date along with the previous rate and it's efefctive date to the parent record using a reverse relationship.  Then look that up down to the time entries and then use the snapshot feature to freeze that data.
http://www.quickbase.com/user-assistance/Default.html#setting_up_snapshot_fields.html

Then down at the time entries I would select the snapshot which applied to the date worked.  That way the user could preload salary changes ahead of time.

Post back if you need need help with the reverse relationship setup.
Photo of Juan

Juan

  • 480 Points 250 badge 2x thumb
Thanks Mark, the snapshot feature is exactly what I was looking for.
Great, thanks for letting me know.
Photo of Juan

Juan

  • 480 Points 250 badge 2x thumb
Hi Mark,

I have a follow up question.

Let's say that 50 labor entries were created last month, and the snapshot field captured the labor rate at $15/hour based on the above setup.  

However, this month we realized that the correct hourly rate for last month for that labor code should have been $17/hour.

Obviously we would make the modification in the Labor Rates table, but... because at the Labor Entries level the hourly rate was frozen in the snapshot field, the correct hourly rate will not update.

Is there a way to ensure that when a pay rate is updated at the parent table level (Labor Rates) with a specific effective period, any records in the Labor Entries table <with a work date that falls within that period> also get updated?
Photo of Jeff

Jeff

  • 266 Points 250 badge 2x thumb
This last question is what I was looking for. I wanted a way to do this without the snapshot function. The payment amount would calculate based on the last effective pay increase date that is before the date of labor.