In Dynamics 365 Business Central it’s quite common to do operations in AL code for filtering a set of records and then retrieving a value of the first record of the filtered recordset. Something like in the following code, where we filter the Item table for retrieving the first item with Unit Price greater than 1000:
Let’s imagine now to use Power Automate to do the same thing. We can use the Business Central Connector and the Find Records action for filtering the Item records and retrieving the TOP 1 record in the following way:
Now we can create a Float variable called FirstUnitPriceGreaterThan1000 and assign to that variable the value of the first item’s unit price returned by the Find Records action. What happens in the Power Automate designer when you do that is the following:
As you can see, Power Automate designer adds a for each loop block automatically. This loop is executed always only 1 time, but it’s always a loop block and if you were one of the attendees of my Azure Logic Apps section in the last Directions EMEA in Lyon you know what’s there under a loop in Power Automate or Logic Apps (storage, queue etc).
Obviously, the flow is perfect and it gives the result I want:
We have one single loop and the output of my FirstUnitPriceGreaterThan1000 variable is exactly the first value I want.
How can I optimize this?
To fully avoid to have a for each block for retrieving a single item of a dataset of records in Power Automate, you can use an expression like the following:
first(body(('YourFindRecordActionName')?['value'])?['YourFieldName']
In the example of this post, we can directly assign to the Set variable action the value accordingly to this formula:
first(body('Find_Items_with_Unit_Price_greater_than_1000')?['value'])?['unitPrice']
And here is how my flow appears now:
As you can see, we don’t have a for each block here. Much better…
I see quite often flows with the “unuseful” for each loop in these cases, so remember this tip! 🙂
Stefano Demiliani 