Answered

Move file to subfolder which currently contain the file with same number by Power Automate Desktop

(0) Share

Report

Posted on by KloeNg89

Hi All,

I am trying to create the flow to move invoice files to different subfolder in which contain the current files with the same contract number by Power Automate Desktop.

Example:

File to move: Invoice PO00011

Path to folder to move this particular Invoice: Order/CUSABC/2022-2023/USA

General Folder: Order (containing numbers of customer subfolders)

Customer subfolder: CUSABC (containing several countries)

Country subfolder: USA (containing different year period)

Year period subfolder: 2022-2023

In year period subfolder, there were different Sales Contracts and Packing Lists, in this example case, the relevant contract and packing list are named as PO00011_SC_CUSABC.pdf and PO00011_PL_CUSABC.pdf respectively.

I read through all the available solutions, but all seems stop at moving files to folder based on file name. However, we dont create folder and name it for each contract and are manually saving invoice to relevant folder.

Just wonder if there is possible way to work around to use Power Automate Desktop for this case?

Thanks a lot.

Categories:

Power Automate Desktop

I have the same question (0)

All responses (7)

Answers (1)

Sort by

momlo 1,527 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @KloeNg89
I'm not sure I full follow what you are trying to achieve, although most likely it is doable with PAD.
I understand that you have files such as below:
PO00011_SC_CUSABC.pdf
PO00011_PL_CUSABC.pdf
Files follow naming convention: <po number>_<country>_<customer subfolder>.pdf

So you need to:
Get *.pdf files from folder
Use For Each loop through Files list
In the loop:
parse file name to obtain the individual strings - there are different ways of doing that, such as split text: Use "_" as the delimiter - this will give you the list of strings, lets call it %ListOfFolders%
Then move the file to:
%GeneralFolder%\%ListOfFOlders[2]\%ListOfFOlders[1]\%DatePeriod%
%DatePeriod% - you need to set the variable as you wish, for example: get the current date, then set %DatePeriod% = %VariableWithCurrentDate.Year%-%VariableWithCurrentDate.Year+1%
I wrote without testing in PAD, but you should get an idea.

Share your code here (copy actions from PAD to here) if you still have issues - just replace the sensitive data where needed

Was this reply helpful? Yes No
Verified answer

MichaelAnnis 5,727 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Begin the flow with:

Set variable %FromPath% to the folder where the files are located that you need to file

Set variable %ToPath% to the parent folder (above CUSABC)

Copy and paste this into your flow:

Folder.GetFiles Folder: FromPath FileFilter: $'''*''' IncludeSubfolders: False FailOnAccessDenied: True SortBy1: Folder.SortBy.NoSort SortDescending1: False SortBy2: Folder.SortBy.NoSort SortDescending2: False SortBy3: Folder.SortBy.NoSort SortDescending3: False Files=> Files LOOP FOREACH CurrentItem IN Files Text.GetSubtext.GetSubtextFrom Text: CurrentItem.NameWithoutExtension CharacterPosition: CurrentItem.NameWithoutExtension.Length - 7 Subtext=> Subtext Folder.GetFiles Folder: ToPath FileFilter: $'''%Subtext%*''' IncludeSubfolders: True FailOnAccessDenied: True SortBy1: Folder.SortBy.NoSort SortDescending1: False SortBy2: Folder.SortBy.NoSort SortDescending2: False SortBy3: Folder.SortBy.NoSort SortDescending3: False Files=> Files2 File.Move Files: CurrentItem Destination: Files2[0].Directory IfFileExists: File.IfExists.Overwrite MovedFiles=> MovedFiles END

In English, it does the following:

Get all Invoice from the %FromPath%

For each invoice:

Get the 7 characters (%Subtext%) from the right of the filename.
Use those 7 letters to search for all files with the 7 letters at the beginning of the filename (%Subtext%*).

File the invoice in the path of the first file that is found

End (for each)

This will be faster than any looping. I had thought about using Excel, but then realized how clean this solution was. It will be insanely fast.

Good luck!

Was this reply helpful? Yes No
momlo 1,527 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @MichaelAnnis You still use one for each loop, and if i got the OP right with his needs your approach would create two issues:
- files are in different "year period" folders, so your approach could put the invoice in wrong folder, for example in 2000-2001 instead of 2022-2033, of course OP could expand your solution to put in the right place
- it will fail in the case of very first invoice for a new customer as there will be no invoices returned from the search, again - something that can accounted for and be built in to manage

Was this reply helpful? Yes No
MichaelAnnis 5,727 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

I assumed that there is only two PO00011* files in the system. One is SC and the other is PL. I assume 00011 is a transaction ID, and that the transaction ID is not being reused for any other Customer\Year\Country\Transaction combination.

The "for each" is only on the list of documents to be filed (which has to be done regardless of the approach), not another loop to find the right folder. A second loop to find the right folder means it is running a loop for every document in the first loop.

In this case, using Get Files (the second time) to find the 2 existing files that are already filed, will give you the information you need to know where those files are located. The only way this files in the wrong place is if PO00011* files exist in two different folders, which shouldn't happen because the transaction should fit to one combination of Customer\Year\Country.

Was this reply helpful? Yes No
momlo 1,527 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Yeah.. I assumed POxxx file is a PO with changing number.
If it is fixed number then get you. I would still just grab the folders from the filename as all data is there already (except year wich we can get just once outside of the loop) and that would save on the second "get files".
This is why I like PAD in other RPA tools - that the problem can get solved in virtually endless ways 😄

Was this reply helpful? Yes No
KloeNg89 7 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @momlo and @MichaelAnnis

Thank you both for the fantastic suggestions. I have learnt a lot from both solutions.

As the contract number is unique in my case, I have applied the one of Michael and achieved the desired flow.

It is interesting to see how versatile of this tool that we can solve the repetitively manual works.

Appreciate the great support from our community!!!

Was this reply helpful? Yes No
KloeNg89 7 on at

Like
a
(0)

Report
Copy link

Link copied!

Yeah, the flow is really smooth. Thanks a lot. Would be interesting to know how to use Excel in this task as well!!

Was this reply helpful? Yes No