I’m having problem finding a workaround for dealing with working hours in an H&R app and I kindly ask for your help.
My client needs to list the employees on the rows, days on the columns and working hours records (day_type, start_time, end_time, overtime – a complicated formula to calculate overtime) for each day in a given month. I attached a sample screenshow below.
I have an Employees table that stores the employee_name, is_active (Boolean), a Sessions table that stores employee_name, start_date and end_date (blank for still working employees), a Workdays table storing employee_name, workday_type, workday_date, start_time, end_time and overtime information.
Approach 1 - I have tried using pivot tables to show total overtime assigning employee_name to columns and day_number (a formula column that takes DAY(workday_date)) but when user does not add a record for a specific day, it doesn’t show up on the columns because there are no records associated with that date.
Approach 2 - I came up with a solution to create a Periods table that stores period_text (i.e. 2024 – 07) and a Periods_Employees table (the exact same table in the screenshot) that stores period_text, employee_name, day_1, day_2, day_3 … day_31 linking all the day fields to Workdays table one by one. I was thinking of writing a workflow that will execute on creation of a new Periods record and loop through each active employee for that period and create 31 empty workday records for the next employee and link them to 31 fields in Periods_Employees table, but I can only get data from the trigger (Periods) and it doesn’t allow me to loop through anything.
I’m really stuck with this as my client wants to see the whole month and wants to see which cells are occupied, which cells are not. They also want to have the opportunity to edit cells by clicking on the values, that’s why I tried the second approach.
Looking forward to suggestions, thanks!