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.