Microsoft Flow – 2 methods to not use Apply to Each action when only a single filtered item is expected

  • Post Category:Flow
  • Post Comments:14 Comments
3+

In this post I will show you 2 methods when using Apply to each Flow action is not necessary. By not using them you make your Flows look cleaner, less complicated.

Please note, these methods are mostly good when only a single item is expected after filtering an array.

I created a SharePoint List with two text columns – Title and Color. Both columns are required. Title values are set to be unique (!).

When using Get Items action, an array of items is returned regardless if it’s a single item in it or not. The moment you try to use a Condition action on its result, the system will insert an Apply to Each action to go through every single possible item. Again, it’s redundant when by design a single item is returned after filtering.

In my test flow I’m using a very simple Filter Query on purpose – Title eq ‘Test 1’. In a real life situation that value you are filtering by can come from any other source and should make more sense.


The Flow without using Apply to Each action:

Method 1 uses a First function. The code for Condition action:

@equals(first(body('Get_items')?['value'])?['Color'], 'Blue')

Method 2 uses ?[0] to get the first item in the array. The code for Condition action:

@equals(body('Get_items')?['value']?[0]?['Color'], 'Blue')

Both of these expressions above are for when Edit in advanced mode is selected for Condition actions.

3+

This Post Has 14 Comments

  1. Sherwin

    After many hours of searching the web, this got the job done!! I had to query a SharePoint list of over 70k records and Apply for each was killing me. Runtime used to go for over 4 hours now it’s down to seconds. I can’t thank you enough for this. Cheers!

    0
    1. Pavel Bludov

      Hi Sherwin,
      This is the kind of feedback that is always great to see!

      0
  2. ouss

    Hello, I had 500000 files I need to go trough with flow. Can I use this methode as well?
    Can’t figure out the right way to write it down. I need to filter on “Starts with” 0345,0355 etc.

    Can I use this methode as well and how do I write it down? It takes ages now to go through the apply to each…..

    0
    1. Pavel Bludov

      Hello,
      Apply To Each can only go through 5,000 or 100,000 depending on your license – more https://docs.microsoft.com/en-us/power-automate/limits-and-config
      Get Items supports pagination, Get Files doesn’t support it. However, for both you can use a workaround explained by a Microsoft Employee here – https://alextofan.com/2019/08/22/how-to-get-more-than-5000-item-from-sharepoint-online-in-flow/
      Filter Query should support StartsWith – more here https://sharepains.com/2018/11/12/sharepoint-get-items-odata-filter-query/
      Getting back to my post, if you know the position of an element, you can always address it through something like this – body(‘Get_items’)?[‘value’]?[3000]?[‘Color’]

      0
  3. ouss

    My file name (field is the default name field) 0345, 0355 etc

    is the query something like this: body(‘Get_files’)?[‘value’]?[0345]?[name’]?

    0
    1. Pavel Bludov

      I don’t think this approach will work for you as a file name very unlikely correlates with Apply To Each.
      Also replying to your email.

      0
  4. ouss

    ah oke, how can I create this formula?

    @equals(first(body(‘Get_items’)?[‘value’])?[‘Name’],’0202′)

    It gives me an error now…

    0
    1. Pavel Bludov

      Replied to your email.

      0
  5. mou

    giving error to me as well.. how to resolve?

    0
    1. Pavel Bludov

      Hello,
      What kind of error? Are you using my example or yours?
      Thanks!

      0
  6. Jay

    Hello, Pavel thank you for this awesome post but there is no option to edit in advance for me! So how can I use that?
    My problem is I want to use 2 dynamic values in condition. So if I try to add those in condition then it automatically adds two “apply to each loop” resulting in running the flow in n^2 no of times.

    What I am doing:
    Let say there are 2 excel files one is Master.xlsx and the other is Temp.xlsx Now I have scheduled flow on Friday to update Temp.xlsx as per the Master.xlsx Like if someone adds or delete a row in Master.xlsx same happens in Temp.xlsx every Friday.

    The condition should be if the Name is not present in Temp.xlsx then add/delete Name from Master.xlsx

    https://drive.google.com/file/d/1-Yx451CZfkQgRDj-NXDzX__WCAAOpJZ8/view?usp=sharing

    Please refer above image and guide me on what should be the condition and dynamic formula?

    1+
    1. Pavel Bludov

      Hi Jay,
      Sorry for my late reply.
      Did you figure it out? If not, please reply to my email I am about to send to you with more details.
      Thanks!

      0
  7. MIchael

    Thanks, this helped

    0
    1. Pavel Bludov

      Thanks Michael!

      0

Leave a Reply