Wait for files in folder query to start subflows

[kylebunkin]

Hi guys,

Continuation of earlier problem... struggling to get this to work, think I've melted my brain so need some explanation on how to get this to work:

1. Wait for files in folder - a csv file, the name will change but it will always be csv
2. When files are seen, iterate through them and run sub flow to read them, take the variables and run a API call, followed by a SQL query 
3. When the API call and SQL query are complete, rename the file and move to folder

I've got this working perfectly so far, but this basically relies on the file name being the same every time and there's a chance that isn't going to happen, so I need to hedge my bets:

This works fine assuming the file name is always the same, but I don't want to run in to an issue where potentially DriverCard(1).csv (2) etc. appear and the flow not run. 

I assume having the loop would cause some issue to the current flow, for example sake the SQL query looks like this:

I'm guessing the loop would mean it needs to start by looking at a variable? 

My brain has melted at this stage so any help appreciated. 

[MichaelAnnis]

Loop While 1=1

    Get Files from Folder for filter *.csv to %Files%

    Loop LoopIndex from 0 to %Files.Count - 1% increment of 1 'this replace your loop 1 to 999999999 above

        'do everything for said file...you can refer to the current file as %Files[LoopIndex]%; for example %Files[0]% is the first file in %Files%

    End (Loop)

End(Loop)

In English, this will loop through all the csv files in the folder to do the above actions, then it will continuously loop through Get Files from Folder attempting to find any other CSV files and will not proceed until another exists.  It doesn't matter what the names are, because it will always pull every CSV file into %Files%.

Good luck!

[kylebunkin]

Hi Michael,

Got this working in a slightly different flow for redirecting printing (just to try the principle out) - I found that if a file is produced every second it works fine, however if two files were to appear at the exact same time, on line 6 I get the error: "Import failed. Error while parsing" so my assumption is something is not right with how my Files indexing is working. 

Full example below:

In theory everything is working correctly and the likelihood of two files appearing at the exact same instance is extremely slim, but Murphy's law says this will happen as soon as I use this flow. 

[MichaelAnnis]

Get rid of one of your loops.  You do not need both Loop 0 to %Files.Count - 1% and For Each %CurrentItem% in %Files%.  As it is written, it will fail on the second run, because it is running the second file on the second loop, but the first loop is still on the first file, so when it tries to ready "Files[LoopIndex]", this is the first file that you have already processed and moved, and now it's gone (doesn't exist) in the previous location defined by Files[LoopIndex].

These two types of loops serve the same purpose and you should pick one or the other.  If you choose to Loop, then %Files[LoopIndex]% is how you refer to the current file.  If you choose to use a For Each, then you refer to current file as %CurrentItem%.

Here is an article comparing the pros and cons of the two:

https://www.linkedin.com/pulse/power-automate-desktop-pad-best-practices-part-3-loop-michael-annis

[kylebunkin]

Hi Michael,

I've tried removing both of the loops but still getting the issue where it fails on Line 6 if two files appear at the same time - I'm assuming this is not how you meant it? 

I've disabled LoopIndex from 0 in this method and only used the For Each, though I have also tried it in the reverse of disabling ForEach and keeping LoopIndex and both fail at the same point. 

Thanks!

[kylebunkin]

Is it potentially related to this? Files moves all of the files if they appear at the same time - should there be some way to iterate it? My brain has turned to mush at this point. 

Have given you a follow on LinkedIn, excellent resources you've provided on there! 

[MichaelAnnis · Accepted solution]

Exactly. You just want to move each one as it’s processed, so don’t move %Files%. Move %CurrentItem% instead.  Good job finding that one.  

[kylebunkin]

That's perfect, I've just changed the flow to that (I honestly couldn't even think what the expression should be but in hindsight that's very obvious) and everything is working perfectly now. I definitely need to practice those loops in other workflows but that is spot on, thank you!