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.
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.
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.
- Let the flow listen to changes to an Entity A record’s status. This is the triggering action.
- 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.
- 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.
- 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.
- 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.)
So with all the actions pieced together, the flow runs fine. A sample output is below. 🙂
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.
- 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.
- 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.
- 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. 🙂
- 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.
- 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.
- The Entity B records retrieved in step #3 will be looped through to build the html table’s data.
- 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.
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.
A sample of an email created by the workflow is in the image below.
That’s it! Til next time. 🙂