How to: Automatically create a training certificate in SharePoint

With our Employee Training Management solution you can manage your employee training program on SharePoint. It’s very easy to provide catalogs of courses and training events, distribute course material and to manage the enrollments of your employees. Out of the box it is not possible to automatically generate a training certificate after successfully completing a training. However, this is quite easy to do using a document library with a certificate template a SharePoint Designer Workflow and a bit of receiver code.

Document library and certificate template

Create a new document library.

Add the fields you want to include in your certificate.

In my case I added:

• Training
• Attendee
• Skills
• Date
• Enrollment

For Training, Attendee and Skills, I used a single line of text columns, as it’s easier to define the format in the workflow than in the Word document. For the Date column I used Date only. The Enrollment column is a lookup to the Enrollment list. Later we’ll need this column to link the certificate to the enrollment and vice versa.

Next, create the certificate template.

Go to the advanced settings and click on “Edit Template”.

Design your certificate and use Insert -> Quick Parts -> Document Property to include values from the document library fields.

Click on save as and save the template as docx instead of dotx. This is very important because otherwise the documents will be broken if created from a workflow. Save it to the same location (certificates/forms/). Then change the name of the template in the advanced settings to docx.

Create a SharePoint Designer Workflow that creates a new document in the certificate library

Firstly you have to decide when the certificate should be generated. In my case I added a Yes/No column to the Training Events list and the Enrollment list to define if a certificate should be created after a training.

In the Enrollment list I also added a new column to allow the trainer and the organizer to mark the users who actually attended the training.

I also added a lookup to the certificate library to link the certificate to the enrollment.

OK, now let’s create the workflow.

Open the enrollment list in SharePoint Designer and create a new workflow.

Add an if condition, something like:

if Scheduled Training Events:Create Certificate equals Yes
and CurrentItem:Attended equals Yes
and CurrentItem:Certificate is empty

Now we have to add the action to create the certificate. Use the action “Create List Item”.

Click on “this list”, select Certificates and set the following fields:

• Path and Name -> Certificate_[CurrentItem:ID] (see below, update 12/11/2015) 
• Date -> Current Item:Event Date
• Training -> Current Item:Training (Lookup value as text)
• Attendee -> Current Item:Full Name
• Title -> Current Item:Title
• Skills -> Current Item:Primary Skill (Lookup Values, Comma Delimited)
• Enrollment -> Current Item:ID

Update (12/11/2015): Do not use “Current Item:Title” as the path and name as this can be too long or contain invalid characters. Instead you can use something like: Certificate_[CurrentItem:ID]

Save the output (List Item Id) to a new workflow variable “Variable: certificate”.

The last workflow step is now to link the certificate to the enrollment. Use “Set field in current item” and set the certificate field to the certificate workflow variable.

Lastly we have to configure the start options so that the workflow starts whenever an enrollment is created or updated.

Now let’s publish the workflow and run some tests.

Firstly we have to schedule a training with “Create Certificate” set to yes.

Now I will enroll myself.

Now we have to mark the user/enrollment as having attended the training in order to start the workflow.

Now let’s check the certificates library.

Voila, we have our first training certificate.

Now let’s check if the certificate is also linked to the enrollment.

Looks good to me.

Event Receiver Code

Of course you don’t want to send a word document to the attendees, that’s why we need a little bit of receiver code to convert the word document to a pdf.

I created a simple SharePoint solution with one event receiver registered on the certificates library.

<?xml version="1.0" encoding="utf-8"?>
<?XML:NAMESPACE PREFIX = "[default] http://schemas.microsoft.com/sharepoint/" NS = "http://schemas.microsoft.com/sharepoint/" /><elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <receivers listurl="Certificate">
      <receiver>
        <name>EventReceiverItemUpdated</name>
        <type>ItemUpdated</type>
        <assembly>$SharePoint.Project.AssemblyFullName$</assembly>
        <class>ConvertDocToPdf.EventReceiver.EventReceiver</class>
        <sequencenumber>10000</sequencenumber>
      </receiver>
  </receivers>
</elements>

<?xml version="1.0" encoding="utf-8"?>
<?XML:NAMESPACE PREFIX = "[default] http://schemas.microsoft.com/sharepoint/" NS = "http://schemas.microsoft.com/sharepoint/" /><elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <receivers listurl="Certificate">
      <receiver>
        <name>EventReceiverItemUpdated</name>
        <type>ItemUpdated</type>
        <assembly>$SharePoint.Project.AssemblyFullName$</assembly>
        <class>ConvertDocToPdf.EventReceiver.EventReceiver</class>
        <sequencenumber>10000</sequencenumber>
      </receiver>
  </receivers>
</elements>

The receiver is registered on ItemUpdated, because the workflow sets the fields in a second update.

In the Updated receiver I use the SyncConverter to convert the docx into a pdf. This is new in SharePoint 2013. If you use SharePoint 2010 you can use the ConversionJob instead. This converts the document asynchronously using a the word automation timer job.

using System;
using System.Security.Permissions;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Utilities;
using Microsoft.SharePoint.Workflow;
using System.IO;
using Microsoft.Office.Word.Server.Conversions;
 
namespace ConvertDocToPdf.EventReceiver
{
    /// <summary>
    /// List Item Events
    /// </summary>
    public class EventReceiver : SPItemEventReceiver
    {
        /// <summary>
        /// An item was updated.
        /// </summary>
        public override void ItemUpdated(SPItemEventProperties properties)
        {
            base.ItemUpdated(properties);
            using (SPWeb web = properties.OpenWeb())
            {
                var spFile = properties.ListItem.File;
                //only if docx
                if (spFile != null && spFile.Url.ToLower().EndsWith(".docx"))
                {
                    using (Stream read = spFile.OpenBinaryStream())
                    {
                        using (MemoryStream write = new MemoryStream())
                        {
                            var wordAutomationServiceName = "Word Automation Services";
                            var sc = new SyncConverter(wordAutomationServiceName);
                            sc.UserToken = web.Site.UserToken;
                            sc.Settings.UpdateFields = true;
                            sc.Settings.OutputFormat = SaveFormat.PDF;
                            var info = sc.Convert(read, write);
                            if (info.Succeeded)
                            {
                                //save the converted bytes
                                spFile.SaveBinary(write.ToArray());
                                //change the filename to .pdf
                                var newUrl = spFile.Url.Substring(0, spFile.Url.Length - 4) + "pdf";
                                spFile.MoveTo(SPUrlUtility.CombineUrl(web.Url, newUrl));
                            }
                        }
                    }
                }
            }
        }
    }
}

using System;
using System.Security.Permissions;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Utilities;
using Microsoft.SharePoint.Workflow;
using System.IO;
using Microsoft.Office.Word.Server.Conversions;

namespace ConvertDocToPdf.EventReceiver
{
    /// <summary>
    /// List Item Events
    /// </summary>
    public class EventReceiver : SPItemEventReceiver
    {
        /// <summary>
        /// An item was updated.
        /// </summary>
        public override void ItemUpdated(SPItemEventProperties properties)
        {
            base.ItemUpdated(properties);
            using (SPWeb web = properties.OpenWeb())
            {
                var spFile = properties.ListItem.File;
                //only if docx
                if (spFile != null && spFile.Url.ToLower().EndsWith(".docx"))
                {
                    using (Stream read = spFile.OpenBinaryStream())
                    {
                        using (MemoryStream write = new MemoryStream())
                        {
                            var wordAutomationServiceName = "Word Automation Services";
                            var sc = new SyncConverter(wordAutomationServiceName);
                            sc.UserToken = web.Site.UserToken;
                            sc.Settings.UpdateFields = true;
                            sc.Settings.OutputFormat = SaveFormat.PDF;
                            var info = sc.Convert(read, write);
                            if (info.Succeeded)
                            {
                                //save the converted bytes
                                spFile.SaveBinary(write.ToArray());
                                //change the filename to .pdf
                                var newUrl = spFile.Url.Substring(0, spFile.Url.Length - 4) + "pdf";
                                spFile.MoveTo(SPUrlUtility.CombineUrl(web.Url, newUrl));
                            }
                        }
                    }
                }
            }
        }
    }
}

With this receiver registered, the certificate will be automatically converted into a pdf.

Now the enrollment is linked to a pdf. What you can do next is to send an e-mail to the attendee including a link to the certificate or for example set permissions using a workflow in the certificates library to allow attendees only to access their certificates.

I hope this was helpful. If you have any questions, please send an e-mail to support@sapiens.at.