A Learning Management System in SharePoint

I’ve been thinking about how SharePoint can act as a relational database with interrelated lists or tables.  Typically, a company would use Microsoft Access as a basic database, but SharePoint, in some situations, has the advantage.

• SharePoint is easier to access (ironically) because it is a web portal
• The SharePoint interface is more attractive than that of Access
• Integrating the data into other functionality is easier. For example, if a company wants to manage customer activities with a SharePoint customer relationship management system, the data from that system can be easily shared with a delivery and project management system.
• SharePoint is possibly less expensive

Of course, Access also integrates directly with SharePoint Enterprise, so in some cases, combining the systems will make the most sense.

Learning Management System

I recently fielded a request to build a learning management system. The company didn’t need anything so complex or expensive as what was on the market. They just wanted a simple LMS that tracked students, registrations, classes, class dates, and certificates of completion. It was a perfect example of a relational database.

Microsoft Access could of course handle the job, but the company didn’t own that product. SharePoint was a better fit because it didn’t have an additional licensing cost, and the data from that system needed to be shared throughout the SharePoint site.

The system I’ve proposed to them would include a few more bells and whistles than what I built for this example, but all of the bones are there.

My SharePoint LMS

I started by drawing out the tables I would need for the system and figuring out how they would relate to one another.  I needed these lists and libraries:

• Students
• Classes
• Class Calendar
• Registrations
• Certificates of completion

The lines between the lists show how they are interrelated. Registrations, for example combine information from the Classes list, Student Lists, and Class Calendar. Drawing everything out helped refine the idea and prevented me from painting myself into too many corners.

Student List

A real company would probably want the student list to come from their payroll system or something else, but in this case, I just created a simple SharePoint contact list.

Users hate filling in the same data twice, so the Full Name field by default is seldom used. I deleted the default Full Name field and then added it back, using a calculated field that generates the full name. The formula was

[First Name]&” “&[Last Name]

I also added department and current fields. The system needs to keep records for former employees too.  A “Current” metadata field allows for easy filtering to see only current employees.

Classes

The Classes list includes all of the types of classes that a student could take, not the instance of the class on a specific date.  This could also connect to a database, or even connect to eLearning material, descriptions, rosters, etc., but I just kept it simple and used a customer list.

Training Calendar

The training calendar is a fairly standard SharePoint calendar web part.  I added a couple of custom fields to describe the training for simple sorting and filtering of events.  I also referenced the class list for data integrity purposes.

With SharePoint Designer, you could also count the registrations for the classes and even check to make sure classes haven’t overfilled.  Also, adding registrations to the system or scheduling classes could be accomplished through a workflow form to prevent users from screwing up the data. A live production system would probably require these features, but I didn’t go that far.

Registrations

The registration table ties together the students and the classes that they took on a specific day. I could see using a calendar for this part, but decided on a custom list because the Calendar web part forces you into selecting start and end times for every event. I’d rather keep the date information in the Training Calendar and just reference it here.

Every registration record is kept indefinitely, even if the student cancels the class or no shows. I used a status column to record if they attended the training.

Certificates of Completion Document Library

This is a simple document library that takes metadata from the classes and registration tables.  With document libraries, the less metadata you use, the better.

Ideally, each document could reference the data about a registration (date, topic, level, etc.), but because I stored that data in the training calendar it could not be brought in through the registration record. The only information stored in the registration record was the record number.

Reorganizing the data could probably solve this problem, or SharePoint Designer magic could do its thing.

Final Learning Management System

Of course, a real company would want pages for classes, each department, reports, etc.  I just put everything on one page to show how it comes together.