A quick one, to get you started:
- use a FOR EACH loop to build a list of available files. The names probably contain some ID that can be connected to the shortlist.
- Perform an INNER JOIN of newly-created file list with shortlist. The resulting set will be the ones that need to be moved
- Loop through the result list and perform an EXECUTE for each entry.
Generally, I am doing it in exactly that way. Only I was once again imagining the thing too complicated - I was trying to come up with a way to do like a lookup in that table within every single iteration of the loop, but now I see I don't even have to do this - it is quite certain that these 4.500 item_numbers exist in that list of files, but I can still check it first - then I just have to build a cmd command for the EXECUTE and that will pick the right file all by itself.
Thanks a lot!
I think there aren't many alternatives to your approach and other tools and/or methods won't be faster so that a stable solution is more important then speed and I believe the biggest bottleneck will be your network and how fast could it copy/move/rename your files. This meant you might need sleep-statements and/or checking-routines that your execute-statement will be executed successfully (you will get soon error-messages if you already starts the hundreds execute-statement if windows is yet storing the tenth file).
To come to your check file from filelist to your table-itemNumber beside the lookup might fieldvalue a faster alternatively, maybe in this way:
fieldvalue('item_number', fieldindex(FileName, '$(File)'))