Model Driven App: How to dynamically create a HTML table from the sub grid in the Email Template
Introduction
The release of Power Apps and Power Automate has changed the way most of the apps are developed now days. Most of the company preferred the approach of low code, as the technology evolves, Company tend to develop cost effective apps in much less time with less code, this eliminates the requirement to hire a developer and which eventually incur costs and also the customer is eager for innovation and build apps with latest technology at the very low possible cost but by still keeping the level of quality at the very high standard which is very common now days in many industries. To be able to deliver project that meet these requirements above is quite challenging especially when the customer has a very tight budget but still require us to deliver the work at a high standard.
Problem
Microsoft has the concept of apps, sometimes some features that we require in the project might not be part of the Power Apps license we have purchased, in my recent project, one of the requirements was to send email containing a related child table to customer using email template. The customer paid for power apps per app license and was not keen to spend for additional third party solution or for more license. The plan was to create a model driven app where email with HTML table and data is sent to customer and the data is fetched from a child table of the record where the email is sent, however, model driven app doesn’t support this functionality unless it’s integrated with some other third-party solution but the problem with this later solution is the additional costs implication.
As the customer is reluctant to spend more because of tight budget and the way around saving costs is the implementation of a custom solution. In this project the customer has purchased a power app per app license as such the design of the solution was mainly around model driven app, in this situation, the obvious and effective way to address this issue was to implement a custom solution and integrated it with the model driven app, thus the email body containing information about the sub-grid is emailed to the customer, as we all know email template in power apps doesn’t support sub-grid table natively as it does with word template so I have to implement a custom solution to get the HTML table embedded in the email template body.
Rich text Editor Field in Model Driven App
Create a field in model driven app that is used to store the html table, recently, Microsoft enhanced the model driven app by introducing the field rich text editor which is able to render HTML, so I used this great feature as part of my implementation. I preferred to use multiline instead of the text area type because the text area maximum length is 4000 and might not work for vey large HTML table, however multiline allow you to store more than 4000 in size and should work well for very large HTML table.
To use rich text editor field, I needed to enable it from the field property and added the Rich text editor control as shown on both screenshots below.
The next step was to check for Web, Phone and Tablet, I just needed to select the device where I would like it to be shown.
Below screenshot show how the rich text editor component displays on the model driven app form, as you can see, it provides all the tools you need to apply font, formatting and create table, etc…
Embed the rich text editor field in the email Template
The next stage was to embed the field on the email template using this syntax {!new_claim: new_claims;} so that when I populated the field with HTML table then it displayed in the email template, as shown on screenshot below, I have highlighted the field that holds the HTML table for the sub grid that needed to be displayed in the email template.
Power Automate
The sub grid in the model driven app that needed to be embedded on the email body and emailed to the customer looks like the one highlighted on the screenshot below, the requirement was just to have four columns in the table which represented four fields in model driven app.
Power automate triggers when a field in Dataverse table get updated and constructs the HTML table from the sub-grid and the HTML table is stored in the rich text editor field added in the email template and rendered when the email is opened.
As shown on the screenshot below, power automate get triggered when the total claim field is updated, this field get updated every time the sub grid record on the model driven app main form changes and subsequently triggers this flow.
As part of the implementation, I needed to add this step with screenshot below in the flow to retrieve all the records that is populated in the HTML table.
I have added the create HML table action below in the flow, this is the step that takes the list of the records from the previous action (screenshot above) and populates them in the HTML table created on the fly each time the flow runs. Each record in the list records returned will be represented as row in the HTML table.
Styling the table
I have also added some actions to style the table, as shown in the screenshot below, these are literally html style and inline CSS tags, in my case, I have applied some border styling on the table, I could have also applied some colour by adding this tag : style: “color:blue”.
Update the rich text editor field
The final step of the flow is to update the record field with the HTML table, so the output of the previous action above is populated in the “claim items” field as shown in the screenshot below.
Model Driven App Email
After the power automate run, the table is dynamically created and populated with the records retrieved from the Dataverse table and the constructed table with data is stored in the rich text editor field. In the model driven app, when sending email to an external user using the email template, the HTML table is rendered on the email body as shown in the below screenshot.
Conclusion
Building high productivity business apps come with challenges especially when we have complex requirements that are not supported by power apps natively or not part of the out-of-the box functionalities. In addition, when the customer want to keep costs to the minimum and not willing to invest more on third party solution, in such circumstances, the only option available at hand is to design and implement a cost-effective custom solution while still keeping high standard of quality work. The extensibility of Power platform provides comprehensive components to develop low code custom solution.
