Wednesday, 15 January 2020

How to avoid Apply to Each from appearing

As a Power Automate maker you are happily building your flows in Power Automate and then suddenly after selecting dynamic content as a value in one of your actions, the Apply To Each appears. You think you’ve done something wrong so you delete the action and try again. The Apply To Each still appears!

Well my friend, you’ve come to the right place because in this WTF episode I can help you understand why this occurs and when you can avoid the Apply to Each from appearing. The first half of this episode is from the perspective of a CDS action however is applicable to the equivalent SharePoint action as well. In case you aren't aware, my background is with Dynamics 365 and my experiences with the Apply to Each has arisen from developing solutions for business use cases in Power Automate with flow.

Why or How does it appear

I have experienced the Apply To Each appearing when I select dynamic content from a CDS List Records action or an HTTP action. This is due to the JSON response as it is in the form of an array.

The way I like to explain an array to an everyday person is to think of it as a structured collection of data. Think of a table in an Excel worksheet. A table is structured where there are columns and rows of data. Each row would be an index and in each row there will be property values associated as reflected by the columns.

When a property is referenced from an array in an action, flow in Power Automate recognizes it is associated to an array and therefore the Apply to Each appears automatically. Flow in Power Automate will have the Apply to Each cocoon your action as it thinks the action is for each array.

In this WTF episode I’ll refer to the two common causes, in my opinion. There can be different causes but these are the two I tend to come across.

CDS List Records action – Cause No.1

There are times when a CDS List Records action is used and only a single record is expected to be returned. When using a List Records action there are multiple records expected to be returned in the result so the structure of the JSON response accommodates this in the form of an array.

An example is from a previous WTF episode where a CDS List Records action (the action in my Power Automate with flow is the 1.5 Get Time Zone Name of User) is used against the Time Zone Definitions entity that contains the time zone information used by CDS/Dynamics 365. Each array in the response will represent the different time zones. One of the properties in each array is standardname which is a value that can be referenced in expressions that require a time zone value, such as the Convert Time Zone action.

In my action I used a filter query to only return the time zone definition record that is equal to the time zone code of the CDS/Dynamics 365 user. This was so that only one record is returned rather than all time zone definitions as I’m only after the standardname property to identify the time zone name of the CDS/Dynamics 365 user to use downstream.

When you try use dynamic content to reference the standardname property in a subsequent action, the Apply to Each will appear which is what was seen in the Convert Time Zone action.


When you review the output of a List Records action it will be in the form of an array. The clue is from the square brackets [] that you’ll see in the output.

Parse JSON action – Cause No.2

Sometimes the Parse JSON action is used after an HTTP action in order to reference properties downstream. I’ll refer to my Power Automate with flow from this WTF episode as an example.

In my Power Automate with flow the goal is to reference the windowsTimeZoneId property downstream. The JSON response of the HTTP action is structured in the form of an array, similar to a CDS List Records action.

If we use the Parse JSON technique, we have to jump through a number of Apply to Each records to get to the windowsTimeZoneId because this property is buried within arrays.

Assess and choose a technique

I’m going to separate this section into two explanations in reference to the previous examples provided.
  • The first part will be in regards to a simple property where it is not buried in arrays.
  • The second part will be in regards to a complex property where it is buried in arrays.

When a simple property is to be referenced

Option one

When a single record is expected to be returned in the CDS List Records action or even an HTTP action, an expression can be used either directly in a field within an action or can be used in a Compose action. I'll use the Flow in my previous WTF episode as an example where the standardname property is to be referenced from a CDS List Records action that retrieves the time zone details of a user.

The key here is to use the first function in the expression. It will look something like this,


To explain this expression I’ll describe it in the way that my friend John Liu did when he helped me understand for my learning.

1.Start with outlining the array that is to be referenced. This is selected through the dynamic content Flow builder using the value output from the CDS List Records action - 1.5 Get Time Zone Name of User from the action (from my previous WTF episode).

body('1.5_Get_Time_Zone_Name_of_User')?['value']

2. Since we are only expecting one record to be returned in the List Records action, the next part of the expression is to wrap the first function around the output. If I go back to the Excel example from the beginning of this blog post, we are only going to reference the first row.

first(body('1.5_Get_Time_Zone_Name_of_User')?['value'])

3.The final part in the expression is the property to be referenced from the first row/record in the array which is standardname. This will be at the end of the expression.

first(body('1.5_Get_Time_Zone_Name_of_User')?['value'])?['standardname']

Bonus tip

The ? makes each property in the expression optional. For example if standardname is missing, the expression will return null and will not cause the flow in Power Automate to crash.
Using an expression either directly in an action or referencing from a compose action is suitable when you are only referencing a single property/field or let’s say two properties downstream in the Flow.

Option Two

Another way of writing an expression is the following

outputs('1.5_Get_Time_Zone_Name_of_User')?['body']?['value'][0]?['standardname']

This will retrieve the first row in the array of the JSON response from the [0] and the standardname property.

When would these options not be suitable

If you are going to be referencing a properties that aren't as straight forward, refer to the next section for another method.

When a complex property is to be referenced

Below is a screenshot of the JSON response in the HTTP action that I referenced earlier in the “Parse JSON action – Cause No.2” section. This is where we want to reference the windowsTimeZoneId downstream in my flow with Power Automate and the property is buried in arrays. To help with your understanding of the issue refer to the following screenshot.


You can see the windowsTimeZoneId property is buried in arrays. I’ll work backwards to help you with your understanding. The windowsTimeZoneId is within the timeZone array, which lives within the timeZoneAtLocation array, which lives in the resources array, which lives in the resourceSets array. Therefore Power Automate thinks the proceeding action is for each of the arrays within the output of the HTTP action.

The expression to use will be the following as per the above explanation where the first row in each array is represented by [0]

Summary

When you have a property to reference downstream and it’s in a simple structure within the JSON response, such as a CDS List Records action, use an expression that will either use the first function in the expression or has a reference to the row in the array. Otherwise if you're referencing a property that is not as straight forward where it may be buried in an array Option Two is better. These are the most two common scenarios I come across as a Power Automate maker working with Dynamics 365/CDS.

You can also check out John Liu’s blog post that mentions the Apply to Each appearing phenomenon, refer to Problem 2 - the array - item object and auto apply-each wrapping.

Friday, 20 December 2019

Playing Spotify to Google Home using Flic and Power Automate

04:58 Posted by Benitez Here , , , , , No comments
This is a special holiday edition #WTF episode where I came up with a fun idea of playing a Spotify Christmas on my Google Home through one click from my Flic button and Power Automate. I had a lot of fun with Megan Walker's MVP Advent Calendar - check out my gift here, and I wanted to do one more holiday themed vlog before the end of 2019.

The warm-up

I had a look at the Spotify developers API documentation to identify the requests I needed. Once I found the API requests I needed, I had to sort out the authentication method and in the Spotify Authorization Guide there was a method that I could use called "Authorization Code." I knew of the tool Postman from my last project that I worked with Natraj Yegnaraman when I was living in Melbourne but I didn't know how to actually use it. I asked my awesome colleague Rex Wessels (🦖) about Postman and he taught me how to work with Postman. After a few hiccups the API requests were successfully sending as I was getting the responses I needed. Hooray!

An obstacle I experienced was that the access code used by the Authorization Code expires and requires a new token which can be generated from the refresh token. I was wondering how to replicate this in Power Automate. I had a go with the HTTP action in Power Automate I couldn't get it work, an error was occurring.

That API Guy

I messaged one of the #FlowFam crew, Vivek Bavishi, for some guidance because I knew he successfully worked with the Spotify APIs from his road trip to UG Summit with Jon Levesque, Ed Gonzales and Anton Robbins. They had this awesome idea of getting the Microsoft Community to interact with them on Twitter by tweeting a song and artist with the hashtag of #ElectricMayhemRequest 

Vivek created a Power Automate that collected the songs requested through Twitter and added them to a Spotify playlist so that they could listen to the music in their road trip. If you want to watch their cool road trip you can do so here.

After sharing my error with Vivek he said that I could connect with Spotify through a custom connector. We jumped on a Microsoft Teams call and he shared with me how to do it. As a result he has blogged about it which you can read here. I won't outline the steps on how to set up the custom connector as Vivek has already provided a guide in his blog post so read it as a prerequisite before following my steps in this #WTF episode.

Setting up the Spotify actions in the custom connector

When you create a custom connector in the Power Platform you can configure actions. In Power Automate this will be something you are already familiar with - think of CDS List Records action, CDS When a record is created action and so forth. Adding actions in the custom connector allows Power Automate makers to select the action when they apply it in their Power Automate with Flow.

There will be two Spotify API requests that I'll be using in my Power Automate with Flow.
This Spotify API request will return a list of active devices. Take note that in the Spotify API documentation it states
When no available devices are found, the request will return a 200 OK response with an empty devices list.
If you see no devices in the returned response it means that you don't have active devices. I've found that with my Google Home it will drop off from the list after a period of time if no music is playing. The JSON response will be an array where any device found is in a row.

To configure the action click on the Import from Sample button.


In here you can select the method, the URI and if required the JSON body. Below is an example screenshot.


Once you import it will be available as an action that can be selected in Power Automate through your custom connector.

Start/Resume a User's Playback 

This Spotify API request will play a playlist, album or artist based on the Spotify ID that is provided in the JSON body. You can find the Spotify ID through the Spotify web player which I've highlighted in yellow in the below screenshot.


One of the query parameters that is optional in this Spotify API request is the device ID.


If a device ID is provided, Spotify will play the playlist, album or artist to the device. This is why we need to call the Spotify API that will retrieve the list of active devices so that Google Home can be called to play the selected Spotify playlist/album/artist.

Follow the same steps as per above and don't forget to add the JSON Body which you can find in the Spotify API documentation.


Note: As per Vivek's blog post it's important that you provide the scope outlined in the Spotify API documentation for the API Spotify request you are calling. This is done in the Security tab of the custom connector otherwise an error message will be encountered as the scope has not been defined in the authentication.

Testing your APIs

One thing I didn't demonstrate in my vlog is that you can test your actions within the custom connector to set up prior to using them in Power Automate. You can run the action and check whether the response is successful.

Lets Automate

The following is what my Power Automate looks like.

1.0 When Flic is clicked

The trigger will be when a single click is executed on the Flic button.

1.1 Retrieve Devices

In this step My Spotify custom connector can be used where we select the action that will retrieve the devices.


There is no need to provide any parameters.

1.2 Parse JSON

The Parse JSON is used for the purpose of using the Filter Array action downstream.


If we don't Parse the JSON and if we attempt to use the Filter Array action the following error will occur.


Power Automate doesn't recognize the output as an array.

1.3 Initialize Variable

To use the Filter Array downstream in Power Automate we need to use the Initialize Variable action for the output from the Parse JSON action. 

1.4 Filter Array

Since multiple devices will be returned from the Spotify API in 1.1 Retrieve Devices, the Filter Array action is used to only retrieve the row in the array that has the Google Home device ID so that we can use it in the action that will call the Start/Resume a User's Playback Spotify API request.

I used the name of my Google Home device as the condition.


The JSON response will then only return the row in the array where the name property is equal to my Google Home device. This is so that we can use the device ID property value in the final action of Power Automate.

1.5 Play on Device

This is the magic finale! I am using the other action in My Spotify custom connector where I can reference the device ID from my Filter Array action in an expression.


The expression will look like the following,

body('1.4_Filter_array')?[0]?['id']

I am also providing the context URI for my chosen Spotify Christmas playlist. This is required as seen in the the Spotify API documentation for the Start/Resume a User's Playback request.

Power Automate in action

I did a video on my Twitter which shows me clicking my Flic button and my Google Home playing my chosen Spotify Christmas playlist which you can view here.

Summary

Using the Flic button and a Power Automate custom connector to call the Spotify APIs, a chosen Spotify playlist/album/artist can play on a device such as Google Home.

As mentioned earlier, devices will only be returned by the Spotify API if they have been active or idle. Don't forget to check out Vivek's blog post for the steps in setting up a custom connector for your Spotify account.

If you want to purchase a Flic button head over to their website. They have recently released their next version of Flic called Flic 2.0. I am not sponsored by Flic by the way, I simply like Flic and like being creative with it using Power Automate 😄

One final note

Thanks again for supporting me and subscribing to my YouTube channel.
I hit my 1,000 subscribers milestone last month 😁 I am grateful, thank you!

Wednesday, 11 December 2019

Displaying dates in a Create HTML Table action in Power Automate

This is a continuation from my previous WTF episode where I shared how to email a tabular list of cases that were either five most recent cases or five oldest cases of a Contact. One of the column values is the Created On field from the Case record. When you view the sent email the dates will be displayed as UTC.

As mentioned in previous episodes, Power Automate treat date and time as UTC therefore will be displayed in a UTC format when retrieving the value. The answer as always from my other WTF episodes is to convert the UTC to the desired time zone which is what I’ll cover in this WTF episode.

Two Options  

There are two options that can be used to display the date and time correctly.
  1. If Contacts are in a single time zone, a single expression can be used in the 1.2 Create HTML table action in the Flow seen in my previous WTF episode. For example in New Zealand there is only one time zone. If an Organisation is sending emails to customers that are based in New Zealand and no other country this option would be appropriate. 
  2. If Contacts are in multiple time zones, a few more actions are required which has been shared in previous WTF episodes. For example in Australia there are multiple time zones. If an Organisation is sending emails to customers nationwide then a single expression with a static time zone value is not valid.

Option 1 – single expression 

Since the Created On date is a UTC value the function to use is convertFromUtc


This function requires 
  1. The property that represents the UTC value 
  2. The destination time zone name which will be a string value that represents the name of a time zone (note I use the time zone names from this article)
  3. The format of the date and/or time of the desired output, for example dd/MM/yyyy 
The expression will look like this 

convertFromUtc(item()?['createdon'], 'AUS Eastern Standard Time', 'dd/MM/yyyy')

This expression will convert the Created On UTC value to a date and/or time that is more understandable by the recipient. The email will now display the Created On date value using the defined time zone in the expression.


This option is suitable if the Organisation has Contacts in a single time zone because of how the string value is explicitly referenced in the expression. It is not flexible for Contacts in multiple time zones.

Option 2 – a few more actions 

In previous WTF episodes I shared how to convert a UTC value into a value of the Contact’s local time zone. 
You’ll see that the following actions is used in all three episodes which I’ll once again use in Option 2:
  1. Bing Maps action 
  2. HTTP action 
  3. Compose action 
These three options need to be before the Create HTML table action. 


The next steps to be explained are covered in my last blog post for my Flow Online Conference session which I’ll use again for this WTF episode. 

1.2 Get Contact Location by address 

As seen in a previous WTF episode there is a Bing Maps action "Get location by address" that allows the location latitude and longitude to be identified based on address information. The Address 1 fields in the contact record will be used. 


1.3 Identify local time zone of Contact 

There are additional Bing Maps Time Zone APIs available which you can read from this Bing Maps blog post. These APIs are not available as Bing Maps action in Flow however the HTTP action can be used to call the APIs.

In order to find out the time in the primary contact's time zone, the "Given location coordinates, find the time zone of the place" Bing Maps API can be used through a HTTP action.

Simply reference the latitude and longitude outputs from the previous step followed by inserting a Bing Maps key.

https://dev.virtualearth.net/REST/v1/TimeZone/latitude, longitude?key=<bingmaps-key> 


1.4 Retrieve windowsTimeZoneId property 

To ensure the contact views the date and time the email in their local date we need the name of the time zone they are located in.

The windowsTimeZoneId property will have the name of the time zone which can be retrieved from the JSON response of the previous HTTP. Below is a screenshot of the response.


There are two methods which can be used to retrieve the windowsTimeZoneId property which I’ve covered previously. The Compose action will be used to reference the property without using the Parse JSON and simply an expression instead.


The expression will look something like this
body('1.3_Retrieve_local_time_of_contact')?['resourceSets'][0]?['resources'][0]?['timeZoneAtLocation'][0]?['timeZone'][0]?['windowsTimeZoneId']

1.5 Create HTML table 

The convertFromUtc function can now reference the localTime property using the output of the compose action.

The expression will be the following
convertFromUtc(item()?['createdon'], outputs('1.4_Retrieve_windowsTimeZoneID'), 'dd/MM/yyyy')

1.6 Send email to Contact 

Same set up as to the email as per the previous WTF episode and in Option One where the Created On date will be displayed in the local time zone of the Contact based on their latitude and longitude address 1 coordinates.

Summary

When dealing with date and time values where it is to be displayed to a user or a customer, it is important to remember that Power Automate treats date and time as UTC. The date and time value will need to be converted to a destination time zone which can be done using the Convert Time Zone action or several functions such as convertFromUtc as per this blog post.

Wednesday, 4 December 2019

Rickrolling these holidays with Power Automate and Microsoft Teams

Earlier this month I was asked by the wonderful Megan Walker whether I'd like to participate in the MVP Advent Calendar 2019. Of course I said yes! I thought about what I could share and decided to do something fun rather than my usual "How-To's." I learnt some new things as a result.

The idea

Since the holidays 🎄 is around the corner I wanted to do something in the theme of the season. One thing that most of us will be doing is saying bye to everyone before heading out the door or finishing up for the day. I came up with an Instant Flow that I could use where it would be triggered from my Power Automate app from my mobile. With one tap I can notify my crew in Microsoft Teams and send an email to all my primary contacts of the clients I work with.

BUT... I took a leaf out of Ashlee Culmsee's book where I decided to Rickroll my team from the message I post in the channel in Microsoft Teams 😁

Watch my vlog below and you can download my Power Automate here.

Let's Automate

This is what my Power Automate with Flow looks like, refer to screenshot below. We're using a parallel branch so that the emails sent to the contacts and the message posted to the channel in Microsoft Teams can be processed at the same time independently.

1.0 Manually trigger a Flow

As mentioned this will be an Instant Flow where the trigger will be a Flow button so that I can run my Power Automate with Flow from my mobile.

1.1 Retrieve Primary Contact records

When your Power Automate with Flow is in a solution and the CDS List Records action is used, an additional field will appear in the action called FetchXML.


This will be familiar to professionals who have been working with Dynamics 365 for a while. It's been used to query data to produce results that can't be achieved out-of-the-box in Advanced Find and for custom reports.

Using the FetchXML field in the CDS List Records action allows us to apply queries that we're already used to in Power Automate. For my query I only want to return contacts that are a primary contact for an account where I am the Owner of the contact record.

This is achieved by building the query in Advanced Find in the model-driven app and then selecting the download FetchXML button from the Advanced Find window. The FetchXML will then be available to use.

Copy and paste into the Fetch XML Query field.

1.2 Apply to Each

To email all the contacts returned in the CDS List Records action, an Apply to Each action will be used.

1.3 Send an email to Contact

The Office 365 Send an Email (V2) action will be used to send the email to the contacts returned in the CDS List Records action.

Bonus tip

One thing to be aware of is even though the full list of fields will appear when you use dynamic content, you can only reference the fields that were included as part of your FetchXML query. In my scenario I only included the fullname Contact field and therefore am referencing this field since I did not include firstname Contact field in my FetchXML query. If you do reference fields that are not in your FetchXML query from the CDS List Records action it will appear as blank.

Below is an example screenshot of an email when referencing the firstname. Since firstname is an attribute not defined in the FetchXML query, nothing is displayed in the output of the sent email.

1.4 Invoke an HTTP request

Currently we can't process mentions in the available Microsoft Teams actions however the Graph APIs can be called. Two Graph API requests will be used in my Power Automate with Flow.

First of all in order to perform any Graph API request there needs to be some type of authentication which can be achieved using the Invoke an HTTP request action as it will use Azure AD. In this action you must provide the following for the connector
  1. Base URL
  2. Base Resource URI
In my scenario it will be https://graph.microsoft.com. Below is an example screenshot.


Once the connector has been successfully completed, you'll then see the ability to
  1. Declare the type of API request 
  2. Provide the JSON body for the API request
Below is a screenshot from my Power Automate with Flow.


The List channels ID API request is used as per the docs.microsoft.com article to retrieve the Channel ID and the name of the Channel so that we can use these two properties downstream in Power Automate with Flow. Part of the API request requires the Teams ID.

1.4.1 How to identify the Teams ID

The ID of a Team can be identified using the List all teams in Microsoft Teams for an organization API request and using Graph API Explorer to perform the request, you can retrieve the Teams ID.

1.5 Channel Name

Using a Compose action we can reference the displayName property from the response in the previous 1.4 Invoke an HTTP request action so that it can be used in our final action downstream. The expression I am using is the following

outputs('Invoke_an_HTTP_request')?['body']?['value'][0]?['displayName']


Since I'll be using it more than once in the final action I thought a Compose action would be more practical rather than referencing/writing the expression multiple times.

1.6 Channel ID

I'll also need the ID of the channel for the next action and using a Compose action to reference the ID property from the List channels response from the previous 1.4 Invoke an HTTP request action. The expression I am using is the following

outputs('1.4_Invoke_an_HTTP_request')?['body']?['value'][0]?['id']

1.7 Invoke an HTTP request

The finale is using the Create chatMessage in channel API request as per the docs.microsoft.com article. In the request URL this is where we can reference the channel ID from the output of the 1.6 Channel ID action.

In the JSON Body there will also be a reference to the channel name from the output of the 1.5 Channel action.


I came up with the schema for the JSON Body from using the "Get Messages" action in a separate Power Automate with Flow where I was able to grab the body that contained the channel I wanted to post a message to.


This is what it looks like when viewing the entire response in Notepad++ where I've highlighted the part I used.


By using that section of the JSON Body I adjusted it by adding my own message and a URL reference to the Rick Roll video on YouTube.

Rickrolling in action with Power Automate and Microsoft Teams

Here's the fun part!!!!

On a more serious note

The emails will actually send too for my clients to be notified of my out-of-office dates as part of the holiday season.

Summary

I learnt a lot about the Microsoft Graph API and Graph Explorer in this fun holiday themed Power Automate. There's a lot more that we can do with the Microsoft Graph API if there are Microsoft related actions that are not available today in Power Automate. One thing to bear in mind is that Create chatMessage in channel API request is a beta API and it says clearly that it's not recommended for Production purposes.

Make sure you stay tuned for more gifts as part of the MVP Advent Calendar and shout to Megan Walker for kindly asking me if I'd like to participate.

Thursday, 28 November 2019

Retrieving most recent or oldest records in Power Automate

“I only want the most recent Cases of a Contact so that I can email the customer a list of their cases at the end of the day.”

“I only want the oldest Cases of a Contact so that I can let my team know they need to attend to these Cases immediately to resolve them.”

Heard something similar before in regards to the above user stories around the requirement to retrieve the most recent or oldest records? Previously in Dynamics 365 classic workflows it’s hard to perform a query and then insert those results in some form of an email or notification to the Customer or colleagues in the company.

I use the above user stories as a means of understanding the method applied in Power Automate with Flow. This can be applicable to any entity and similar use cases that require retrieval of recent/oldest or even top/bottom type of data that you're familiar with in Microsoft Excel already.

I show how this is achievable using CDS and Power Automate with Flow in this WTF episode.

Most recent records

Since more than one record is to be retrieved the CDS action of “Get Record” is not valid since this action retrieves a single record. The CDS action to use is “List Records” where a query is performed to retrieve the records that meet the criteria. In the CDS List Records action there are a couple of fields in addition to the Filter Query field that will be used. These fields are
  • Order By 
  • Top Count 

Order By 

This is where you can define the field that will be used for the most recent parameter. Examples are using a Date field such as Created On from the Case entity or a Currency field that has a dollar amount such as an Estimated Revenue in the Opportunity entity. The values that can be entered is
  • desc for descending – for example orders date results from oldest to newest or currency from highest to lowest 
  • Asc for ascending – for example orders date results from newest to oldest or currency from lowest to highest
You can use more than one field as well when you want to order you results returned. This is the same principal as sorting a column in a system view, personal view or advanced find in a model-driven app. For more information on ordering you can review this docs.microsoft.com article.

Top Count 

This is where you can define the number of records to be retrieved. If you enter 1, only a single record will be retrieved and it would represent the most recent/oldest record. If you enter 5, five records will be retrieved and these would represent the five most recent/oldest records. For more information on top you can refer to this docs.microsoft.com article.

Use Case 

The user story I referred to in the vlog is the following which I'll refer to in the remainder of this blog post.
I only want the most recent Cases of a Contact so that I can email the customer a list of their cases to provide them with an update. 
Overall the Flow looks like this:

1.0 When a Contact is selected trigger 

For the purpose of explaining this How-To, I used an Instant Flow with the CDS “When a record is selected” trigger for the contact entity.

Other triggers could be Recurrence or the alternative is to have the actions downstream in a Flow based on preceding actions.

1.1 Retrieve top 5 most recent Cases 

The next action will be the CDS “List Records” action with the following
  1. Entity will be Contacts 
  2. Filter query is _primarycontactid_value eq trigger contactid 
  3. Order By is createdon desc 
  4. Top Count is 5. This will retrieve Cases where the Case Contact field value is equal to the Contact selected and only five cases with a most recent Created On date value. 

1.2 Create HTML table 

The next action to use is the Create HTML table that will allow the retrieved records to be displayed as a table in an email sent to the Contact. It’s a fairly simple action to understand,
  1. From is the value dynamic content from the previous CDS List Records action 
  2. In the Header is where you enter what you’d like text to appear as the Headings 
  3. Value is the desired field values from the Case entity through the previous CDS List Records action

1.3 CSS style of table 

If you use the Create HTML table action as-is by inserting in the Outlook Send an email action, it won’t be formatted nicely. I learnt this tip from fellow MVP Andre Margono, refer to this blog post of his. A compose action will contain the CSS that will be applied to the output of the Create HTM action.

1.4 Send an email to the Contact 

The final action is the Outlook Send an email action where
  1. The Contact’s email address from the Flow trigger is used in the To field 
  2. The Subject is entered 
  3. The content of the email message includes the Output from 1.3 CSS style of table which will have the table nicely formatted

What the Flow in action 

Open a Contact record that has more than 5 active cases associated to the Contact. In the command bar, click on the ellipsis and select the Instant Flow to run the Flow.


You’ll see the email with the nicely formatted table of the five most recent cases for the Contact. Awesome sauce.


Oldest records 

To retrieve the five most oldest Case records simply use asc instead of desc in your Order By field in the CDS List Records action.


When the email is sent it will show the five oldest Cases.

But wait 

How come the date is a bit funky in the email? In my next WTF episode I’ll show you how to overcome this issue. Stay tuned!

Summary 

It’s quite simple to query records by using a quantitative value (such as 5 or 1) and using desc/asc for retrieving the most recent (or top) and older (bottom) records of an entity using Flow. In classic Dynamics 365 workflows it’s not available out-of-the-box to query records and there is no type of formatting of tables in the Send email workflow action either. Flow removes these boundaries without the use of development.

In my next WTF episode I'll share with you how you can correctly display dates when using the Create HTML table action.