Scheduled reports have always been a bit of a challenge with D365/CRM. It’s easy to create a workflow to notify users and admins about actions on individual records, but sending a list of records on a scheduled interval has always required some sort of development. Using Flow is no exception here, but we’ve been using it for a few months now and found that it’s a great way to automatically send a nicely formatted list of records via email. And once you know how, these Flows are quick to set up.

Here’s an example:

Before we dive in, a big thank you to Nick Dewitt and his Flow Community post that has the methodology we use here. We were struggling to crack the code of how to bring in the names of related records into a list, and Nick’s use of HTML tables works perfectly. We use this Flow here at DC to send a daily email of CRM Records that need to be updated (based on a due date), and we have it set up for a few clients to identify exceptions that need attention (for example, Work Orders that look like they should be closed but are still open). Some other ideas would be notifying users of overdue Tasks, Opportunities with Estimated Close Dates in the past, or pretty much any record criteria you can filter for in Advanced Find. One more thing: This Flow can look a little intimidating at first, but don’t let the “apply to each” loops and HTML tables scare you – after creating an example for yourself you’ll get the hang of it and a whole new world of possibilities to notify admins and pester users will open up.

Here’s a quick overview of how the Flow works

  1. A Recurrence step sets the days/times that the flow will run
  2. A List Records step runs your query to get the records you need
  3. A Variables step creates an empty array (this will hold your data)
  4. A Condition step checks that there’s at least one record returned, and continues if so
  5. For each record in the list
    1. Get Record gets the name from a related record (a record Owner’s full name, for example)
    2. Compose step creates a formatted HTML table row with the columns you need (this will combine the data from the List Records step (step 2) with the name of the related record (step 4a))
    3. Variables step inserts the row into the array created in step 3
  6. Compose step builds the final HTML table with the column headers and all the data from step 4
  7. Send an Email step creates and sends the email with all the information nicely formatted

When complete, it’ll look like this:

For our example, we will create a flow that sends an email at 5 pm Pacific time, Monday through Friday, that includes a list of open tasks that are overdue. Let’s begin: Log in to https://flow.microsoft.com Create a new Flow Add a “Recurrence” step and set the frequency you’d like to send the email Add a “List Records (Preview)” step to query D365/CRM for the records you need. The tricky part about this step is getting the filter query syntax correct. We found that creating an Advanced Find with the appropriate filters and exporting the FetchXML to be helpful here to get the correct syntax. Add a List Records step Rename this to “getOverdueTasks” to make it easier to use later in the Flow. Note that we renamed the step to a name without any spaces. This is a best practice since there are some actions in flow that will break when there are spaces in the name. Add an “Initialize Variable” step to create an empty array This step basically creates an empty container for the data we will gather; leave the value blank for now. Rename this step to “createArray”. Add a “Condition” step to check that the List Records step has at least 1 record. We don’t want to send email alerts with empty tables, so this step is included to check that there’s at least 1 overdue Task to send. We will use an expression here: length(body(‘getOverdueTasks’)?[‘value’]) is greater than 0 Insert an Expression to check for data Add a Condition step to check that there is data Add a “Get Record (Preview)” step and point it to the related entity you need to get information from. Here, we want to get the name of the User that owns the overdue Task. So inside the Yes portion of the condition we’ll create a Dynamics 365 > Get record (Preview) step and point it to the appropriate environment. We want to look up data from the Users entity, where the User is the Owner of the overdue Task (from the getOverdueTasks step). Add a Get Records step to get the Owning User The end result of this step will look like this: Get Record step will be placed into an Apply to Each loop After saving the Get Record step, Flow will automatically place it into an Apply to each block (since we’re getting a value for a single record from a list of records). Rename the Get Record step to “getOwner”. Add a “Compose” step to create the HTML table Next, we want to start building an HTML example that will join our Task data with the full name of the owning User. At this point, we need to determine which fields we want to include in our table. For our overdue task example, we want to bring in the Subject, Due Date, Priority and Owner’s name. It might help to build an Advanced Find first to ensure you’ve got the correct format. Once you have your fields identified, add a Data Operations > Compose step. Within this step, we need to update and paste in some HTML code. Here’s the template:

<tr> <td style="border-bottom: 1px solid #ccc; padding: 8px;"> <a href="https://[orgName].crm.dynamics.com/main.aspx?etn=[entityName]&id=[recordId]&pagetype=entityrecord">[entity.field]</a></td> <td style="border-bottom: 1px solid #ccc; padding: 8px;">[entity.field1]</td> <td style="border-bottom: 1px solid #ccc; padding: 8px;">[entity.field2]</td> <td style="border-bottom: 1px solid #ccc; padding: 8px;">[entity.field3]</td> <td style="border-bottom: 1px solid #ccc; padding: 8px;">[entity.field4]</td> </tr>

Copy and paste this into a text editor (like Notepad) and update the URL for your D365/CRM environment with the orgName and entityName. The other variables (in square brackets) will need to be manually replaced with dynamic content once we get them into Flow. If you need more fields, just copy and paste more of the rows that begin with “<td style=” until you have as many as you need. Remember, this will be built in an email, so we’ll want to keep to just a handful of columns. Here’s what we copied and pasted into our Flow:

<tr> <td style="border-bottom: 1px solid #ccc; padding: 8px;"> <a href="https://example.crm.dynamics.com/main.aspx?etn=task&id=[task.recordId]&pagetype=entityrecord">[task.subject]</a></td> <td style="border-bottom: 1px solid #ccc; padding: 8px;">[task.priority]</td> <td style="border-bottom: 1px solid #ccc; padding: 8px;">[task.dueDate]</td> <td style="border-bottom: 1px solid #ccc; padding: 8px;">[user(owner).fullname]</td> </tr>

After pasting in the HTML, your Compose step should look like this: Now, put the cursor on the first set of square brackets – [task.recordId] for this example – and delete this out, keeping the cursor in the same place (we’ve noticed the best way is to just hit the delete key multiple times). Then in the Dynamic Content window, search for Task and click the field with the description saying it’s the unique identifier of the record. After adding the Task Id, your Compose step should look like this: The Compose step should look like this with the Dynamic Content in place Repeat this process for all the variables you have set up in your HTML table. When you’re finished, it should look like this: The final Compose step should look like this Note that the User’s Full Name field should come from the getOwner step: Rename this step to “createRow”. Add an “Append to Array Variable” step to insert your HTML table row into the array This step adds the table row to the createArray step we created earlier. Rename this step to “appendRowToArray”. Add a “Compose” step to build the final table. Now that we have looped through our list of Tasks to get all the fields we need, it’s time to build the final table with the column headings. Note that this step needs to be outside of the “for each record” loop, so be sure to click the “Add an action” button that’s at the bottom: Add a Compose step here. And we’ll need to add in some more HTML to build our final table. This step adds in the column headings and formats a nice, responsive table. Here’s the template:

<table style="border-collapse: collapse;"><thead><tr> <th style="border-bottom: 1px solid #ccc; padding: 8px; text-align: left; color: #2d4967;">[columnHeading1]</th> <th style="border-bottom: 1px solid #ccc; padding: 8px; text-align: left; color: #2d4967;">[columnHeading2]</th> <th style="border-bottom: 1px solid #ccc; padding: 8px; text-align: left; color: #2d4967;">[columnHeading3]</th> <th style="border-bottom: 1px solid #ccc; padding: 8px; text-align: left; color: #2d4967;">[columnHeading4]</th> </tr></thead> <tbody>[Join(variables('recordList'),'')] </tbody> </table>

Copy and paste this into a text editor and simply replace the [columnHeading#] variables with the column heading names that correspond to the fields included earlier. Here’s what we pasted into our Flow:

<table style="border-collapse: collapse;"><thead><tr> <th style="border-bottom: 1px solid #ccc; padding: 8px; text-align: left; color: #2d4967;">Subject</th> <th style="border-bottom: 1px solid #ccc; padding: 8px; text-align: left; color: #2d4967;">Priority</th> <th style="border-bottom: 1px solid #ccc; padding: 8px; text-align: left; color: #2d4967;">Due Date</th> <th style="border-bottom: 1px solid #ccc; padding: 8px; text-align: left; color: #2d4967;">Owner</th> </tr></thead> <tbody>[Join(variables('recordList'),'')] </tbody> </table>

Next, we’ll need to set up the expression for the table body (simply copying and pasting it in to Flow won’t work). Delete out the [Join(variables(‘recordList’),”)] and keep the cursor there. Then paste (or retype) it back in without the square brackets and click Ok. Insert an Expression When finished, it should look like this: Final Compose step with the Expression inserted Rename this step to “buildFinalTable”. Add a “Send an Email (V2) (Preview)” step to send the email For the last step in our Flow we’ll create a step to construct the email and send it to the team. Note that we’re sending a list of all the overdue Tasks to the entire team here (rather than just the overdue tasks to each user). This makes the flow a little simpler and helps to create a sense of accountability – nobody wants to see their name on this list in front of everyone. Include the email addresses for all those that should receive the email in the To line, add a Subject and set up the body to look like this: Set up the Send an Email step with the output from the final table Finally, open the advanced options and set the Importance of the email to Normal. The default for this step sets the importance to Low which can look a little strange in Outlook. Rename the step to “sendEmail”. Since we don’t want to send the email if there are no overdue Tasks, we don’t need to put any steps in the No side of the condition. So again, here’s a look at the completed Flow: Overview of the final Flow steps Make sure you have at least one record available in CRM that meets the criteria and run a test to make sure everything works properly. That’s it! Again, it might look a little intimidating at first, but try building out this example step-by-step and you’ll get the hang of it. Have questions or need help? Contact one of our solution consultants for a free consultation.

Tagged With

D365CEMicrosoft Flow