I have an employee table (parent) that includes a "Benefits Eligibility Date (Start Date plus 90 days)." I have a PTO table (child) that has a multiple choice field "Leave Request Type (Vacation, Sick, Professional Development)," a numerical field "# of OOO Days Requested," and a multiple choice field "Approved/Not Approved." From the Benefits Eligibility Date employees are eligible for 10 business days of vacation annually (not calendar or fiscal year but within the year after they started - Eligibility Date). If # of 000 Days Requested are equal to Vacation and Approved, on the employee table I want to be able to calculate the total number of approved vacations days during the current year, another field for the unused available days for the current year, and then after the anniversary Benefits Eligibility Date is passed for the current year the number of vacation days for the following year calculates the 10 vacation days for the next year plus unused vacation days from the previous year.
Does that make any sense?
Another caveat - I need some sort of "override" for those that may negotiate something different into their employment contract.
I've written some doozy of formulas before but I am stumped on this one. Any thoughts?