Good day,
THE STORY
We have a consulting firm that is busy with a staffing optimisation project and the consultant phoned me to ask if I can add a Spreadsheet somewhere on the intranet so that the HR guys can book people for the training. So I asked the consultant a few questions about the booking, i.e. Where will it be held? How many computers per training room? How many training rooms? How many sessions does one person attend? What is the main purpose? After discussing this for a bit with him I told him that we can probably do something better with some SharePoint lists and a bit of workflow
THE DETAIL
The training will happen at one of our training centres at 2 of the training rooms. They are Training room 3 and 4 and there are 16 computers per training room. There are also 2 sessions for each day with the possibility of adding a third session. They want HR to be able to book the people for these sessions per computer but they should also not be able to delete each other's bookings. They want to see this in a calendar view and he wanted the solution later the afternoon...(+- 3 hours later)
THE IMPLEMENTATION
At this point I had a quick discussion with my colleague Leon Haasbroek and we discussed a few possible solutions. Seeing that I didn't have a lot of time I decided on a quick and dirty solution because of the time limit.
SharePoint
I created a new calendar list and a custom list for all the dates. I created the Possible booking days (custom list) with three columns with the data (data was built in Excel and uploaded via the Datasheet view)
- Computer identifier
- Status
- Date
In the calendar list I added columns with Company relevant information, i.e. Employee name and Surname (Single line text because not all the guys have got email), Company/Personnel number, Position (Job title), Shaft (Business Unit that h/she works at) and then I pulled in the lookup fields for booking the date and computer. I also configured the SharePoint Calendar list so that a user can only edit or delete the entries created by him or her. This will solve the issue of guys removing or bookings not made by them. I changed the form with Nintex Forms
The Date selection filters the Possible Bookings Date list on the date field and the Training room field uses a view that already filters the list on status Open only. So when I get to the Training Room selection I only see available computers for the date. The quick and dirty that I mentioned is that I combined a few fields into one field In the form I also save the ID of the selected date in the ID connected to field.
You can see in this list I combined the Training Room - Computer nr - and Session all in one field. If I had more time I would split this and not have a list of 1280 instance but as mentioned it was quick to create the records in Excel.
The workflow on the Calendar list does 3 things:
- First step is to go and book out the date in the possible dates list by changing the status to booked.
- Secondly it creates the booking in the calendar list
- Thirdly, if the Send email tick was ticked will get the employee's email and other details from SAP and send an email to the employee, manager and CC the HR person that made the booking.
In the Possible Dates booking step I just use and Update Action.
In the Create booking step I had to determine the session from the last digit because it was a combined field and then according to the session calculate the start and end times. This was unfortunately hard coded in the workflow. The ideal would be to have this in a list so that it is easy to add sessions. I then updated the booking accordingly.
In the send email action I test if the send email tick was selected. then I get all the information from our SAP system that I need about the employee and about the manager and then I send the email.
So that was my solution. I know...I took many shortcuts...please let me know how you would have done this differently.
Thanks for your time.
