Preserving existing record values while updating a pay rate

[Bobby_Gay]

I have a payroll base that tracks timesheets and commissionable transactions for our employees. It includes the following linked tables:

• Employees
• Timesheets

The Employees table includes information for name, address, email, etc. Additionally, there is a Straight Time (ST) Hourly Rate field. We have thousands of records in the Timesheets table from daily entries submitted by employees. The submissions simply consist of:

• Employee Name
• Start Date/Time
• End Date/Time

Formula fields calculate the number of hours worked. To calculate ST Gross Pay, a lookup field displays the ST Hourly Rate from the Employees table. That is multiplied by the number of hours worked.

We recently decided to give an employee a raise in his ST Hourly Rate. My problem is that if John has a ST Hourly Rate of $20 and I change it to $21 then, based on the present calculations in the Timesheets table, all my historical records reflect the new $21 rate, which is not accurate.

It was suggested that I create a Rate History table linked to the Employees table that includes the following fields:

• Employee
• Effective Start Date
• ST Hourly Rate

I did that, and entered the employees' existing ST Hourly Rates using their date of employment as the Effective Start Date and then applied the new rate to the employee that is to receive the increase from $20 to $21. To test this, in the Employees table, I left the ST Hourly Rate (Original) in the Timesheets table, which is a Currency field.

I added a Rollup field that retrieves the ST Hourly Rate (new) from the Rate History table. It includes a filter where Effective Start Date is on or before today, and added an aggregation formula of MAX(values). In the Employees table, I placed the ST Hourly Rate (Original) field next to the ST Hourly Rate (Rollup). to visually confirm the data is correct. The before and after values are correct.

The Effective Start Date I used for the new rate was 2/19/24. I made a test entry in the Timesheet table with a Start/Stop Date of 2/19/24. It did the ST Gross Pay calculations correctly, but it also changed the hourly rate of the historical records to $21.

How do I preserve the records with the previous rate? 

[TheTimeSavingCo]

I think the best way to handle this would be to apply the current rate whenever a Timesheet record gets created via an automation, and so the rate would no longer be a lookup but a Currency field.  I can't think of a way to do this with just lookups / rollups since you can't dynamically filter those based on the current record's data

[Bobby_Gay]

Thanks Adam. Could you share with me how I should set up the automation? I am not a programmer, but I never realized this would be such a challenge.

Bobby

[TheTimeSavingCo]

Sure!  Assuming you have a Rate table like this:

We would always be able to identify the latest rate by looking for a record that didn't have an "End" date set

This would allow us to create an automation that would:

1. Trigger when a new Timesheet record has an Employee linked but no rate

2. Look for a record in "Rates" that matches the employee and has no end date:


3. Use that found record's rate to update the Timesheet record:

Link to base

[Bobby_Gay]

Thanks Adam. I'll give this a try.

Bobby