Insert Child Records into an Email via Power Automate and Custom Activity Workflow

In my previous post, I wrote about updating the status of the child and the children of these child records. Now that we already are able to get hold of the child records, what I want to do this time is to select details from these records and add them into the body of an email. As one would expect, we don’t want to just throw a bunch of data into an email without formatting it into a nice table, do we? Similar to that post, what I’ll do is to build a custom workflow activity and then to take the low-code to no-code approach of setting up a Power Automate flow.

0 Main Diagram

Before going any further, the question that needs to be answered is this: what real scenarios call for this solution? One easy answer would be when someone’s working on an opportunity and then details of which including its related products be sent out into an email. This, perhaps, to complement generating some document as an attachment to the email.

Power Automate

If you are someone who somehow finds it fun mapping out the path of a business process on a flow chart,  then you should get the same with Power Automate. Here is how it goes.

  1. Let the flow listen to changes to an Entity A record’s status. This is the triggering action.1 Power Automate Trigger
  2. Entity B records will now be retrieved which is carried out by the second action on the below image. But immediately before that is the action to get the Entity A record itself that kicked off the flow. We will need that to filter out records we are not interested in.  To apply the filter, in the Filter Query field, enter _lookup field’s logical name_value eq guid of the Entity A record which just means that we want to get Entity B records where the parent is the Entity A record that triggered the flow.2 Power Automate Retrieve Child Records
  3. Initialize an array variable whose values will be the data of Entity B we would like to expose into a table. This will be the same table inserted into the body of the email.
    Why the need for an array? We’re to use that as an input to the Create html table action, which is further down the flow (see step #4). To add values to the array, the Apply to each action will loop through the returned list of entity B records from step #2. At each iteration, the values from the Name, Parent, and Description columns will be taken and appended to the array. Notice that before the action of adding values to the array is a step to get the Entity A record. This is so we can take the name field from the Entity A because otherwise, the parent lookup field in Entity B will just display a global unique identifier, a string of number and letters, which is rather meaningless to the reader of the email.3 Power Automate Loop Through the Child Records to Build the Array
  4. We are now in a position to create the html table. The Create HTML table action will do that with the input being the array variable created in step #3. The action below it is used to store the table styling attributes, which is just how we want the table to look like. The third action is to combine the table style string with the output from the Create HTML table action. The output of this is actually what is to be inserted into the email body.
    4 Power Automate Create HTML Table
  5. Finally, we compose the email. As what I just wanted to prove is if the email will get created with an HTML table in it, I did not bother looking into what all the other fields here mean, apart from identifying the field that represents the email’s body.
    (Also, for whatever reason, I cannot seem to trial an Office 365 into my tenant. That’s why I picked the action of creating a record rather than the action to send the email.  Since proving that that the email is there with a table in it is all that I’m after, this is sufficient.)
    5 Power Automate Create Email

So with all the actions pieced together, the flow runs fine. A sample output is below. 🙂

6 Power Automate Email Output

Custom Activity

Now we move on to the part where we write a code to extend the available actions that can be called when setting up a classic workflow.

  1. These lines define both the input and output parameters. The input parameter will be the record whose child records will be retrieved. The output parameter, on the other hand, is the variable that will store the HTML table string.2 Custom Activity Define Paremeters
  2. This is the block of code to use the Organization Service methods. The last line is to get hold of the actual inputted entity record in Step #1.
    1 Custom Activity Connect to Org Service Methods
  3. This code block queries the actual Entity B records whose parent is the inputted entity record in step #1. The query syntax is in FetchXML, which again, is readily available by doing an advanced find. This makes the life of a developer a tad (or a whole lot?) easier. 🙂
    3 Custom Activity Query Child Records
    7 Custom Activity Advanced Find
  4. I wanted to up the complexity a bit by retrieving the the three attributes we are interested in, namely —  the Name, Description, and Parent. What do we need this for? I wanted to get to the metadata of these attributes, particularly the Display Name, so that whenever this changes, we still have it right. This rather than defining a static value.4 Custom Activity Get Attribute Metadata
  5. This builds the HTML table string. The first block is for the table, table header, and table data styles. The second block is to add the table headers. It is on this part where the Display Name comes in handy. These lines pull that attribute metadata.5 Custom Activity Table Style and Table Headers
  6. The Entity B records retrieved in step #3 will be looped through to build the  html table’s data. 6 Custom Activity Table Data
  7. Lastly, we are now ready to take the built HTML table string and set this to be value of the output parameter. This output parameter is what will be inserted into the email.6 Custom Activity Set Value of Output Parameter

With the custom workflow activity registered via the Plug-in Registration Tool, we can now set-up the classic workflow. This is how it looks like.

8 Custom Activity Workflow Step 19 Custom Activity Workflow Step 2

A sample of an email created by the workflow is in the image below.

10 Custom Activity Workflow Email Output

That’s it! Til next time. 🙂

References:

https://www.c-sharpcorner.com/article/how-to-create-and-format-html-table-using-microsoft-flow-and-sharepoint-online/

https://docs.microsoft.com/en-us/power-automate/data-operations

https://www.w3schools.com/html/html_tables.asp

https://dynamics365blocks.wordpress.com/2016/12/18/how-to-retrieve-display-name-of-an-attribute-in-dynamics-crm-using-c/

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