For Each NOT iterating every item

[Anonymous]

I'm building a flow that takes file names from a folder and breaks them down into the same data points that are present on an Excel spreadsheet I'm going to forward them into.

My flow works from one end to the other - however, the issue is that my For Each loop isn't iterating every item.

I've targeted a folder with 28 items in it. It is supposed to iterate 28 loops of the process, however, it only fires 3 times. The first iteration is the always the first item, it doesn't seem to matter if I change WHAT this first file is, and the last 2 files in the bottom of the list. It will skip over almost the entire list, even if it displays the list of files as longer than 3.

Any ideas what's causing this?

***EDIT:

I put together a counter to track how many iterations of the For-Each loop my Flow has processed already. It looks like this:

Total Files = Total Files - Total Files

Total Files = (0) + 1

At the end of the For-Each loop I show how many iterations so far:

Then, when all the processing is done and the last file has been iterated, I show how many iterations vs how many files...

If Iterations = Total Files then the For-Each worked as intended. Which it did. 29 files, looped 29 times.

The issue I was experiencing before this edit was more a behavior of the For-Each loop itself and I was misinterpreting what that behavior was.

The files I'm iterating are sorted alphabetically, and in their destination worksheet they're also sorted alphabetically. In my all-knowing monkey-brain I thought the behavior from the For-Each loop would replicate this alphabetical sort same as the files are listed. Instead, For-Each would randomly select the next file to iterate, going from alphabetical A to M then Z, then back to C. No idea why. The alphabetical sort of the data isn't crucial for my Flow to work right, but it would be nice to watch the Flow go from sorted file 1 to sorted file 2, rather than 1 to... any other number, then eventually back to 2.

For-Each WAS iterating each item, just wildly out of order.

[Highboy]

Could you share a bit of you code? Would make it easier to help.😊

[VJR]

Hi @Anonymous 

If able to please screenshots of the variable that the "For each" is iterating through and the code that does the iteration.

[Anonymous]

The next 52 steps are all involved in the processing of the current item. The flow ends with the "End" tag for the For Each loop.

One of the last things the Flow does is open an Excel workbook, attach to the running workbook, pass vars via a VBA macro, and then close the attached-to workbook.

[Anonymous]

The rest of the flow's operation happens inside the For Each loop confines (between the "start" For Each loop tag and the "End" For Each loop tag).

[MichaelAnnis]

Is it possible that at some point you accidentally replace a variable, such as %Files%?

If you still can't find the answer, then I recommend changing to a Loop:

Loop 0 to %Files.Count - 1% increment 1

Do your processes:  You will have to change all %CurrentItem% references to %Files[LoopIndex]%

[Anonymous]

I've run through a few similar issues in VBA Excel where I have a long list of items to iterate through piecemeal. I've only ever had to count the contents of the list once, and that was likely do to poor For-Each exception handling. I haven't poked around the internet yet, but do you know if PAD offers any "count" functions as drag-and-drop functions? (The same way you drag and drop other process functions like a For Each loop or Parse Text).

I had come to work today thinking about this issue and the method you described is very similar to the solution I had come up with. In my attempt to apply the solution I found that I am lacking some critical underlying knowledge about PAD, I think you've succinctly highlighted my knowledge gap with the ".count" method modifying the "%Files%" var.

Please hold my hand just one moment longer - 

To apply your solution, I need to navigate to my Flow operation where I reference "%CurrentItem%" and replace the var with "%Files.Count - 1%" - as well as replacing my For-Each loop with a Loop?

[MichaelAnnis · Accepted solution]

Right.

So, previously, when you tried to use "For Each %CurrentItem% in %Files%",

%CurrentItem% was simply %Files[0]%, then %Files[1]%, then %Files[2]% and so on.  All lists start with index 0 as the first item. In this case, we are saying Loop starting at 0 to %Files.Count - 1% where the LoopIndex is going to be the number inside the brackets and Files.Count-1 is the last index number we need. Ex. 28 files, 27 is the last index, because the first one starts at 0.

As for Files.Count, all variables have properties.  You can see all datatype properties here:

https://docs.microsoft.com/en-us/power-automate/desktop-flows/datatype-properties

I actually keep that link saved on my desktop.  You can also see properties and even select them by opening up a variable when building out a PAD command.  Ex:

This is actually better, because one time I tried to do an email property "Email.From" and it was wrong, but 99% of the time, it is just %Variable.Property% syntax.

For your last sentence above, you need to replace %CurrentItem% with %Files[LoopIndex]%.  Remember, %LoopIndex% is what loop you are on, starting at 0.  %Files.Count - 1% is the %LoopIndex% limit that you put in the "End to" field of the Loop command; see below:

[Anonymous]

Delightful.

Thank you very much, @MichaelAnnis. I'll give this a try and see if this remedies my issue.

[Anonymous]

I realize this isn't exactly the solution you suggested but its something I was curious to try.

I noticed that my For-Each loop was skipping over a (random) large swathe of my test files to iterate. While frustrating over the specifics of my Flow to make it work, I realized I could simply cause it to count how many times it iterates the files vs how many files it SHOULD have iterated.

File count var: <%Files.Count%>
For Each count var <%FileCount%>

Behold, the tiny bit of code PAD needed to show me this behavior. With a small sample of files, (5), my For-Each iterates all five files correctly... more testing coming...

Jeez I feel kinda... errr... not the most pleasant words.

Using the above vars in a message box I discovered that the problem outlined in the OP is actually a fabrication of my imagination LMFAO.

PAD HAD been iterating each file, or at least, is now iterating each file. However I think what originally threw me for a loop was the fact that PAD didn't iterate the files ALPHABETICALLY. PAD elected the file with index number 0, which happens to be the first file sorted alphabetically, then elected the next item, index 1, as some random file non alphabetically sorted and so on. I couldn't tell you the exact sort order, index 1 - 29, but I can tell you for sure it did not go A, B, C, D, E...

It's not exactly a crucial behavior for my Flow to iterate alphabetically, but if it were, do you have any idea how I might go about forcing PAD to iterate alphabetically?

Show me what's been counted so farEstablish what to count, and when to start the countShow me files iterated vs total file count

[MichaelAnnis · Accepted solution]

You can sort a list based on any properties you can arise from the values in the list, so a %Files% list can be sorted alphabetically, or by date last modified, etc.

[Anonymous]

@MichaelAnnis I'd be horribly lost and without either paddles without your help!!

Thank you very much! You've opened my eyes in such a wonderfully short, sweet, and to-the-point way. I cannot thank you enough!