First, your schedule needs to be a table in your database so that you can look up the "allowed" vacation days from it for a given employee.
Next, I'd use a related table where a record related to your table of employees is created each time an employee takes vacation. You can compute total days taken from this related table to use in combination with the days allowed to compute the number of days remaining.
Note that there are typically other details to this process not yet specified in your post--what happens to vacation days from previous years that were not taken? Your company's policies for this will determine whether you need to maintain a field where you keep a running total of all unused vacation days that spans more than the current fiscal year.
Thanks for the reply. Now, I have two Tables (Employee Table & Vacation Table)
In my Employee table I have the following fields
Employee_ID (Get UUID)
Vacation_ID (Get UUID)
Employee Table and vacation Table is connected with
Employee_ID (Get UUID) = EmployeeID (Allowed creation ; delete related records)
The unused vacation days for the current year will be forwarded next year.
is this enough info to calculate the Days_allowed?
Good afternoon karen_joy,
I hope your day is going well. I recently created a similar solution, and I thought it might be useful to list some of the considerations I had to account for during development:
- Should total time be tracked in hours rather than days - my company permitted sick-time to be taken in 1 hour increments?
- How do you account for varying work schedules?
- How do you account for vacation requests that span non-workdays (e.g., weekends, holidays, etc...)?
- How to automagically account for "floating" company holidays (e.g., Easter, Thanksgiving, etc...)?
- How to track time off request status (e.g., approved, denied, approved without pay, pending, etc...)?
- Does paid time off (PTO) reset for everyone on the same date, or is it based on hire date anniversary?
- If the PTO reset date is common for all employees, how is the prorated time for the first year handled?
This is not an exhausted list, but if I had possessed the wisdom you demonstrated in reaching out to this community before beginning my project, and someone had provided a similar list for me, I would have more hair today. Good luck!
All is well. Thank you for asking & Thank you for the list, May be as I go on, I will be going back to your list but as of now, I want to get help to calculate the vacation allowed base on their year of service.
I would put the days allowed and days remaining in the employee table, not the vacation table.
Thanks for the suggestion Phil