You’re offline. This is a read only version of the page.
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).
Kris,
MAJOR help and thank you very much, that takes me like 95% of the way there. I had not considered (or used before) get rows and thus did not know about the array filter, I was trying to use get row (singular).
My main issue I still see is that it appears Flow cannot reference tables that have formulas in them (which IMO is kind of insane since the whole point of flow would be to reference likely dynamic data, but that's another topic).
I think you've got the gist of what I am trying to do because that solution totally nailed it. Our office manager saves a new file in a specific DB folder and it is formatted "SUB_####_###.pdf", the two numbers being Submittal Number and Revision Number. The flow grabs this new file and its file name, looks against the excel sheet and references column A for the matching existing file name, and then creates a copy of that file in a nw folder with the new file name in column B.
The issue I am having is the data I want my users touching is in columns C, D, E, F, G, and H, and columns A and B are concatentations of those other columns (or rather the =C3&" - "&D3&" - " etc etc format, not sure what that is called). Bottom line is column A and B are dynamically referenced off columns C-H by formulas and Flow doesn't like that. The easy workaround is that I have the users manually create A and B, but that somewhat defeats the purpose of this excercise.
The flow above is very similar to what you posted, and it is referencing the newly created file named by the OM against the column A data - but doesn't like when it is a formula. Can the formula be entered into the left side of that flow using concat() to tie everything together as an array filter in the data set (since every line will in some way be unique by sub or rev number)?
The one other issue I have is the steps after the array filter - creating the new file (and emailing myself that it has been done). The issue I have is that as you can see, I currently have 3 entries in the excel form. If I can implement this, the list will have hundreds and hundreds of entries. Right now that flow action is doing an "apply to each", so referencing every row I pulled and not just the filtered row, so when I create a new file that matches the first row in my table (row 3) or the second or the third, it does properly create the new file with the new name in the new folder (provided I take out the formulas and put in manually to column A and B), but it ALSO creates new files with the new file names from all of the lines in the table but using the content of the single file that was created with the reference name, if that makes sense? This process creates 3 files with new, unique file names in the new folder, but each has the same content as the single file I wanted to process. Same goes for the email, I get one email per line in the excel table even though I have only uploaded and processed one matching file. Is there a way to turn off this "apply to each" and get it to only handle the single file and old/new file name?
EIther way, major help. This got me way closer than I was. Also wondering if there is a better place to store this data than an excel table that one of our many users could conceivably screw up? Sharepoint list (we don't use sharepoint for anything but we are a construction company so our tech adoption is...poor), google sheet, access table, etc?
Thanks again
Hi @Speez,
Could you please show a bit more about your Excel file?
If you want to look up rows with the newly created filename within your Excel file, you should firstly extract the name of submittal (may be first three Characters which are need to be capital, e.g. SUB), submittal number, Rev number from the newly created filename.
If you want to extract text value from the filename, I think the substring() function could achieve your needs. Please check and see if the following article would help in your scenario:
If you want to concatenate multiple strings within Microsoft Flow, I think the concat() function could achieve your needs. More details about the concat() function, please check the following article:
I assume that you have extracted the corresponding text values from the filename and concatenated them, e.g. SUB_0024_002. If you want to filter your Excel table based on the SUB_0024_002 string and find the corresponding row, please take a try with the following workaround:
Please check and see if the following article would help in your scenario:
Best regards,
Kris
#1
WarrenBelz
146,658
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
65,999
Most Valuable Professional
Share
Report
All responses (
Answers (