Microsoft Dynamics 365 – Microsoft Dataverse (Common Data Service) has been gaining a lot of popularity lately because it helps normal users, who are not developers, to capture, analyze, predict, present, and report data with a high level of agility. A few months ago Microsoft further extended CDS by starting to support complex data-types like files, which makes CDS even more extensible for development.
In fact, during a recent discussion with a Microsoft Dynamics 365 client, I was asked if we could convert a Microsoft CDS column including the ‘File’ data type to PDF.
In this post, we’ll use Muhimbi’s ‘convert’ action for Power Automate, to convert CDS values to PDF and then Merge those with the file to the converted .
Before we begin, please make sure the following prerequisites are in place:
- An Office 365 subscription with access to Power Automate (Flow).
- Muhimbi PDF Converter Services Online Free, full or trial subscription (Start trial).
- Appropriate privileges to create Power Apps and Power Automate(Flow).
- Working knowledge of both Power Apps, Microsoft Data Traverse (CDS) and Power Automate(Flow).
If you are new to Microsoft Dynamics 365 – Dataverse(CDS), you can add the “File” datatype field to your table with reference to instructions below:
- In the Power Apps portal, click to the expand the Data tab, then click on Tables, and select the Table to which you want to add the File-type column. For the purposes of his post, I am using the “Account” table.
- In the Account table, shown in the image below, click on + Add Column and the column properties pane will open. In the Column properties pane, select the File Data type drop down menu.
Note: The default Maximum file size is 32 MB and the largest size you can set is 128 MB. Once the maximum file size has been saved, it can’t be changed.
Now that we have added the File column to our table, you need to add the column to your APP or Form and the Field to your Form.
To add a Field to the Form, click on the Properties tab of the right-hand pane and select Edit fields.
In the Fields pane, select Add File field, select the check box for each field, and then select Add.
Now that our Form is ready, lets start building our Power Automate(Flow). From a High level our solution will look like the screenshot below:
Step 1:
- Open Power Automate designer, create a Flow and find the “When a record is created, updates or deleted” CDS trigger. This trigger will start the Flow when a record is creates or when an existing record is updated.
- In the action, select the table that you want to monitor for new items and specify the Scope.
Step 2:
- Add the CDS “Get file or image content” action to the Flow Canvas and configure it with reference to the details below:
- Entity name(Table Name): Accounts.
- Item ID(Unique value): Account
- Field name(Select the File field from the dropdown): accFile
Step 3:
- Add the CDS “List records” action to the Flow Canvas and configure it with reference to the details below:
- Entity name(Table Name): Accounts.
- Filter Query: accountid eg ‘Account’
The ‘Filter Query‘ will help us only get the ‘Current item‘ rather than all the values from the CDS table.
Note: accountid is the internal name of the column ‘Account’ and it is the unique value in my Microsoft Dataverse (Common Data Service) .
Step 4:
- Add the “Initialize variable” action to the designer canvas and specify the “Name“(fileName) and “Type” (String) and “Value“(empty).
Step 5:
- To configure the “Parse” action, add the “Compose” action temporarily and pass it the “body” the output of the “List records” action and “Run” your Power Automate.
- After the “Run” copy the “JSON” from the input or output to your clipboard.
Step 6:
- Add the “Parse JSON” action to the designer canvas.
- Content: Pass it the “body” the output of the “List records“.
- Schema: Click on the “Generate from sample” and paste the content you copied to the clipboard.
Step 7:
- Add the “Set variable” action to the designer canvas.
- Name: Select the “fileName” from the dropdown.
- Value: Set the filename value.
Note: The apply to each loop will be added automatically when the you add the filename to the value.
Step 8:
- Add the Muhimbi “Convert HTML to PDF” action to the designer canvas.
- Copy the HTML fragment provided below into the “Source URL or HTML” area of the Muhimbi Action and then insert the various list fields in the appropriate places, just before each line’s <br/> element.
<html>
<body style="font-family: Arial">
<h1>PowerApp PDF Demo</h1>
<p>
<b>Name</b>:@{triggerOutputs()?['body/name']} <br/>
<b>Main Phone</b>: @{triggerOutputs()?['body/telephone1']}<br/>
b>Address 1: City</b>: @{triggerOutputs()?['body/address1_city']}<br/>
</p>
<p>Generated using <a href="http://www.muhimbi-online.com">www.muhimbi-online.com</a></p>
</body>
</html>
Step 9:
- Add the “Merge document” action, and configure it with reference to details below:
- Source file name – 1 : Hardcode HTML.pdf
- Source file content -1 : Add the “Processed file content” the action the output of the “Convert HTML to PDF” action.
- Source file name – 2: Add the “fileName” the output of the “Initialize variable” action.
- Source file content -1 : Add the “File or Image Content” the action the output of the CDS “Get file or image content” action.
Step 10:
- Add the “Create file” SharePoint action to create the PDF document in the SharePoint document library.
- Folder Path: Specify the output path to write the PDF file to.
- File Name: For this Demo, we will create a dynamic file name using the formula concat(triggerOutputs()?[‘body/name’],’.pdf’)
- File Content: “Processed file content” is the output variable of the “Merge documents” action.
All Done! Launch your PowerApps, fill in the details in the, click on the Submit button and after a few seconds, you should have a PDF in your SharePoint Folder.
Subscribe to this blog for the latest updates about SharePoint Online, Microsoft Flow, Power Apps and document conversion and manipulation.
Clavin's Blog 