I am trying to create an annual leave calendar. I have it set up so each annual leave for an employee is an event (so if I take off on 23 March, that is one day of annual leave). Is there a way to designate a set number of annual leave days for an employee (41 days per year) and then have SharePoint calculate the remaining number of annual leave days the person is entitled to after adding in a new event (my 41 days per year becomes a remaining 40 days per year)?
SharePoint is not the best place for these types of projects, but here's a possible solution if you are using SP2013 or later and your farm has everything installed to support 2013 style workflows.
Keep in mind:
- A list is needed with the total number of days allowed for each employee (assuming they don't all get 41 days per year (Really, 41? What kind of job is this? :-) )).
- Any row could be updated at any time, so the workflow must check and update every row in the current year for that employee.
- Two "advanced" workflow features are used: Looping and REST API calls.
- Workflow is triggered by the edit of any one item.
- Workflow reads the column with the employee's ID and uses the REST API to read the number of leave days from another list with at least an employee ID and max leave days.
- Workflow uses the REST API to read a date ordered list of all rows for the current year of the current item's employee.
- Workflow loops through all rows calculating the remaining days and updating each row as needed.
- ??? Workflow sends an email to someone if more than max leave days requested.