Send a single email individually with table of row...

artdlov · ‎03-07-2023

To successfully implement this DPA in practice, it requires to make a generic automated process that essentially stacks entity records against related entity field.

This article is prepared for users familiar with Power Automate fundamentals and concepts.

Focus is more on the process steps of managing data and description of actions of Built-in connectors like Data Operations, Variable and Expressions functions.

Your initial email/notification body result might look like this:

You can also customize the style of table like this:

We will go through all steps shown below.

1. When creating a new cloud flow pick a scheduled cloud flow and configure it.

Example:

2. Insert a new step, add an action - Premium Microsoft Dataverse “List rows” and configure it. Example:

Alternatively, you can choose Built-in Data Operation “Parse JSON” action and use Schema for using your data as custom Dynamic content. Example:

3. Insert a new step, add an action – Build-in Variable “Initialize variable”. Configuration:

Give it a name relevant to context. Example: Email (which is Primary internal email address of an owning user.)
Choose Type – “Array”.
Keep “Value” blank.

4. This step is optional (not mandatory) but can be required depending on a case. (Skip to step 5).

Insert a new step, add an action – Build-in Control “Condition”.

Reason: to support several scenarios where (If yes) at least one record or (if no) none listed after query to set up different groups of actions.

Configuration:

The equivalent of Condition Expression function in Dynamics 365 is: operator “Contains Data”

In “Choose a value” we are going to insert an Expression function.
- Example:
  - empty(outputs('List_rows')?['body/value'])

or

not(empty(outputs('List_specific_records')?['body/value']))

Choose an operator “is equal to”.
In 2nd “Choose a value” we are going to insert an Expression: false or true (depending on previous function)

5. Insert a new step, add an action – Build-in Control “Apply to each”.

Configuration:

Reason: to identify all records and related owning user primary email addresses.

In “Select an output from previous steps” insert a Dynamics value.
- Example:
  - “Value” from “List rows” previous action.

Add an action – Build-in Variable “Append to array variable” inside “Apply to each”.

Select “Name” from “Initialize variable” previous action.

In “Value” insert a Dynamics value. Example: “Primary email”

6. Add an action – Build-in Data Operation “Compose” outside “Apply to each”.

Configuration:

In “Inputs” insert an Expression function.
- Example:
  - sort(union(variables('Email'), variables('Email')))

Reason: to create a list of unique email addresses in ascending order.

7. Add an action – Build-in Variable “Set variable”.

Reason: to set all listed unique email addresses in ascending order as custom variables.

Configuration:

Select “Name” from “Append to array variable” previous action.

In “Value” insert a Dynamics value. Example: “Outputs” from “Compose” previous action.

8. Insert a new step, add an action – Build-in Control “Apply to each”. By default “Apply to each 2” will be added.

Configuration:

Reason: to prepare a set of actions for each custom variable.

In “Select an output from previous steps” insert a Dynamics value.
- Example:
  - Select custom variable from “Set variable” previous action.

9. Insert a new step, add an action – Build-in Data Operation “Filter array” inside (a new) “Apply to each 2”.

Configuration:

Reason: to stacks or group all previously listed entity records against related entity field (custom variable).

In “From” insert a Dynamics value.
- Example:
  - “Value” from “List rows” previous action.
- In 1st “Chose a value” insert a Dynamics value: “Current item” of (a new) “Apply to each 2”
- Choose an operator “is equal to”.
- In 2st “Chose a value” insert a Dynamics value used in “Append to array variable” inside “Apply to each” previous step.
  - Example: “Primary email”.

10. Insert a new step, add an action – Build-in Data Operation “Select” inside (a new) “Apply to each 2”.

Configuration:

Reason: to prepare table data.

In “From” insert a Dynamics value.
- Example:
  - “Body” from “Filter array” previous action.
- In “Map” Enter keys (as String/text) and Enter values (as Expression functions).
  - Example
    - Account Name / @item()?['name']
  - It is possible include hyperlinks to records:
    - <a href='https://@{uriHost(item()?['@odata.id'])}/main.aspx?cmdbar=true&forceUCI=1&navbar=off&newWindow=true&pagetype=entityrecord&etn=@{replace(item()['@odata.type'], '#Microsoft.Dynamics.CRM.', '')}&id=@{item()?[concat(replace(item()['@odata.type'], '#Microsoft.Dynamics.CRM.', ''), 'id')]}'>@</a>

Additionally, it is possible include hyperlinks to records with Dynamics 365 workflows.

11. Insert a new step, add an action – Build-in Data Operation “Create HTML table” inside (a new) “Apply to each 2”.

Configuration:

Reason: to create standard HTML table data.

In “From” insert a Dynamics value.
- Example:
  - “Output” from “Select” previous action.
- In “Columns” choose “Automatic”.

12. This step is optional (not mandatory) but can be required depending previous step.

Insert a new step, add an action – Build-in Data Operation “Compose” inside (a new) “Apply to each 2”.

Configuration:

Reason: to prevent errors in a later stage when Email body with URL is generated.

In “Inputs” insert an Expression function.
- Example:
  - replace(replace(body('Create_HTML_table'), '<', '<'), '>', '>')

13. Insert a new step, add an action – Build-in Data Operation “Compose” inside (a new) “Apply to each 2”.

Configuration:

In “Inputs” insert an Expression function:

concat(body('Filter_array')?[0]?['owninguser']?['firstname'], ' ', body('Filter_array')?[0]?['owninguser']?['lastname'])

14. Insert a new step, add an action – Build-in Data Operation “Compose” inside (a new) “Apply to each 2”.

Configuration:

In “Inputs” insert an Dynamic Vaues:
- “Output” from previous “Compose” steps.

15. This step is optional (not mandatory) but can be required depending on a case. Insert a new step, add an action - Premium Microsoft Dataverse “Add a new row” and configure it.

Example:

In “Table name” choose “Mails”:
- In “Activity Party Attribute Value - 1” type - systemusers()
- Inside “()” insert an Expression function:
  - body('Filter_array')?[0]?['owninguser']?['systemuserid']
- Make sure you also fill out “Subject”, “Description” (with “Output” from “Compose action” in step nr.14) and “Direction” (as “Yes”).

16. This step is optional (not mandatory) but can be required depending previous step nr.15. Insert a new step, add an action - Premium Microsoft Dataverse “Perform a bound action” and configure it.

Example:

Try it out and compare if results are similar to provided examples at the beggining of the article. Good luck! 😊