I am considering creating a system for tracking timesheet entries in Monday.com. One board will contain Employee information and another connected board will track Timesheet information. The Employee board will include a Straight Time Hourly Rate column.
Hours Worked will be a formula that subtracts the Start Date/Time from the End Date/Time. Straight Time Gross Pay is a formula that multiplies Hours Worked by the mirrored Straight Time Hourly Rate field in the Employee table.
Using airtable previously presented a problem. When an employee receives a raise (e.g. $12/Hr to $13/Hr) the Straight Time Hourly Rate changes. When that changes, all the historic timesheet records changed from $12 to $13 per hour, which is incorrect. Is there a way to preserve the $12 entries and, from the effective date of the raise, properly calculate the $13 entrties?
At this time, it is expected that those changes reflect to those past records - apologies for the setback.
A way around this would be to create a seperate forum column once the raise has been implemented to measure the change moving forward, while not impacting previous records. I recognise this isn’t the most ideal solution, however with the way the system works, I believe this may be the best way to achieve this natively
Column Magic, a third-party app, has the ability to copy a column to another.
I am assuming the hourly rate is being pulled from the connected item in the employee info board, and is a mirror column on the timesheet tracking board?
Column Magic could be configured that when the connected item changes, copy the rate mirror column to a real numbers column. Then it will only change if you change the connected item in the future.
That connected item change is probably triggered by whatever automation is matching your entry to the employee.
