cancel
Showing results for 
Search instead for 
Did you mean: 
Jcook

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 filterMy List Records with my Odata filter

 

Add a Compose action below the List action. And select ExpressionAdd 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 tabType 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 correctlyIf 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 withRemove 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 arrayRemove 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']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 CommentClick OK.. I usually like to put the Expression in a Comment

 

 

Here is another example. Getting the email address from the List recordsHere 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']

 

 

 

SuccessSuccess

 

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

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.

Anonymous

Very thoroughly documented steps and timely. Thanks Josh for a great post!

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.

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)

Hi @jakenl79,

please post a sample of your data and the expected result.

 

thanks 

@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"
}
]
}
Screenshot 2022-05-31 22.48.08.png
 
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 it
 
It boils down to the fact that I am confused with arrays, records, objects and how to properly address them.

@Jcook any suggestions on the above example?

About the Author
  • Experienced Consultant with a demonstrated history of working in the information technology and services industry. Skilled in Office 365, Azure, SharePoint Online, PowerShell, Nintex, K2, SharePoint Designer workflow automation, PowerApps, Microsoft Flow, PowerShell, Active Directory, Operating Systems, Networking, and JavaScript. Strong consulting professional with a Bachelor of Engineering (B.E.) focused in Information Technology from Mumbai University.
  • I am a Microsoft Business Applications MVP and a Senior Manager at EY. I am a technology enthusiast and problem solver. I work/speak/blog/Vlog on Microsoft technology, including Office 365, Power Apps, Power Automate, SharePoint, and Teams Etc. I am helping global clients on Power Platform adoption and empowering them with Power Platform possibilities, capabilities, and easiness. I am a leader of the Houston Power Platform User Group and Power Automate community superuser. I love traveling , exploring new places, and meeting people from different cultures.
  • Read more about me and my achievements at: https://ganeshsanapblogs.wordpress.com/about MCT | SharePoint, Microsoft 365 and Power Platform Consultant | Contributor on SharePoint StackExchange, MSFT Techcommunity
  • Encodian Owner / Founder - Ex Microsoft Consulting Services - Architect / Developer - 20 years in SharePoint - PowerPlatform Fan
  • Founder of SKILLFUL SARDINE, a company focused on productivity and the Power Platform. You can find me on LinkedIn: https://linkedin.com/in/manueltgomes and twitter http://twitter.com/manueltgomes. I also write at https://www.manueltgomes.com, so if you want some Power Automate, SharePoint or Power Apps content I'm your guy 🙂
  • I am the Owner/Principal Architect at Don't Pa..Panic Consulting. I've been working in the information technology industry for over 30 years, and have played key roles in several enterprise SharePoint architectural design review, Intranet deployment, application development, and migration projects. I've been a Microsoft Most Valuable Professional (MVP) 15 consecutive years and am also a Microsoft Certified SharePoint Masters (MCSM) since 2013.
  • Big fan of Power Platform technologies and implemented many solutions.
  • Passionate #Programmer #SharePoint #SPFx #M365 #Power Platform| Microsoft MVP | SharePoint StackOverflow, Github, PnP contributor
  • Web site – https://kamdaryash.wordpress.com Youtube channel - https://www.youtube.com/channel/UCM149rFkLNgerSvgDVeYTZQ/