Bypass Apply to each loop on single record
The Problem:
Power Automate creates a 'Apply to each' loop when selecting dynamic content from a action that Lists items, folder, or anything.
In most cases this is awesome and creates a nice smooth workflow.
However, what about when you know exactly what you want to look for and you know its only going to be 1 record, item, whatever is going to be returned. Power Automate will still make you use the 'Apply to each' loop.
This may not be a such a terrible thing, but if you need to do multiple things underneath that action, you will have to put them in the loop as well (if you need any data or reference to that action)
Why not use a get record instead:
In some cases, yes a 'Get' action will be perfect, but this is only if you know the "Key".
In my example I only know the users 'Full Name', so I cannot use the CDS 'Get record' action.
Note:
Regardless of what connector or actions you use, this method will allow you to bypass the loop.
Solution:
Okay now on to the magic..
In my example Flow:
- I am using a 'Compose' action to have my Full Name stored.
- I am using the 'List records' CDS action to list records from the default Users table entity.
- I am using a ODATA filter on the 'List records' which I am using to filter fullname
- Under the 'List records' I use a 'Compose' action to store the users ID(Primary Key from CDS) and the users Email Address
My List Records with my Odata filter
Add a Compose action below the List action. And select Expression
Type anything, this is to keep us in Expression mode when we switch back to Dynamic Content tab
If you see the fx Logo in the Dynamic Content Tab, you have done this correctly
Remove what you had, and Click the Value of the action you want to bypass the loop with
Remove the ? and add [0] This is saying we want the first record only. Since this returns an array we say 0 as this is the first record in an array
after the [0] we type what the field name is, in this format: ['feildname']
Click OK.. I usually like to put the Expression in a Comment
Here is another example. Getting the email address from the List records
The expression is:
body('List_records')['value'][0]['systemuserid']
and
body('List_records')['value'][0]['internalemailaddress']
Success
Limitations:
The only thing you have to watch out for is when there is a empty record. This will cause an error if the record is empty.
This can easily be fixed using a Condition If block before the Compose to check if value is empty using the empty() expression.
Thanks.
I hope anyone finds this useful. This boosts performance greatly when you only need one record.
—Josh
If you like my post please hit the "Thumbs Up"
Comments
-
Bypass Apply to each loop on single record
@Jcook yes, of course
I use the output of a filter array to slim down the original (excel array) back to the records that I need. the filter array consists of objects with 2 columns (if I use the right vocabulary for things here). My aim is to filter the original Excel array on the ID column and only display the records with ID "ZN0999ZD-6049IK22-10967JARIE", "ZN0888ZD-6049IK22-10967JARIE" or "ZN0777ZD-6049IK22-10967JARIE"
The output of the filter array:
{"body": [{"VerplaatsCheck": "WelVerplaatsen","TekortID": "ZN0999ZD-6049IK22-10967JARIE"}{"VerplaatsCheck": "WelVerplaatsen","TekortID": "ZN0888ZD-6049IK22-10967JARIE"}{"VerplaatsCheck": "WelVerplaatsen","TekortID": "ZN0777ZD-6049IK22-10967JARIE"}]}I know I go wrong at the body('Matrix_filteren_2')?['TekortID'], because when I change it to body('Matrix_filteren_2')?[0]?['TekortID'] it will filter the Excel array on the first TekortID no problem.Another solution that works is to add another select array in between, to only have the column 'TekortID' leftover, then the array looks like["ZN0999ZD-6049IK22-10967JARIE","ZN0888ZD-6049IK22-10967JARIE","ZN0777ZD-6049IK22-10967JARIE"]and the array filter becomes:body('Selecteren_3') and works fine with comparing to the Excel ID column. I only think that adding the additonal select array is unnecessary and that something like body('Matrix_filteren_2')?[xyz]?['TekortID'] should do itIt boils down to the fact that I am confused with arrays, records, objects and how to properly address them. -
Bypass Apply to each loop on single record
I wonder why the '?' needs to be removed between the item and the column.
I have problems to filter an array with a 'column from a multi-column array'. Filtering from a single array works fine, but when I use equals(body('select multi column array')['Column1'],item()?['ColumnX'] it always throws an error (with and without the '?' btw)
-
Bypass Apply to each loop on single record
Thank you all.
@PieterVeenstra Awesome idea. However I like to throw the error if its blank, if i am using this method I am usually expecting at least one record. Which will be easy to configure a run after if needed.
-
Bypass Apply to each loop on single record
Very thoroughly documented steps and timely. Thanks Josh for a great post!
-
Bypass Apply to each loop on single record
Hi Josh,
Great post.
Have you considered using the first() function instead of the [0].
https://sharepains.com/2018/11/13/microsoft-flow-single-item-nested-arrays/
The [0] would potentially return an error when the array is empty where first would return null.
*This post is locked for comments