cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarcusD1
Helper II
Helper II

Routing files into Sharepoint document library subfolders from Form submission

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?

2 ACCEPTED SOLUTIONS

Accepted Solutions
ekarim2020
Super User
Super User

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:

Snag_3d9b54e0.png

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

Snag_3d9bfe30.png

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:

Snag_3dad0a04.png

 

This is the flow:

Snag_3dada172.png

 

This is the mappings table:

Snag_3dadfcff.png

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

Snag_3dbbc1a8.png

 

This is how I entered the expression: 

2022-01-09_22-48-11.gif

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

Snag_3dc15e4d.png

Here is the sample runtime output: 

Snag_3dd62fd5.png

Snag_3dcf6d0e.png

Snag_3dd30383.png

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.

View solution in original post

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

Snag_1e714cf.png

The three expressions are:

 

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

 

Snag_1ee6dab.png

Snag_1ec6db4.png

Snag_1f1b93c.png

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.

View solution in original post

9 REPLIES 9
DamoBird365
Microsoft
Microsoft

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
Super User
Super User

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:

Snag_3d9b54e0.png

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

Snag_3d9bfe30.png

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:

Snag_3dad0a04.png

 

This is the flow:

Snag_3dada172.png

 

This is the mappings table:

Snag_3dadfcff.png

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

Snag_3dbbc1a8.png

 

This is how I entered the expression: 

2022-01-09_22-48-11.gif

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

Snag_3dc15e4d.png

Here is the sample runtime output: 

Snag_3dd62fd5.png

Snag_3dcf6d0e.png

Snag_3dd30383.png

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
Helper II
Helper II

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
Helper II
Helper II

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...

MarcusD1_1-1642125626547.png

 

but I'm getting this error:

MarcusD1_0-1642125585291.png

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

 

 

 

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
Helper II
Helper II

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'),'_')),'.')))

 

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

Snag_1e714cf.png

The three expressions are:

 

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

 

Snag_1ee6dab.png

Snag_1ec6db4.png

Snag_1f1b93c.png

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.

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?

You could combine all the expressions into a single expressions:

ekarim2020_0-1642281291722.png

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:

ekarim2020_1-1642281473864.png

Snag_7ba586b.png

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

 

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

 

Ellis

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Users online (1,833)