I faced a challenge recently: while working on my own invoicing software (basically a Model Driven app that generates a PDF based on a Microsoft Word template), I bumped into a problem I had not foreseen: I'm going to need different invoices and each one will have a different structure and fields.
The easy fix is to have an if/switch statement that will create the different templates based on a choice field (e.g., if the invoice is for Spain, use the Spanish version with tax withholding; if the invoice is going outside the EU, use the English version without VAT; etc.).
Well, I thought about that, and I didn't like the solution.
So, I designed an alternative solution that doesn't need me changing the flow. It's all configuration.
Disclaimer here: configuration doesn't mean a nice interface (although it could be, of course). In this case, it's just JSON object handling.
Step 0: build your Word template
I won't go through this step as there are plenty of articles from the community that explain this in great detail. Here are a couple that I came across with a first query to Bing (yes, Bing!):
Step 1: Extract the Word template schema
When you set up the different text fields in your Word template, you are generating a schema inside the Word file behind the scenes. This schema is the definition of the fields you will fill out inside the Power Automate action.
So, how can you get that schema? I've seen in a blog that you can do save the Word as a .zip and then navigate its internals, but it felt too cumbersome for me. So, being that this operation won't be very frequent, the easiest way (and low-code way) is to extract the schema from Power Automate itself.
To do that, first select your Word template and fill the parameters. Here, instead of the actual values you would add, it's best if you put some text that will help you understand what each parameter stands for:
Next, go to the Code View on the flow Action and copy the schema, which is comprised of all those key-value pairs that start with "dynamicFileSchema":
The goal now is to come up with a strategy to build those "dynamicFileSchema" fields dynamically (i.e., independent from the actual Word). If it helps, visualize the end goal as enabling a dropdown to select the Word template we want to use each time and, because templates will change (both in their content and which ones we can select), nothing can be hardcoded.
The strategy I came up with is to have an intermediate configuration that will act as a "translator".
Step 2: Build your own configuration object
We're going to abstract ourselves from the content of each Word template. For that, you'll have to think of a "configuration object" that the flow will need to read without worrying about the contents of the dynamic schema.
In other words, when you build your flow, you want the "create Word template" action to be completely independent from what Word is selected and what fields it has at execution time:
Now we have to build our configuration object, which is nothing more than a convention. And it's pure creativity, so the solution I'm going to show you may not be the best and you can actually come up with a much better one.
The most important thing is that we cover the following criteria:
The config. object must be able to deal with Arrays.
The config. object must be able to deal with formatting (e.g., format the dates in a particular way).
The config. object must be able to hold enough information so that Power Automate can retrieve the proper value from Dataverse.
Now, here's an extract of the kind of configuration object I have created for my different Word templates:
[
{
"type": "array",
"schemaId": "308610390",
"itemsSchema": [
{
"schemaId": "detailDescription",
"retrievalValue": "abg_description",
"specialTreatment": "none"
},
{
"schemaId": "detailCharges",
"retrievalValue": "abg_charges@OData.Community.Display.V1.FormattedValue",
"specialTreatment": "none"
},
{
"schemaId": "detailDiscount",
"retrievalValue": "abg_discountpercentage@OData.Community.Display.V1.FormattedValue",
"specialTreatment": "none"
},
{
"schemaId": "detailsVAT",
"retrievalValue": "abg_VAT/abg_vatpercentage",
"specialTreatment": "none"
},
{
"schemaId": "detailsSubtotal",
"retrievalValue": "abg_auxtotal@OData.Community.Display.V1.FormattedValue",
"specialTreatment": "none"
}
]
},
{
"type": "string",
"schemaId": "857015247",
"retrievalValue": "abg_BilledTo/name",
"specialTreatment": "none"
},
{
"type": "string",
"schemaId": "1135211173",
"retrievalValue": "abg_auxtotalvat@OData.Community.Display.V1.FormattedValue",
"specialTreatment": "none"
}
]Each object has the following properties:
type: string or array.
schemaId: the Word template's dynamicFileSchema ID.
retrievalValue: the "path" to the value we want to retrieve from a Dataverse object. If you see forward slashes means the "path" is navigating a 1:N relationship (nested objects).
specialTreatment: to deal with special formats (e.g., dates).
For the type: array, the only thing that changes is that we need to navigate another level to retrieve the values. We just need to define the array items in a consistent way:
schemaID.
retrievalValue.
specialTreatment.
Pick this way of defining the configuration object if it suits you.
Step 3: store the configurations in Dataverse
Once you've prepared all the JSON configuration for each of your templates, you'll need to store it in a "config table" in Dataverse so that it's available for your flow when it runs:
Note: you'll need to store the Drive id for both the library where your file is and the Word file. The reason is that the Word Business connector uses the Graph's OneDrive API to retrieve the file, instead of SharePoint. You can retrieve those values from the Power Automate action:
Step 4: build your Power Automate flow
So, now that we have our:
Configuration table.
Configuration records ready and stored in the configuration table.
A strong desire for complicating one's life.
... it's time to start building the "engine" that will generate the dynamicFileSchema based on the configuration records we've defined:
The tactic is going to consist of looping all the items inside the configuration object ("ForEachJsonArrayItem") and use the addProperty function to add the key-value pairs that the Word dynamicFileSchema expects.
We're going to use 2 variables to perform the operations:
"DynamicSchemaObject": will hold the dynamicFileSchema to pass on to the Populate Word action.
"AuxDynamicSchemaObject": will assist us in the creation of the DynamicSchemaObject using the addProperty function.
Let's go step by step without worrying about arrays yet. When dealing with the array, you just need to nest another loop, but the actions are the same. I hope I explain myself here:
Final comments
Creating a truly dynamic Word template generation Power Automate flow is not easy but can be done with an additional abstraction layer: a configuration object. And this is basically what we must do in many complex scenarios: create a data structure that's going to make our life easier.
I hope this article helps you approach this problem. I know it's not an easy topic and believe me it's not easy to explain either. I leave with the feeling of not having made myself clear.
If that's what you're feeling too, please let me know in the comments. Should I try to create a YouTube companion video and explain this in a friendlier format?
Let me know.
Bye for now! Take care and keep pushing forward 💪
Andres B.