Very basic overview, we get a lot of correspondence in from a client. Rather than having our office manager manually rename the attachments that come in via email with complex file names, I would like to (try) and automate it with Flow (or something similar) against our correspondence log.
For example, we might get a response back to a document that was called "Submittal No. 24 Rev 2" and the file will come back as an attachment (pdf) from the client named "Submittal No. 24 Rev2 Response.pdf' or something similar.
What I ultimately want the filename to be would look something like this:
SUB_0024_002 - Name of the Submittal - Spec ## - Return Date - Status.pdf
What would be easy for the OM to do when emails come in is rename their "Submittal No. 24 Rev2 Response.pdf' file to something like "SUB_0024_002.pdf" and save it in a folder (dropbox or onedrive or whatever). What I would then like the flow to do is see that a new file has been created, look at the filename (that the OM did), and lookup that filename against a log we have in excel. The columns in the log would be the submittal number, name of submittal, spec ##, returned date, status, and some other columns, with that submittal number as the lead column.
If doing this manually, I could use a lookup against the first column with the newly created filename to see what the full file name would be. I could then have a cheater column that concatenated all the other columns to generate a filename for me that looks like the above. Thus "SUB_0024_002.pdf" would lookup the row with "SUB_0024_002" in column 1 and then return the cheater column filename of "SUB_0024_002 - Name of the Submittal - Spec ## - Return Date - Status.pdf" that draws on all the other columns.
What I cannot figure out, and maybe it can't be done, is how to lookup rows in an excel table by anything other than the PowerAppsID, which is a unique random string and not part of any consistent numbering system that we are using. Can this be done?
I would like the flow to see a new file in the folder, grab its file name, reference the filename against the log in excel, find that row (it will always be unique but in a structured sense), return the new filename from a vlookup of the row, and then rename the file to that name (or create a new copy of it elsewhere).
