Generating Dynamic Documents for Dynamics 365 using Power Automate

Setting the Stage

So, you may asking, “Why don’t you just use the Word Templates already in Dynamics 365?” That’s a great question. But, here is the scenario:

I needed a way to generate a dynamic Word document using a template, but I wanted the generated document converted to PDF, stored in SharePoint and accessible within Dynamics 365.

Note: The out-of-the-box Word Templates in Dynamics 365 don’t store the generated document anywhere. When you use the Word Template, it just pulls in the dynamic data from Dynamics 365, generates the Word document, and opens Microsoft Word.

Got all that? Ok. Good.

The Setup

Now that you understand what needs to be accomplished, let’s run through the applications we will use.

  • Microsoft Word: We will use this to create the Word template
  • SharePoint: We will use this to store the Word template and the final generated documents.
  • Power Automate (formerly Microsoft Flow): We will use this to wonderful, magical application to automate the document creation, conversion to PDF, and creation of Dynamics 365 records.
  • Common Data Service: We will use the CDS connector to assist with the Power Automate flow.
  • Dynamics 365: We will use this to start the Power Automate flow, and access the generated documents for the specific Account.

IMPORTANT! You will need to ensure you have set up the Dynamics 365 and SharePoint integration. If you haven’t done this, refer to this article: Set up model-driven apps in Dynamics 365 to use SharePoint Online

Now that you know the players, let’s run through each application to get things set up.

Creating the Template in Microsoft Word

For the sake of this blog, I am keeping the Word Template setup simple. However, the approach can be used for more complex templates.

I have a simple, generic service agreement document that I will use for the example. The next step is to insert text field placeholders that we will use later in Power Automate.

To insert the text field placeholders

  • Open the Word document.
  • Select the “Developer” tab.
    • If you don’t see the Developer tab:
      • Select File/Options
      • Select “Customize Ribbon”
      • On the right-hand side, put a check-mark next to “Developer”
      • Turn on the Developer Tab in Microsoft Word
  • Insert a “Plain Text Content Control” into the place in the Word document where you will want dynamic values to be populated.
    • Inserting Plain Text control into Word document
    • Notice in the above screen shot that I have inserted 3 Plain Text controls called “ContractDate”, “CustomerLegalName”, and “CustomerAddress”.

Continue inserting Plain Text controls into the document to correspond to any dynamic data you will insert later. For out example, we will just use the above 3 fields.

After you are done updating the document, be sure to save it.

Storing the Microsoft Word Template in SharePoint

Once the document is complete, it will need to be uploaded to SharePoint.

In SharePoint, I have created a library called “Contract Templates”. Inside this library I have added a folder called “Temp Files”. We will reference this later in Power Automate.

Next, I uploaded the service agreement Word document that we updated a few moments ago.

SharePoint - Contract Templates files and folders

The SharePoint Account Library

When you set up the Dynamics 365 and SharePoint integration, you may have noticed that a new Library was set up for each entity you specified in the configuration. For our example, we will just be using the Account library.

SharePoint - Account library for generated documents

Dynamics 365 – Part 1

Let’s jump over to Dynamics 365 to show what the end result will be. This will help you understand what we will be doing in Power Automate.

Dynamics 365 - Run flow

After the flow runs in Power Automate, you will see 2 new documents, a Word document and PDF document, in the “Files” tab on the Account.

The Magic – Power Automate

Now we come to the awesome part…Power Automate!

We will walk through the flow set up so that you can see how all the parts work together. First, review the below screen shot and then we will go through things step-by-step.

Power Automate - Steps 12 - 21
Power Automate - Steps 22 - 24
  1. Name of the flow. Always be sure to name the flow.
  2. When a record is selected. This trigger is created from within Power Automate, or starting in Dynamics 365.
    • Power Automate - When a record is selected
    • Environment: This should be “Default”
    • Entity Name: This should be “Accounts” as this is where we are running the flow from.
    • Input: I selected the “Add an input” and chose “Text”
      • After you add the input, you can add a drop-down list of options linked to the text input.
      • Power Automate - When a record is selected - Text input with drop-down list
  3. var_AccountNameClean. This is a string variable.
    • Power Automate - var_AccountNameClean
    • The value is an expression of: replace(replace(triggerBody()?[‘entity’]?[‘name’], ‘.’,’-‘), ‘/’, ‘-‘)
    • The expression replaces dots and slashes in the Account Name with a dash . The reason is that when the Account folder is created in SharePoint, it uses a combination of the Account Name and the Account ID (GUID). But, the Account Name can’t contain a dot or a slash.
  4. var_AccountIdClean. This is a string variable.
    • Power Automate - var_AccountIdClean
    • The value is an expression of: toUpper(replace(triggerBody()?[‘entity’]?[‘accountid’],’-‘,”))
    • The expression replaces the dashes in the Account ID (GUID) with nothing (removes the dashes). Next, puts all the letters in uppercase. The reason is that the Document Location record in Dynamics 365 won’t work properly if the ID contains lowercase letters.
  5. var_AccountWithId. This is a string variable.
    • Power Automate - var_AccountWithId
    • The value is a stringing together the var_AccountNameClean variable, followed by an underscore, then the var_AccountIdClean variable.
  6. Convert Time Zone. This action will convert the UTC time to Eastern Time.
    • Power Automate - Convert Time Zone
    • If you don’t convert the time zone, the date/time stamp on the document name (later step) will have UTC time instead of the your time zone.
  7. FormattedDate. This is a Compose variable with a single input.
    • The input is an expression of: formatDateTime(body(‘Convert_time_zone’), ‘yyyyMMddTHHmmss’)
    • This expression uses the output from the “Convert Time Zone” action.
    • This compose variable will be used later for the document date/time stamp.
  8. CurrentDateDayNbr. This is a Compose variable with a single input.
    • The input is an expression of: formatDateTime(utcNow(), ‘dd’)
    • This expression will use the current date/time and output just the Day Number.
  9. CurrentDateMonthName. This is a Compose variable with a single input.
    • The input is an expression of: formatDateTime(utcNow(), ‘MMMM’)
    • This expression will use the current date/time and output just the Month Name.
  10. CurrentDateYearNbr. This is a Compose variable with a single input.
    • The input is an expression of: formatDateTime(utcNow(), ‘yyyy’)
    • This expression will use the current date/time and output just the Year Number.
  11. var_OrdinalNumber. This is a string variable without a value. This will be set later.
    • An Ordinal Number contains “st”, “nd”, “rd”, “th” after the integer.
    • The next few steps will create and set this variable.
    • An example of using this is: The 3rd day of November, 2019.
  12. STarray. This is a Compose variable with a static array input.
    • The “STarray” is named because the numbers in the input will end with “st” for their ordinal number.
    • The numbers in this input are 1,21,31.
  13. NDarray. This is a Compose variable with a static array input.
    • The “NDarray” is named because the numbers in the input will end with “nd” for their ordinal number.
    • The numbers in this input are 2,22.
  14. RDarray. This is a Compose variable with a static array input.
    • The “RDarray” is named because the numbers in the input will end with “rd” for their ordinal number.
    • The numbers in this input are 3,23.
  15. THarray. This is a Compose variable with a static array input.
    • The “THarray” is named because the numbers in the input will end with “rd” for their ordinal number.
    • The numbers in this input are 4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,24,25,26,27,28,29,30.
  16. Check array conditions. This is a Condition action with “If yes” and “If no” branches.
    • Power Automate - Check array conditions
    • The condition here will first check if the “STarray” contains the output of the “CurrentDateDayNbr”. For example, if the current date day number is 1, then the out put of this condition will set the ordinal number variable to be “1st”.
    • If the “STarray” condition is not met, it will go down the “If no” path. This will keep checking the “CurrentDateDayNbr” against each of the arrays.
    • If the array condition is met, then the “If yes” path will set the “var_OrdinalNumber” variable by stringing together the “CurrentDateDayNbr” with the appropriate suffix of “st”, “nd”, “rd”, or “th”.
  17. ContractDatePhrase. This is a Compose variable with a single input.
    • The input is a concatenation of 3 variables to create a date phrase. For example: 24th day of November, 2019
    • The 3 variables are: OrdinalNumber, CurrentDateMonthName, CurrentDateYearNbr.
    • So, it will look like this: OrdinalNumber day of Outputs(‘CurrentDateMonthName’), Outputs(‘CurrentDateYearNbr’)
  18. Switch depending on Contract Type. This is a Switch condition.
    • The “on” value in the “Switch” is based on the very first “When a record is selected” trigger. If you recall, we set up a drop-down list for the text input.
    • The value of the text input called “Choose Contract Type” will allow us to switch through the Cases for the switch.
    • In this example, I have only 1 template set up, but if you have 3 or 4, you can set up multiple cases under the switch to toggle the different templates.
    • Here are the steps for the switch. Notice the “Default” path contains the “Terminate” action.
    • Power Automate - Step 18 - Sub-steps 18a-18f
      • 18a – Equals. This is a text value that should match one of the options in the text input in the first step. So, if the user chooses “General Support Agreement”, then this Switch case will activate.
      • 18b – Get file content. This step uses the SharePoint connector and will get the Microsoft Word template file we uploaded earlier. Here are the values to input.
        • Power Automate - Step 18 - Sub-step 18b - Get File Content
      • 18c – Populate a Microsoft Word template. This will populate the dynamic values (Plain Text content) in the Word Template we uploaded to SharePoint.
        • Power Automate - Step 18 - Sub-step 18c - Populate a Word Template
        • The “Location” is the SharePoint site.
        • The “Document Library” is the library we created earlier called “Contract Templates”.
        • The “File” is the Microsoft Word template within the library.
        • The “ContractDate” is the plain text control within the Word document. The value will be populated with the Output of the “ContractDatePhrase” variable.
        • The “CustomerLegalName” is the plain text control within the Word document. The value will be populated with the “Account Name” from the “When a record is selected” trigger at the very beginning of the flow.
        • The “CustomerAddress” is the plain text control within the Word document. The value will be populated with the “Address 1: Street 1”, “Address 1: City”, “Address 1: State/Province”, and “Address 1: Zip/Postal Code” from the “When a record is selected” trigger at the very beginning of the flow.
      • 18d – Create Word file. Once the template is populated, you will need to create the Word file within SharePoint. This file will be stored temporarily in the “Temp Files” folder in the “Contract Template” library.
        • Power Automate - Step 18 - Sub-step 18d - Create Word file
        • The “Site Address” is the SharePoint site.
        • The “Folder Path” is the folder where this file will be created.
        • The “File Name” will be concatenated with a prefix of “GSA” (General Service Agreement), the “Account Name”, and the output of the “FormattedDate” variable.
        • IMPORTANT! Be sure to include the file type at the end of the name. In this case the file name will end with “.docx”.
      • 18e – Convert Word Document to PDF. This will convert the newly created Word file into a PDF.
        • Power Automate - Step 18 - Sub-steps 18e - Convert Word Document to PDF
        • The “Location” is the SharePoint site.
        • The “Document Library” is the “Contract Templates” library.
        • The “File” is the the newly created Word file. The “Name” SharePoint output displayed is the output from the “Create Word file” step.
      • 18f – Create PDF file. This step will create the PDF file after the Word document has been converted from the previous step. This step is exactly like the “Create Word file” step except for the file type at the end.
        • Power Automate - Step 18 - Sub-steps 18f - Create PDF file
  19. List folder. This is a SharePoint action that will list the folders within a folder.
    • Power Automate - Step 19 - List Folder
    • The “Site Address” is the SharePoint site to look in.
    • The “File Identifier” is the folder to look inside of.
  20. Filter array. This is a filter array action to allow us to put in filtration on arrays.
    • Power Automate - Step 20 - Filter array
    • The reason for this step is to help us find out if the Account folder already exists or not.
    • The “From” value is the “Body” output from the prior “List folder” step.
    • The filter here is the “DisplayName” of the folder “is equal to” the “AccountWithId” variable.
  21. Check if SharePoint Account folder exists. This is a Condition action.
    • Power Automate - Step 21 - Check if SharePoint Account folder exists
    • The condition to check here is to see if the output from the prior “Filter array” step is empty or not.
    • The input in the left is an expression of: empty(body(‘Filter_array’))
    • The “If yes” path has 2 steps:
      • “Move Word file from Temp Folder to Account Folder”. This is a SharePoint action of “Move file”.
        • This step is finding the file from the “Create Word file” step from earlier using the “Id” output.
        • Then it will put the file in the “<AccountName>_<AccountId>” folder since it already exists.
      • “Move PDF file from Temp Folder to Account Folder”. This is a SharePoint action of “Move file”.
        • This step is finding the file from the “Create PDF file” step from earlier using the “Id” output.
        • Then it will put the file in the “<AccountName>_<AccountId>” folder since it already exists.
    • The “If no” path has 3 steps:
      • “Create Account Folder”. This will create a new Account folder for the Word and PDF files to go into. This is created because the folder doesn’t exist.
      • “Move Word file from Temp Folder to Account Folder”. This is a SharePoint action of “Move file”.
        • This step is finding the file from the “Create Word file” step from earlier using the “Id” output.
        • Then it will put the file in the “<AccountName>_<AccountId>” folder since it already exists.
      • “Move PDF file from Temp Folder to Account Folder”. This is a SharePoint action of “Move file”.
        • This step is finding the file from the “Create PDF file” step from earlier using the “Id” output.
        • Then it will put the file in the “<AccountName>_<AccountId>” folder since it already exists.
  22. List Document Location records. This is a Common Data Service action of “List records”. This step will list the “Document Location” records. The “Document Location” records are what links the SharePoint folder to the Dynamics 365 entity.
  23. Filter Document Location records. This is a “Filter array” action that is filtering the “Document Location” records from the prior step to help us check if a record already exists or not.
    • Power Automate - Step 23 - Filter Document Location records
    • The “From” is the output from the prior step of “List Document Location records”
    • The condition value on the left is the “Relative URL” field from the “Document Location” record.
    • Then we’re checking if the “Relative URL” is equal to the “AccountWithId” variable.
  24. Check if Document Location record exists. This is a Condition action.
    • Power Automate - Step 24 - Check if Document Location record exists
    • This will take the output from the “Filter Document Location records” and check to see if it’s empty or not.
    • The expression used here is: empty(body(‘Filter_Document_Location_records’))
    • If it’s not empty, then this means the Document Location record already exists. So, in the “If yes” path, I have put in a “Terminate” step.
    • If it’s empty, then this means the Document Location record doesn’t exist. So, in the “If no” path, we will create a Document Location records under the Parent Document Location record.
      • This is found by filtering on the “relativeurl” schema field using the OData filter of: relativeurl eq ‘account’
    • The very last step is to create the new Document Location record. Here are the fields that need to be updated.
    • Power Automate - Step 24 - Sub-step 24a
    • The “Name” will match the default naming that Dynamics 365 assigns to the records. However, you can name these whatever you would like.
    • The “Relative URL” is populated from the “AccountWithId” variable.
    • The “Parent Site or Location” is the output of the prior step.
      • Even though the list was filtered, it will auto-produce an “Apply to each” step.
      • Inside the “Apply to each” it will take the output from the prior step. In this case, it is the “ID” of the Parent Document Location record.
    • The “Regarding” field will be the “ID” of the Account record we started with in the trigger for the “When a record is selected”

Back in SharePoint

Let’s go check SharePoint to see if the Account folder was created.

SharePoint - Account folder created

As you can see the “A Datum Fabrication” folder is new as it has the small little indicator in the left corner.

Also notice the ID at the end is all uppercase and is stripped of the dashes.

Now, let’s go check out Dynamics 365

Dynamics 365 – Part 2

If you navigate to Settings/Document Management/SharePoint Document Locations, you will see the list of Document Location records.

Dynamics 365 - Document Location records

Above you will notice the record with the “Regarding” of “A Datum Fabrication”. This is the newly created record. Also, notice the “Relative URL” value is matching what’s in SharePoint.

Next, notice the first record is the Parent record with a “Relative URL” of “account” this matches the “Account” folder within SharePoint.

Now, let’s navigate to the Account record to see if the documents we generated are listed.

Dynamics 365 - Documents listed in Files tab

As you can see above, the Word document and the PDF document are displayed. Notice that the Path is new folder in SharePoint that we created in Power Automate.

In Conclusion

As you can see, Power Automate is powerful and can connect to many different services to provide the automation you need.

To build upon the above information, you could branch even further and create steps to attach and email the PDF or Word documents to the customer. Or, maybe email them to the Account owner for review prior to sending.

Using this method provides more flexibility in how the documents are stored and generated. And, since the Template is in SharePoint, it makes revising it easier as Dynamics 365 doesn’t provide a native method to export existing templates.

Until next time…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.