Relationships Matter

Generating documents is a fairly common use case among organisations. A business entity that sells goods and services will always invoice its clients. A quote may need to be sent to a potential customer looking for a vendor that will provide them with what they need, the decision mainly driven by cost-effectiveness. These are just two of the common documents that are produced and sent out to customers. This post is about how a quite simple Power Automate flow can be used in conjunction with a document generation tool to provide a slick solution to a scenario that is, I would argue, almost a standard requirement in its prevalence.

Suppose No Blues is a company that sells furniture. The requirement is that whenever a furniture is to be delivered to their customer, a delivery document is sent to inform them that a delivery has been scheduled and will reach them soon.

The picture below illustrates the relationships of entities supporting this process. At the heart of these connections is the Delivery entity, which stores, among other important data to get this delivery through, when the delivery will happen and who the client is. What we want to achieve is that when the status of the delivery is scheduled, an email with a document I will call delivery document (not creative enough with the name, i know) goes out.

The first question might be: Can’t we use the word template in Dynamics 365 to print the document? Surely we can, but only up to the extent that the entities we will be pulling data from are limited to the ones that are one level above and one level below our triggering entity. In the diagram above, if Delivery is the triggering entity, then we can only get data from the Client and the Product Delivery entities. If I want to get further down to the Product Delivery Product Sub-component, that is the end of the journey of the out-of-the-box document generation of Dynamics 365. The image below is inserted to stress that restriction.

I’ve seen blogs where Power Automate does all the heavy lifting. It takes care of retrieving all the relevant data and putting it in the right spots within the document. That’s well and good. What I’ve done here is actually to leverage XpertDoc in taking care of the document template and generation aspects (which is what the product is all about) while Power Automate is responsible for sending out the emails, checking conditions, looping through records, etc.

In XpertDoc, generating a document takes essentially three steps: 1) Creating the data set; 2) Designing the template; and 3) Setting up a Flow (which is just like Power Automate itself). It begs a second question: Can’t we just let XpertDoc not only generate the document but send the email, since this is a product we’re paying for anyway? That is a fair question to ask and certainly this can be done; but the main reason for me really of inserting Power Automate into the picture is to provide the control to enable generation of individual documents for each child record of the triggering entity and all those generated documents will be attached into a single email. (Unfortunately, my trial account allowance was depleted even before I could prove the point, so I’m left with just demonstrating the standard document generation where one document is generated and child records are pulled and displayed as a table into the document.)

The data set created is as below. We can see that we can criss-cross the entire data model to pick up the data that we want to surface into the document.

This is how the document template looks like. The key question that stumped me is this: Can I create a loop inside a loop? If yes, how can I do that? Say we have components table and chair. Each of two have their sub-components. What I want is to be able to include in the list the table and chair and also their sub-components. This apparently can be done by a) creating a loop of the components, b) creating a loop of the sub-components, and c) grouping the two loops according to a field you’d like them to be grouped.

The last step as far as XpertDoc goes is creating the flow that generates the document. This is a rather straightforward, no-frills flow. Only the first three layers are in use — Start flow, Collect data, and Generate documents.

Now that we have the XpertDoc pieces already in place, it’s time to set up the Power Automate flow that calls XpertDoc to generate the document.

The Power Automate flow runs when the statuscode of the Delivery entity record changes. It will proceed to the next set of steps only if the statuscode value is Scheduled.

We obtain the client record to get the email address of the client. The succeeding two actions are provided by the XpertDoc connector, the purposes of which are to trigger running the XpertDoc flow we’ve setup above and to retrieve the document, respectively. The entity named “B” is the delivery entity, which is our triggering entity.

The last step is now to send the email and attach the document generated into it.

There you have it! Once all done with the configuration above, we get this nice-looking document attached into the email sent to the recipient. 🙂

In sum, Power Automate (and the Power Platform, more broadly) has made putting together this solution so much easier, which also means we can get this up and running so much faster than obviously the classical approach of writing a code.

Till 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