You have never seen SharePoint like this before 😉

As our journey through the MS365 ecosystem progresses, we are getting more and more interesting questions about the possibilities and features it possesses (and which it doesn’t!). Against popular belief – ‘you probably can’t do this or that because I can’t find a button for it‘ – there are practically no limits to the extent to which existing functionality in and around the applications of MS365 can be expanded.

One of these questions concerned a pretty old and classically designed PHP/MySQL database system that a client of ours had been using for over 10 years. The application was almost impossible to maintain: there had been some bugs in the frontend leading to data-pollution over the years in the backend that had now reached a critical point and no code updates had been deployed during its entire lifecycle. It was based on a 2012-build of the (at the time) open-source project SugarCRM.

Context

The context for the system is certificate and course management for employees of different companies: our client provides safety and emergency response training courses where companies can sign up their employees, they can get a certificate for these courses and these courses have to be repeated over time for the certificate to remain valid: a lot of information to be stored, as well as a lot of information to migrate!

We sat down with our client and discussed the wishes they had to replace the system. Hearing the basic 2 requirements (‘the old system used to be great and complied to our requirements, it’s just not functioning correctly anymore’ – ‘can we get a new system that we can use anywhere and on any device without complex infra-stuff like VPN-tunnels?’) we immediately knew that we should look into the possibilities of building this system using SharePoint.

Application design

We started by carefully looking at the old systems front- and backend and devising a datamodel that resembled the database schema, but would work with SharePoint. As the system relies heavily on large datasets (50.000 items in a table), we discovered early on that we would have to use indexes in the SharePoint lists that would contain all of the data. (The third big requirement for the new system being that it should contain all of the data of the old system).

From a technical point of view the application consists of:

• One SharePoint site collection containing a set of SharePoint lists containing all of the data
• A SharePoint Framework front end webpart to navigate and manage most of the complex/tied together data
• A custom displayform for managing the less complex data

Show me!

The main part is the SharePoint framework component to plan courses for attendees directly from a calendar view. The webpart relies heavily on Fluent UI controls and the PnPJS library for managing the underlying data.

Check out how the main window looks and how to plan a new course directly from the calendar view (Disclaimer: the video is in Dutch, because our client needed a Dutch system, however, we can easily add other languages):

Well that certainly doesn’t look SharePointy anymore! Leveraging the full power of the SharePoint framework and using the Fluent UI controls, we are able to create an intuitive and comprehensible UI/UX experience for the end user of the system. The main window is divided into Pivot Tabs for displaying other information, filtering and sorting through the data and to be able to make exports to Excel directly from the relevant data, as shown in the next video:

These are just a couple of features that we highlight, but hopefully this shows just how much possibilities there are using the correct tools and expertise. Of course there was also the tedious process of having to sift through the MySQL database and writing some code to import that data into the new datamodel in SharePoint Online, but that was totally worth the effort: Our client is now happily working in the new application with all of the historical information intact!

Contact us

We are happy to answer any questions about this project and other projects we are working on; Just contact us at info@jellybeanconsultancy.nl.