Certificate generation

Applies to version: 2020.1.x and above; author: Franciszek Sakławski

 

Introduction

You can automate your existing company's processes using workflow class systems. WEBCON BPS Portal gives you many tools for handling digital process automation. One of them is generating certificates for employees who successfully ended a given training course.

This article describes how to create and program such a process and how to generate a certificate and send it by e-mail.

 

Description

The process will contain two workflows:

1. Training –will store data about participants and the training
2. Certificate generation – a separate instance for every participant of the training

In addition, the process uses the Document template process to add a certificate template which will be generated for users.

The user will start the process by registering a training document in the system. Next, when the document has been registered in the system, a certificate for each user will be generated.

 

Business case

Workflows and forms

First, configure the Training workflow. It contains two system steps – start and finish and one intermediate step.

 

Fig. 1 Training workflow

 

On the form, several form fields that store data about the training and users have been defined.

 

Fig. 2 Training form

 

On the list storing the users' data, on each name there is a “Is certificate needed” checkbox. The system will be checked for whom to generate and send the certificate.

The “Certificate generation” workflow is also very simple and consists of two system steps.

 

Fig. 3 Certificate generation workflow

 

The form of Certificate generation is presented below:

 

Fig. 4 Certificate generation form

 

Action configuration

There are four defined actions:

1. On Training workflow
   1. Action which starts one Certificate generation subworkflow per participant.
2. On Certificate generation workflow
   1. Action which generates certificate in MS Word – .docx format.
   2. Conversion of the .docx format to .pdf.
   3. Certificate and congrats mailing to the participant.

The first action is on the “Generate certificates” path moving from the “Training registered” step. In order to configurate it, we need to double click it, then go to the “Actions” tab. As it is presented on Figure 5 type of the action is “Start a subworkflow (SQL)”.

 

Fig. 5 Action configuration window

 

On the „Execution condition” field you can see a nested SQL Command. You can configurate them by clicking the icon of three dots.

 

Fig. 6 SQL Query configuration

 

As the SQL Query is an execution condition, it has to return a Boolean type of data (1 – true – action is executed, 0 – false – action execution is withdrawn). You should use a conditional statement available in T-SQL language – “IF EXISTS”.

In the query we will check if there is any record on our list of participants. If the training does not have any registered participants, the certificates generation does not make sense.

Entries on the item list are in the WFElementDetails table and can be found by a DET_WFDID column storing an instance ID where the list locates. There is only one list type on this type of document, so we don’t have to narrow the query down to a specific type of list. Every returned entry for each element will be the instance of a given list.

If you don’t know the SQL language, below I will place a translation into English. But if you do understand what is written on Figure 6, please ignore next paragraph.

The SQL query can be translated as follows: “If there is at least one record in WFElementDetails table, then return 1(true), if there are no such records return 0(false).

After setting up the execution condition go to the action configuration. In order to do it, click on the “Configure” button.

 

Fig. 7 “Basic” tab in configuration window

 

On the “Basic” tab select the workflow and the first path. As Figure 7 presents, the “Certificate generation” workflow and the “Generate certificate” path have been selected. At the bottom of the window, there is a “Workflows start mode” choice list – leave the default “As a subworkflows of current instance” value. Thanks to this field, on the information panel on the right side of the screen you will see all subworkflows connected with the main workflow. After that you can move on to „Data” tab.

In order to correctly set “Start a subworkflow(SQL)” action you need to provide it with another query. The query needs to specify the number of subworkflows to start and what data should they contain.

 

Fig. 8 SQL query which return data for subworkflows creation

 

The SQL query presented in Figure 8 defines which fields will be in the new instance of the subworkflow. The query returns records with Company, Participants and Subject of the training.

As you can see, in the WHERE clause a first condition is the same as the one from the execution condition, however the second – defines whether on the list of participants the “Is certificate needed” field is checked. If the checkbox is checked the certificate will be generated.

Let’s go to the actions defined on the “Certificate generation” workflow.

The first action will generate the .docx file with data provided from the form. But before you configure them, you should do two things:

1. Create a document template with WEBCON BPS add-in for MS Word.

See: WEBCON BPS add-in for MS Word

2. Add a template to the document template workflow

See: Storage methods of document templates specifically in „Dictionary process for document templates” chapter.

A certificate document was created:

 

Fig. 9 Certificate with configured fields

 

Then, the certificate was added to the document template workflow and copied their GUID number (Globally Unique Identifier).

 

Fig. 10 Document template form

 

Let’s go to the workflow configuration.

The first action is “Generate/Update a Word file”.

 

Fig. 11 Action configuration window

 

Click the “Configure” button and on the “Template” tab select the “Create new file” option from the drop-down “Mode” list. Also, from the “Template” list select the “Document template” option. In the “Document template” field enter the „/DOCTEMP/” + GIUD number which has been previously copied from the document template.

 

Fig. 12 Configuration of .docx file generation

 

Then, go to the “Output” tab and select the “Sava as attachment” option. In the “File name” field fill in a name you want to be seen on the generated certificate.

 

Fig. 13 Configuration of generation .docx file

 

In the next action, you need to convert a file to the .pdf format – go to their configuration. The only field that you should change is the “Convert” field – change the value to the “The newest attachments”).

 

Fig. 14 Configuration of convertion into .pdf format

 

The “Convert Word to PDF” action adds another file to the attachments without removing the previous one. That’s why after the correct conversion, a document should contain two attachments – master copy in .docx and conversion in .pdf.

The last action is sending a custom e-mail to the training participant with the generated certificate in the .pdf format.

In the configuration window on the „Recipients” tab select the “Always to (people)” option and then, in the expression editor select the “Form fields” -> “Participant” -> “E-mail” options.

 

Fig. 15 Send mail configuration

 

Next, go to the “Message content” tab and set the e-mail content according to your needs and preferences. You can use the Variable editor on the right side to use data from the form.

 

Fig. 16 Sending mail configuration – content

 

On the “Attachments” tab in the “Attachments” field choose the “Send last” option., thereby the last added attachment, in our case – certificate in .pdf format, will be sent with the email.

 

Fig. 17 Sending mail configuration – attachment

 

A process has been correctly configured. Let’s try to test them.

 

Workflow presentation

First, register the training workflow and fill out data.

On the “Training registred” step click the “Generate certificates” path. If all actions in the workflow was correctly configured, the system will generate a document for each participant defined on the list (with the “Is certificated needed” option checked).

 

Fig. 18 Training form

 

After completing this action, on the information panel on the right side there are two links to the documents. Each one of them represents a certificate per participant.

 

Fig. 19. Information panel (right side of the form)

 

As we can see the documents have been successfully generated. Let’s move on to one of them to check if information provided on the form is correct.

 

Fig. 20 Certificate generation form

 

There are two attachments on the document. One in the .docx and one in the .pdf format. Below, you can also see a generated e-mail with the attachment.

 

Fig.21 Email with the certificate

 

Let’s check whether the certificate generated correctly.

 

Fig. 22 Certificate

 

As we can see above, the certificate looks just fine. It means, that the process has been accomplished.

 

Summary

On the example presented above, you can see that thanks to the correctly configured actions in the system, the workflows can be automated and a user should also complete data.

Certificate generation process is a great example of how big variety of enterprise activities can be automated. While training for two participants seems like a piece of cake, lets think about manual sending certificates to hundreds of users or more.