2020-05-16: A new blog series on this topic will not start.
2019-01-15: This solution got a different setup then the one presented in 2018-10. I therefor stop this blog series and start a new one begin February on this solution. I will add the link to the first blog post of that series here. This blog post will not be deleted.
On 2018-10-18, I gave a presentation about the business solution “Employee capacity planning” I created with: SharePoint Online (Modern team site), PnP PowerShell and PowerApps. The 45 minutes were over before I knew it. Several topics were not, or in lesser detail, presented. I therefor decided to start a blog post series specially for this.
The blog post series has the following topics:
- Introduction
- The start screen and the menu
- Panels
- Week overview
- Combined controls
- Power BI
- And something more
The minimum resolution of this solution is the number of days per week. Details for a specific day were thus not needed.
Introduction
The business solution “Employee capacity planning” supports what the name implies: Maintaining a capacity planning for employees. Entities for this solution are:
- Employees
- Jobs
- Planning
- Overview
When talking about functionalities, a roadmap was created meaning the first release contains a subset of the required functionalities. This blog post series is about the first release.
The solution uses one modern team site. The site navigation was update to contains links to the four apps (links under “Home”) and to the lists “Employees” and “Jobs”. Data in these lists is added/updated directly.
Apps
This solution started as one app, but because the number of controls became rather large, I divided the solution in four. One for every entity.
All the apps have a screen size of 1920 x 800 with a locked aspect ratio.
Employees
Employees could be active or not-active. Employees are never deleted because of the requested overview functionalities.
An employee has only a few properties:
- A unique ID
- The list item ID
- A name
- The amount of workable days a week
- Active yes/no
Jobs
A job can also be seen as a project. A job has the following properties:
- Title
- Company
- A lookup column is used
- Year
- The begin week
- The end week
- The amount of requested days a week
- The amount of requested employees
Because PowerApps can only collect 2000 items at maximum, the data had to be segmented. A year is used for this meaning a job is always per job. When a job spans more years, multiple jobs had to be made. This was not a problem for the solution owner.
Data structure
All data is stored in SharePoint Online list. For the first release, 9 SharePoint lists were created with PnP PowerShell:
- Companies
- Employee details
- Employee year details
- Employees
- Employees and jobs
- Job details
- Job year details
- Jobs
- Years
The list ‘Employees and jobs’ connects the employees and the jobs. The list “Employee details” contain an item per employee per year per week. It also contains the actual amount of workable days for that week. The list “Job details” contains an item per job per year per week. It also contains the actual amount of requested days and employees for that week. The year detail lists contain per year which employee/job has been assigned. The chapter “Assigning employees/Jobs” contains more details about assigning.
SharePoint Online is not a relation database. The solution requires a relation setup though. It is PowerApps which connects the SharePoint lists together.
Assigning employees/jobs
To store data, 2 setups are possible in theory:
- Store a definition and store the changes compared to this definition.
- Store a definition and assign once (!) all week details.
The first option was investigated by me but I could not think of a workable solution. Therefor, the second option was used.
The app for employees has a functionality to assign week details to an employee for a specific year. The list “Employee year details” contains an item per employee per year meaning employees have to be assigned to every year they are employed. The list “Employee details” contain per employee per year per week an item. After assigning a week to an employee, weeks could be deleted making these week invisible . Deleting a week actually means putting the value -1 in the related week. An employee item is thus never deleted.
The setup described above for employee is also made for jobs. The only difference is that job items can be deleted because only the amount of items per week are added when assigning a job. Example: When a job spans 12 weeks, only 12 items are created.
Overview
No overview is possible without the administration of data. Some data is administered directly in SharePoint and some data is maintained via the apps.
An overview view app is also created. This app shows the capacity planning for all active employees for the next 9 weeks in a year starting from a manually selected week in a manually selected year.
Power BI is also used for overviews. More details about Power BI is shown is blog post #6 of this blog post series.
Screenshots
Week details employee
