Adding holiday requests and displaying number of days used/left in a different list
Hi,
I have a sharepoint list that has our employee details with several columns, one of whihc is "Number of days Holiday" = 25.
In a seperate list with Calendar view, users create Holiday requests which (data filled in is start date, end date and number of days used) are then sent via the Approval workflow, the calendar is filtered to only show apporved holidays.
I would like to show in the first "Employee data" list the number of holidays used and then in a seperate column show number of days left.
i.e. Number of days holiday = 25 (employee list) -> Days Used = 1 (calendar list) -> Days left (calculated in the employyes list) = 24
This is simpole to do in Excel but in Sharepoint i cannot for the life of me see how to calculate between 2 lists easily.
Thanks!
I have a sharepoint list that has our employee details with several columns, one of whihc is "Number of days Holiday" = 25.
In a seperate list with Calendar view, users create Holiday requests which (data filled in is start date, end date and number of days used) are then sent via the Approval workflow, the calendar is filtered to only show apporved holidays.
I would like to show in the first "Employee data" list the number of holidays used and then in a seperate column show number of days left.
i.e. Number of days holiday = 25 (employee list) -> Days Used = 1 (calendar list) -> Days left (calculated in the employyes list) = 24
This is simpole to do in Excel but in Sharepoint i cannot for the life of me see how to calculate between 2 lists easily.
Thanks!
PS you can make Holidays Used column hidden if needed, or just remove it from user Views
ASKER
What would happen if the employee decided not to take holiday and we deleted the holiday from the Calendar list?
This happened a few times last year.
I need to Sum all the approved holidays in the calendar list and take that figure and isert it into the employees list.
So no matter whats happens i have upto date figures.
:)
This happened a few times last year.
I need to Sum all the approved holidays in the calendar list and take that figure and isert it into the employees list.
So no matter whats happens i have upto date figures.
:)
well, you can create one more workflow and run it on ItemDelete to recalculate Holidays Used Count back :) and you don't need to Sum all everytime, workflow will keep the values in sync for you by adding/removing Holidays count right after the Holiday item added/removed
ASKER
hi thanks for the info.
I have not used SPD before to Add a number to a list, any suggestions to help me?
I have not used SPD before to Add a number to a list, any suggestions to help me?
there is pretty good step by step instruction on exactly your case (with a little bit different approach)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
1. create new column on Employee list to hold Holidays Used Count
2. Update it with simple workflow after new holiday get approved
3. Create new calculated column on Employee list and display data by simple formula as in Excel