Many consultancy firms use an external system to maintain the planning of their employees. In most of these planning tools the user's personal Outlook calendar is not integrated. This means that employees need to maintain their own planning in sync.
The idea of this application was to make things simpler for our employees by creating an Office 365 application which keeps their calendar in sync with the external planning tool. In the application itself, they can see their own planning and that of a colleague, and they have the ability to synchronize their calendar manually or automatically.
How it works
We first started with an application that extracted all the clients and events from an external planning tool via an XML data connection. Soon we noticed the potential of the application, so we turned this into an automatic process that synchronizes the events with our SQL database that is accessible via a Web API on Azure.
Users that login into the application have the ability to view their own planning and that of a colleague. We incorporated a functionality that gives a visual hint when consultants are allocated for the same client. This could make it easier to plan things like carpooling and meetings.
Of course we know that an extra tool in the company would be something that eventually will stay unused. So we added the ability for the user to automatically synchronize their planning automatically and to receive mail notifications when their are updates made to their planning. This could be useful when you were not aware of new clients or planning updates.
All these functions are also available from within Outlook via add-in integration. The Outlook Add-in enables the users to send an overview to their clients when the consultants will be present.
Another advantage is: the manager or planning operator does not have to send exported Excel files to their clients. They can just add the overview of the planned days into the mail via the add-in.
How we built it
We have built it with various technologies. First we started with a simple MVC application to visualize the user's planning from the external planning tool. Quickly we noticed the potential and also the problem that could occur when a lot of users will make use of this tool; the connection would be limited. So we went for another approach and we developed a Web API on Azure with a SQL database. A scheduled web job on Azure: it runs every hour and synchronizes all the data from the external planning system into our SQL database via REST end-point (Azure Web API).
After the web job is done synchronizing, which typically takes about 1-2 minutes (depending on the number of people and changes), it synchronizes the planning of each individual to their Office 365 calendar; only if auto-synchronization is turned on. For adding and updating events in the Office 365 calendars, we have used the Microsoft Graph API. As we do not want to update the planning of the user each time the job runs, we added an identifier which enables us to check if it is an event created from the planning application. This enables the application to update or remove events.
The interface of the application and add-in have been built by making use of the Office UI Fabric.
Here is a diagram of the components:
Challenges we ran into
Microsoft Graph API and the time zone gave us a little headache. The API has changed many times before it went GA and there were a couple of drastic changes to the date format of adding or updating events.
Accomplishments that we are proud of
These external planning tools are difficult to work with if you do not know everything about them. In this respect, we are very proud that we now have an application which enable our colleagues to just use the tools they know which is Outlook and a website.
What we learned
We have learned a lot during the creation process of the application. We have tried to use new technologies such as the Microsoft Graph, Office UI Fabric, Azure Web Jobs, Azure Web API.
What is next for the Online Planning System
We are now trying to find a way in order to make planning changes in our application. The next step is that the application will send an email of approval to the team manager. Once he approves the email, the changes will be updated in the external system. The challenge here is that not all external systems allow changes.