Routing files into Sharepoint document library subfolders from Form submission

[MarcusD1]

I'm trying to build a Flow that takes attachments submitted from a Microsoft Form and saves them to specific folders based on a menu choice in the form. Specifically, the form has one question, and two upload questions. The question is State (drop down menu), and then an upload field for one type of file ("primary"), and a second upload field for a second type of file ("secondary"). There's plenty of tutorials / answers on how to place an attachment in a SP list or document library, but I can't find one that describes how to use a Conditional filter to place a file, and/or deal with two (or more) attachment upload fields.

The process I'm aiming for is:

1. Respondent identifies the State/Territory from the down menu.

2. Uploads a single file to the "primary" attachment field.

3. Upload one or more (likely more) files to the "secondary" attachment field.

4. Upon submit, the Flow routes the attachments to the correct State folder in my SP doc library, AND marks a metadata column in the document library as either "primary" or "secondary" based on which attachment upload field was used for the corresponding document.

I can get as far as how to send an attachment to a specific folder, and I know I could use a conditional filter to route files to specific state folders, but that would require 50+ unique filters, and that seems unnecessarily complicated/unwieldy. Is there an easier way to accomplish this?

Secondly, conceptionally I think I can use Update File properties to add the metadata ("primary" or "secondary") - but I'm struggling to figure out how to structure that given the two distinct file upload fields in the same form.

Has anyone done this or something similar?

[DamoBird365]

Hi @MarcusD1 

I did something like this based on an email subject here https://www.damobird365.com/amazing-saving-an-attachment-to-a-custom-folder-or-site/

You can use an array based on the post albeit I would now suggest an even simpler method, an object in a compose.

{

“answer1”:”/path/to/folder1”,

“answer2”:”/path/to/folder2”,

“answer3”:”/path/to/folder3”

}

If the above is stored in a compose, you can query it as output(‘compose’)?[‘’answer2’] where the answer 2 is the expression for the dynamic value of your forms answer.  Make your answers on the form match the key names of the object and this will work dynamically without the need for a condition. You can then use that in the path for create file.

Hope this makes sense. I can put together a proof of concept if not. Please just give me some sample answers and paths.

Please consider accepting my answer as a solution if it helps to solve your problem.

Cheers
Damien

Please take a look and subscribe to my YouTube Channel for more Power Platform ideas and concepts, or take a look at my website. Thanks

[ekarim2020 · Accepted solution]

There are a few options for with dealing multiple conditions and filters. One option is to create a mapping between the US state name and folder name -  using a JSON object:

In the following example, the US states and the state specific folder names are stored in a variable:

To get the state specific folder name we need to use an expression. The expression will reference the varUSStates variable along with a state name. For example, the expression variables('varUSStates')?['Alabama'] returns the value AL. And variables('varUSStates')?['North Carolina'] returns the value NC. 

variables('varUSStates')?['Alabama'] ---> AL
variables('varUSStates')?['Alaska']  ---> AK
variables('varUSStates')?['Arizona'] ---> AZ
variables('varUSStates')?['North Carolina'] ---> NC
variables('varUSStates')?['Texas'] ---> TX

Here is a very basic demo using a MS Form and a few US states:

This is the flow:

This is the mappings table:

Now we get the state specific folder name - using the value that was submitted via MS Forms:

This is how I entered the expression: 

 

Finally, add a Create File action. Be careful when entering the Folder Path:

Here is the sample runtime output: 

 

This is the JSON object, which I took from: https://gist.github.com/mshafrir/2646763

{
"Alabama": "AL",
"Alaska": "AK",
"American Samoa": "AS",
"Arizona": "AZ",
"Arkansas": "AR",
"California": "CA",
"Colorado": "CO",
"Connecticut": "CT",
"Delaware": "DE",
"District Of Columbia": "DC",
"Federated States Of Micronesia": "FM",
"Florida": "FL",
"Georgia": "GA",
"Guam": "GU",
"Hawaii": "HI",
"Idaho": "ID",
"Illinois": "IL",
"Indiana": "IN",
"Iowa": "IA",
"Kansas": "KS",
"Kentucky": "KY",
"Louisiana": "LA",
"Maine": "ME",
"Marshall Islands": "MH",
"Maryland": "MD",
"Massachusetts": "MA",
"Michigan": "MI",
"Minnesota": "MN",
"Mississippi": "MS",
"Missouri": "MO",
"Montana": "MT",
"Nebraska": "NE",
"Nevada": "NV",
"New Hampshire": "NH",
"New Jersey": "NJ",
"New Mexico": "NM",
"New York": "NY",
"North Carolina": "NC",
"North Dakota": "ND",
"Northern Mariana Islands": "MP",
"Ohio": "OH",
"Oklahoma": "OK",
"Oregon": "OR",
"Palau": "PW",
"Pennsylvania": "PA",
"Puerto Rico": "PR",
"Rhode Island": "RI",
"South Carolina": "SC",
"South Dakota": "SD",
"Tennessee": "TN",
"Texas": "TX",
"Utah": "UT",
"Vermont": "VT",
"Virgin Islands": "VI",
"Virginia": "VA",
"Washington": "WA",
"West Virginia": "WV",
"Wisconsin": "WI",
"Wyoming": "WY"
}

Hope this helps.

Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

[MarcusD1]

Damien and Ellis - thank you both your replies! Ellis - your detailed instructions were VERY helpful and very much appreciated. I got the general flow put together using those, but got distracted with something before I could finish it up completely. I'll come back and share once it's fully successful (hopefully by the end of this week).

[MarcusD1]

I got this set up and the routing to a particular folder is working great. But I have one thing I still can't figure out, which is how to strip the submitter's name from the uploaded file.

For the first issue, I found this post, and I tried using a Compose to grab the filename_submitter and then the expression provided in the post...

but I'm getting this error:

Not sure how to fix (or if I even have these operators in the right place). Any suggestions?

[ekarim2020]

What does a typical filename name look like? The referenced post uses an expression for a specific filename format  that may not match your requirements.

Can you also please the expression in the stripName action.

Ellis

[MarcusD1]

The desired filename taxonomy for a submitter to use is "[Location Name]_[State]_[File Content]", however that's impossible to enforce and I expect filenames to be whatever the submitter had chosen.

Since the submitter's name is being automatically added, using the desired format it would look like "Location Name_ST_File Content_FirstName LastName". But it could just as easily look like "Whatever I Decided (2022.1.13)_FirstName LastName".

As for the expression in stripName - I used the same expression as in the referenced post because I thought it striped whatever came after the final underscore, but my understanding of how the expression works might be wrong?  concat(first(split(outputs('name'),'_')),'.',Last(split(last(split(outputs('name'),'_')),'.')))

[ekarim2020 · Accepted solution]

Try the following which I've broken down into steps:

The three expressions are:

(1) last(split(outputs('ComposeFileName'),'.'))
(2) last(split(outputs('ComposeFileName'),'_'))
(3) first(split(outputs('ComposeFileName'),concat('_',outputs('ComposeSplit'))))

Once you have the expressions working, you can merge some of the steps.

These are some of the results from my tests:

Old: [Location Name]_[State]_[File Content]_Bill Gates.docx
New: [Location Name]_[State]_[File Content].docx
    
Old: Location Name_ST_File Content_FirstName LastName.pdf
New: Location Name_ST_File Content.pdf
	
Old: Whatever I Decided (2022.1.13)_FirstName LastName.xlsx
New: Whatever I Decided (2022.1.13).xlsx

Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

[MarcusD1]

Ellis that worked beautifully. I'm okay leaving the steps separate since it makes it super clear what is happening in each step (which is great for someone like me still learning how to use expressions). But if I did want to consolidate... where might that be done?

[ekarim2020]

You could combine all the expressions into a single expressions:

concat(first(split(outputs('ComposeFileName'),concat('_', last(split(outputs('ComposeFileName'),'_')) ))),'.',last(split(outputs('ComposeFileName'),'.')))

Or use two expressions: one for the new file name, and one expression for the file extension:

first(split(outputs('ComposeFileName'),concat('_', last(split(outputs('ComposeFileName'),'_')) )))

But it becomes much harder to read and understand what is going on.

Ellis