Key Microsoft Power Automate Concept: ‘List Rows’ for the D365 System Admin
In the first two blog posts on this series, we identified “Big Picture Power Automate Concepts for the D365 System Admin” and then reviewed the “Power Automate trigger for the D365 System Admin”. (Links below)
We continue exploring “things every Dynamics 365 System Admins should know.” In this blog we’ll discuss the “List Rows” and “Apply to each” actions that let you loop through a result set and process each row.
In Microsoft’s seemingly never-ending quest to provide work for the ecosystem, this action has been renamed “List Rows”; it used to be called “List Records”. This was in response to the related terminology changes: entity is now table, records are now rows, fields are now columns. So List Records is now List Rows.
Microsoft Power Automate List Rows
The Power Automate “List Rows” action returns 0, 1, or many records from your CRM/CDS/Dataflex/Dataverse database.
When you add a “List Rows” action step to your flow, you’ll notice there are many parameters that affect the list of records returned:
Using FetchXML Query to Loop Through Dynamics 365 CRM data
The best way, in my opinion, for experienced CRM System Admins to begin experiencing the power in Power Automate is to run an advanced find query, and then loop through that result set with a “List Rows” Action.
Yes, we can finally loop through records without writing a plugin!
- FetchXML is only possible with newer (“Current Environment)” connector.
- Simplest way to operate on a list of records: create an advanced find query, and “Download Fetch XML”, and paste into the “Fetch XML Query” field in the “List Rows” action.
- Can also use the FetchXML Builder in the XRM Toolbox for more complex queries not possible in OOB advanced find. Paste into the “Fetch XML Query” field in the “List Rows” action, Read more about it here:
- Any fields you want to use in subsequent action steps need to be included in your FetchXML query within the “attributes section”. (For advanced find, select all fields you want to use in the flow. If you forget a column, just go back, add it, and re-populate the FetchXML query block.)
- You can use variables within a FetchXML query. (Typical method is to, e.g., identify one record in advanced find, and then replace that record’s GUID with a variable, that changes for each record in the List Rows result set.)
Good link on FetchXML:
Filter Query
Filter Query is another way you can reduce the rows that are returned by a List Rows action.
Boolean Field: use true/false not 1/0.
When using Two Option Set values in the filter expression, you cannot use “booleanfield eq 1”. You have to use “booleanfield eq true”. Otherwise you get the following error:
“A binary operator with incompatible types was detected. Found operand types ‘Edm.Boolean’ and ‘Edm.Int32’ for operator kind ‘Equal’.”
You can get creative with this filter query, for example:
How to make a filter to return the maximum or highest value of a column
Order by descending (syntax is, without quotes: “ColumnName desc”), then set the Top count to 1. See more on Order by syntax in this help article: https://powerusers.microsoft.com/t5/Building-Flows/Sytnax-for-Odata-Order-By/td-p/55185
Using the “TOP Count” parameter
The “Top Count” parameter can also be very useful when debugging. For example, if your List Rows query returns hundreds of rows, the flow will take a while to run, lengthening your debugging cycle time. To keep using that same query and shorten the process, add a number into the “Top Count” box. No more than that many records will be returned. (I like to enter ‘2’, so I can at least test that my loop is working,)
The List Rows action is the foundational building block for looping through D365 Dataverse data. List Rows creates a table of data that you loop through, once record at a time, with “Apply to each”, which is the next article in this blog series.
QUICK LINK TO DAY 4: “Apply to Each” – Process each record returned by a List Rows action