Certificates of Achievement for successfully completing courses in ecLearn is possible with some customization work. The following document will describe how to create a usable certificate of achievement using a Microsoft Word template and a Power Automate Flow.
The Certificate Template
Any Microsoft Word document will do. The important part of creating the template is setting up Content Controls. Download the sample certificate template for an example of how to use content controls in a certificate.
The attached template includes three content controls:
- FULL NAME
- COURSE NAME
- DATE
These controls will have their content replaced with appropriate values based on the Learner and Course completed.
Once the certificate has been created, you can upload it to a course in the Certificate Template field.
Power Automate Flow
In order to create the final Certificate of Completion for the Learner, each Word template will require a custom Power Automate Flow. Every template will contain different parameters, so for every different template you will need to create a specific flow that will follow the steps:
- Trigger “When a row is added, modified or deleted” on Course Attempts
- Check the Course Attempt result
- Get the Course Registration record
- Get the Learner record
- Download the certificate template
- Temporarily upload the certificate template to OneDrive
- Populate the template with values from the Course, Learner, and Course Registration
- Temporarily upload the completed certificate to OneDrive
- Convert the file to PDF
- Upload the completed certificate PDF to the Course Registration record
- Delete the temporary certificate template from OneDrive
- Delete the temporary completed certificate from OneDrive
Trigger “When a row is added, modified or deleted”
Set up the Dataverse trigger with the following values:
| Property | Value |
|---|---|
| Change Type | Added or Modified |
| Table Name | Course Attempts |
| Scope | Organization |
| Select columns | ec_result |
Check the Course Attempt result
Add a Condition where the Course Attempt Result is equal to “Pass”. If you wish to use multiple certificates for different courses, you may also need additional conditions to ensure that the flow only progresses for courses with the intended certificate for this flow.
Add all remaining steps to the “If yes” condition.
Get the Course Registration record
Add a Dataverse “Get a row by ID” step to retrieve the Course Registration record for the affected Course Attempt:
| Property | Value | Description |
|---|---|---|
| Table name | Course Registrations | |
| Row ID | @{triggerOutputs()?[‘body/_ec_courseregistrationid_value’]} | Dynamically select the Course Registration ID from the Course Attempt in the trigger step |
Get the Learner record
Add a Dataverse “Get a row by ID” step to retrieve the Learner record for the previously retrieved Course Registration:
| Property | Value | Description |
|---|---|---|
| Table name | Learners | |
| Row ID | @{outputs(‘Get_Course_Registration_Record’)?[‘body/_ec_learner_value’]} | Dynamically select the Learner ID from the Course Registration selected in the previous step |
Download the certificate template
Add a Dataverse “Download a file or image” step to download the certificate from the Course record for the previously retrieved Course Registration:
| Property | Value | Description |
|---|---|---|
| Table name | Courses | |
| Row ID | @{outputs(‘Get_Course_Registration_Record’)?[‘body/_ec_courseid_value’]} | Dynamically select the Course ID from the Course Registration selected in a previous step |
| Column name | Certificate Template |
Temporarily upload the certificate template to OneDrive
For this step, you will need a OneDrive location. In the example below, we have created a folder at the path “/Documents/Uploads” for this purpose.
Add a OneDrive for Business “Create file” step:
| Property | Value | Description |
|---|---|---|
| Folder Path | /Documents/Uploads | |
| File Name | course_@{outputs(‘Get_Course_Registration_Record’)?[‘body/_ec_courseid_value’]}_template.docx | Create a custom file name for the file to avoid name collisions. In this case, we use the Course Registration ID to ensure a unique file name. |
| File Content | @{body(‘Download_Template’)} | Dynamically select the Body from the previous download step. |
Populate the template with values from the Course, Learner, and Course Registration
Add a Word Online (Business) “Populate a Microsoft Word template” step:
| Property | Value | Description |
|---|---|---|
| Location | OneDrive for Business | |
| Document Library | OneDrive | |
| File | /Documents/Uploads/course_@{outputs(‘Get_Course_Registration_Record’)?[‘body/_ec_courseid_value’]}_template.docx | Combine the Folder Path and File Name from the previous step to dynamically construct the path/name of the file to be populated |
| dynamicFileSchema | { “1300799527”: @{outputs(‘Get_Learner_Record’)?[‘body/ec_name’]}, “-616060284”: @{outputs(‘Get_Course_Registration_Record’)?[‘body/ec_name’]}, “-1556692127”: @{triggerOutputs()?[‘body/ec_completedon’]} } | See notes below for how to retrieve the content control ID values. |
The “dynamicFileSchema” values here are the hardest part of the process. Unfortunately, it is not possible to retrieve the numerical IDs for the content controls in your template, so for each template you will need to discover these individually using the following steps:
- Upload the template to a OneDrive location
- Create a temporary Word Online (Business) “Populate a Microsoft Word template” step:
| Property | Value | Description |
|---|---|---|
| Location | OneDrive for Business | |
| Document Library | OneDrive | |
| File | /Documents/Uploads/CERTIFICATE OF COMPLETION.docx | Manually select the uploaded certificate template. |
| Full Name | @{outputs(‘Get_Learner_Record’)?[‘body/ec_name’]} | This is the “Full Name” content control. Select the dynamic Learner “Name” value |
| Course Name | @{outputs(‘Get_Course_Registration_Record’)?[‘body/ec_name’]} | This is the “Course Name” content control. Select the dynamic Course Registration “Name” value |
| Date | @{triggerOutputs()?[‘body/ec_completedon’]} | This is the “Date” content control. Select the dynamic Course Access “Completed On” value |
- With the values selected for the step, use the step’s kebab menu (…) and select “Peek code”
- Find the values labeled like “dynamicFileSchema/1300799527” and copy them. For example, in the example we have set up, there are three values like this:
- Use the IDs revealed in this step to construct the “dynamicFileSchema” values. For example, the step:
“dynamicFileSchema/1300799527”:
“@outputs(‘Get_Learner_Record’)?[‘body/ec_name’]”
Should be converted to :
“1300799527”: @{outputs(‘Get_Learner_Record’)?[‘body/ec_name’]}
The full value in this case will be:
{
“1300799527”: @{outputs(‘Get_Learner_Record’)?[‘body/ec_name’]},
“-616060284”: @{outputs(‘Get_Course_Registration_Record’)?[‘body/ec_name’]},
“-1556692127”: @{triggerOutputs()?[‘body/ec_completedon’]}
}
- Enter this final value, including the curly braces (“{“, “}”) in the dynamicFileSchema field for the dynamic “Populate a Microsoft Word template” step.
- Delete this temporary step.
Temporarily upload the completed certificate to OneDrive
Add a OneDrive for Business “Create file” step:
| Property | Value | Description |
|---|---|---|
| Folder Path | /Documents/Uploads | |
| File Name | attempt_@{triggerOutputs()?[‘body/ec_courseattemptid’]}_certificate.docx | Create a custom file name for the file to avoid name collisions. In this case, we use the Course Attempt ID to ensure a unique file name. |
| File Content | @{body(‘Populate_Template’)} | Dynamically select the Body from the previous template population step. |
Convert the file to PDF
Add a OneDrive for Business “Convert file” step:
| Property | Value | Description |
|---|---|---|
| File | @{outputs(‘Upload_Populated_Document_To_OneDrive’)?[‘body/Id’]} | Dynamically select the file created in the previous step. |
| Target type |
Upload the completed certificate PDF to the Course Registration record
Create a Dataverse “Upload a file or an image” step:
| Property | Value | Description |
|---|---|---|
| Table name | Course Attempts | |
| Row ID | @{triggerOutputs()?[‘body/ec_courseattemptid’]} | Dynamically select the row ID from the trigger step |
| Column name | Certificate | |
| Content | @{body(‘Convert_file’)} | Dynamically select the body of the previous file conversion step |
| Content name | Certificate.pdf |
Delete the temporary certificate template from OneDrive
Add a OneDrive for Business “Delete file” step:
| Property | Value | Description |
|---|---|---|
| File | @outputs{(‘Upload_Template_To_OneDrive’)?[‘body/Id’]} | Dynamically select the temporarily uploaded template file |
Delete the temporary completed certificate from OneDrive
Add a OneDrive for Business “Delete file” step:
| Property | Value | Description |
|---|---|---|
| File | @outputs{(‘Upload_Populated_Document_To_OneDrive ‘)?[‘body/Id’]} | Dynamically select the temporarily uploaded populated file |
Test the Flow
To test the flow, simply register for a course with an uploaded certificate, then take the course and complete it with a passing grade.
The flow will run, and after a few moments, the completed certificate will be added and attached to the Course Attempt record in the “Certificate” field. If you download the completed certificate, you will see the Full Name, Course Name, and Date fields filled with appropriate dynamic values.
Back to top