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

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

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

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

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

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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (1,871)