Signed Employee Certificate (Word to PDF) from MS Form Request

[Erthling]

Hi all,

I have created a Form that employees can fill out with their details that would request for a Salary, NOC or Employment certificate.

How can this request automate and provide the matching certificate form (see attached) pull the corelating Word template, place the name and required details, have the digital signature then convert it to PDF and email it to the employee?

Many thanks,

[Erthling]

If there is a better method, I am open to all suggestions of course.

[AlexEncodian]

@Erthling 

Hi, here's a blog article showing you how to Mail Merge Word Document with Microsoft Forms Response. You can quite easily build a flow to choose the correct template based on the a form response to the "Type of Certificate" question.

Do you need to bring in external data however? e.g. salary data? Then you need to see where this is stored and how you can bring it into your flow. The easiest would be if its stored in a SharePoint list and you can retrieve it in your flow to populate the document.

You can then convert the Word document to PDF and apply a digital certificate using Sign PDF to certify the document.

[Erthling]

Hi @AlexEncodian, and thanks for your support and input.

I was thinking if there's a simpler method (as per HR's demands to get all the data from their Excel) where I can have 2 fields only in the Salary Certificate Request MS Form with the user only putting his 'Full Name' and 'Email Address' (Email being the trigger link to getting the user's data from Excel.

Once the email address is captured, the details are triggered from HR's Excel sheet which has all the users' data that would be placed into the Template Word document that would have the Plain Text Content for the Bolded/Underlined in this format:

--------------------------------------------------------------

'Date of Request'​ (can be generated based on the time of Form request)            'Internal doc reference: XYZ'

To whom it may concern, 

Ref: Salary Certificate 

Dear Sir/Madam, 

We hereby confirm that ​'First Name'​ ​'Last Name'​ is an employee at XYZ company. 

He/She is presently working in the capacity of ​'Position'​ with a total remuneration of ​'Salary'​ per month, including fixed allowances. 

This letter is issued upon the employee’s request, and it does not constitute a financial guarantee on our part. 

With best regards, 

'Signature/Company Stamp pic'

(HR manager name)

Human Resources Department Manager 

--------------------------------------------------------------

It's just that I'm so new at this....

[Erthling]

@AlexEncodian

 The user's data (1st/2nd Name, salaries, email addresses and positions) are stored in an excel sheet.  Shouldn't that be enough as the data source to populate the word file template (non-premium) just by triggering the flow from a simple MS Form that the user puts in his email address only?

So, MS Form ---> Get Data from Excel (1st/2nd name, salary, position in company, joining date) + add date of file creation ---> Populate into a Word Template (non-premium way) ---> Convert to PDF (Digitally sign) --->Delete Word file ---> Email user with attachment.

How would the Workflow/Formulas/Variables be to get the matching data to the Word file with the plain content holders?

Much appreciated.

[AlexEncodian]

@Erthling 

yes - Excel as a data source would work as well. In a little more detail, the flow would look something like this:

1. When a form response is submitted
2. Get form response details
3. List rows present in a table (excel data)
4. You'll have to filter the Excel data via a unique identifier from your form response e.g. user email...to get the correct row
5. Populate Word document with the Excel data and form response
6. Convert to PDF
7. Sign PDF

Note, Microsoft's Populate Word connector is a premium connector. I'm not sure what you meant by non-premium i.e. whether you meant not 3rd party or not premium Power Automate licensing.

[Erthling]

@AlexEncodian 

Hi Alex, yes, we don't have MS 365 premium, and a workaround for that I found on youtube is to create a Sharepoint site with adding columns that match the required data, that eventually populates into the Word template (eventually to PDF).

Still trying to figure it out, but when I prompted Bing Chat to trigger getting the data from Excel, these are the steps it gave me to create:

Trying to use this method but to do the workaround using Sharepoint to Word extra step.

Can the Word doc be created instead using the Encodian option? (Like I said, I'm very Very new to all of this)

[AlexEncodian]

@Erthling 

The ChatGPT solution still uses Microsoft's Populate Word connector at the end. The SharePoint bit is just a data source. As I said, you can also use Excel (as per the steps I showed above).

As I said, Microsoft Populate Word is a premium connector. If you don't have access, you can use Encodian's connector which also has a Populate Word action (but it requires a separate subscription for over 50 actions per month).

This blog article shows you all the steps: Mail Merge Word Document with Microsoft Forms Response

[AlexEncodian]

Sure - that could work. Sometimes you just have to start building to see what nuances come up. Good luck

[Erthling]

Hi again @AlexEncodian,

Alright, so i did get the whole flow to work using Encodian, however, I just have 3 small issues that I need help with.

1) When I fill in the JSON info that gets extracted from the Excel (employees data sheet), 1 of the Dynamic contents is adding the submission time to be reflected in the final PDF certificate, and by using the submission time captured by the MS Form function is giving the full Date & Time including the Hour and Seconds.... 

2) When adding the employee dateofJoining in the company, the date format in excel is 'Date', yet in the word/pdf output result is showing a complete calculated number with comma (like an addition or division)

What other way can I add just the day/month/year and the employee joining date correctly?

The output in the PDF demo file is as follows:

Last but not least, the name of the generated PDF is showing correctly in the folder repository (Generated Certificates folder), however in the email, the attached file name is simply result.pdf. 
Why/How/What???

The workflow is as follows:

[AlexEncodian · Accepted solution]

@Erthling 

1) MS Form send the dates as a string and you need to format using either native expressions or Encodian's Utility - Format Date action (this is a Utility action i.e. only uses 0.05 credits).

If you use expressions, wrap your Submission date dynamic value in a formatdatetime('dynamic value from form','date formate') expression. For example, the below would return a form submission from today as 25-08-23 in the Word document.

Alternatively, you can convert dates using Encodian. The below example would output the submission date for a form submitted today as 25-Aug-23...then use the output from this action in your JSON

2) This is how Excel stores dates (an integer counting up from 1st Jan 1900 i.e. 2=02-Jan-1900 and 40,000 = 06-Jul-2009). To get date formats, switch to ISO 8601 format in your excel action. Note, you will still need to format this date like in step 1.

3) You are specifying the file name as a dynamic result from one of the Encodian actions. I can't tell which one. If you want a different file name, then you need to insert the correct one...its up to you (or change the output of that Encodian action).

[Erthling]

Thanks again, i'll try and do that now and report back to you before going off for the weekend!