Reporting form i can start adding the questions associated with my forum i have different column types to choose from i'll start with the title of the incident i'll select text i will make this a required field i'll add a second question the description of the incident so i'll select text i'll enable long answer and not make this a required field next i'll add a choice column this will be the category of the incident.
I can add my options right here i've added four options i'll add another option for choice this will be the office location where this incident occurred i've added my three office locations here i'll add the incident date and finally i would also like to provide an option for the user to upload files so i'll pick file upload since i'm creating a standard microsoft form and since i want the user to upload.
Files a new folder will be created in my onedrive since i'm the creator of this microsoft form and the files uploaded by the responders of this form will be captured in the specific folder i'll say okay i'll call this photos here i can decide how many files can the users upload let's say i limit it to three i can also define the file size limits for those individual files i'll make this a required field.
I also have the ability to go to more settings and decide which file types the user can upload in my scenario i'll restrict the user to only upload files of type image this now completes my incident reporting form i can click on preview forms are responsive this is the form on the desktop experience and this is how the form would look like on the mobile app experience you can define a theme.
Now all of these responses that are being recorded including those files are being stored in the user's onedrive the user who has created the form in my onedrive i have a folder created called apps in there i have microsoft forms this will list out all my forms here is the incident reporting form that i created those images that i uploaded are right here they are being loaded in my onedrive.
I also have the ability to open and see the responses of this form in an excel file and these are all the results that are captured now our scenario here is every time a new response is submitted i would like to grab the details of the response and i would like to store them inside a data source in this case the data source i will select is sharepoint to store the responses i will create a.
New list i'll create a blank list i will call this incident reports i will add columns in sharepoint so i can store the data that is coming in from microsoft forms i have the title column where i can store the incident title i'll create a multi-line of text column i will call it description to store the description of the incident i'll add a date column called incident date incident category and office location.
These were choice options inside microsoft forms i can do something similar in sharepoint by creating a choice column i've added my four choices for the category and i've added my three choice options for location the photos that are coming in through the file upload control these i will add them as attachments to the sharepoint list item itself and i will create one additional column here type single line of text.
I am calling this submit a email i will store the email address of the user that is filling out that microsoft form now that i have my list created my next step is to automate the process of saving the microsoft form response and those attachments directly to this sharepoint list for automation i will head over to power automate head over to templates.
Posts Related:
And search for microsoft forms there are templates that are pre-created
For us that we can leverage one of them is record form responses in sharepoint that's exactly my use case so i will select this template i will click continue and this creates the flow based on the template the first step is to connect to your form this will list out all your microsoft forms.I have my incident reporting form listed i will go ahead and select this this is the trigger action of the flow so when a new response comes in for the incident reporting form then this loops through all the responses that are received now it could be possible that there are multiple users responding at the same time so this flow will take all those requests and it will loop through each of those requests it then grabs the details of the response that the user has provided and.
Once again we have to provide the id of our form pick my incident reporting form and then the next step is to start creating the item in sharepoint here i will pick my sharepoint site in case your site is not listed you can head over to enter custom value and plug in the url of your sharepoint site pick the list from my sharepoint site my list is incident reports it will load all the columns in my sharepoint list this is where i need to start mapping my.
Microsoft form controls or questions to my sharepoint list columns so sharepoint column title from dynamic content the microsoft forms action called get response details will give me all the details of the response that the user provided i will pick incident title for description i will pick the incident description incident date currently it shows me one option here called submission time that's when the user submitted the.
Response i can head over to see more and pick other options i had a column for incident date so i'll select that category value is a choice column in sharepoint and these were the four options i provided these are the exact same options that are available in microsoft form so to pick the value coming in from microsoft form head over to enter custom value and then select the column associated with the category in my case incident category.
And i will do exactly the same thing for my location choice column enter custom value and pick my microsoft form column office location submitter's email that information is also provided under dynamic content it's called responders email i can rename my flow and i can click save the flow is now saved and the flow is ready to go from this point onwards any new response that is submitted in.
Microsoft forms the flow will trigger and create an item with the details of the form in sharepoint however i would also like to upload the files that the responder has uploaded in my question called photos these files are being stored in my onedrive right after create an item i will add an action called get file content i will pick the get file content action.
From the onedrive for business connector here i need to provide the identity of the file if you look at dynamic content i have the photos column listed right here and there could be multiple files so this returns an array of information a collection of files plus it returns the response in string format so we need to convert this into an actual array that power automate.
Understands and then we need to loop through each and every file start getting the content of that file from onedrive and then add it as an attachment to the item that we just created to do that right before get file content i will add an action and i will use the apply to each control this creates a loop i will rename this to apply to each attachment and to get that array of data.
I will head over to expression and use the json function
Which expects a parameter of type string for that i will head over to dynamic content and select the photos property from the microsoft form action get response details i will click ok now inside this loop i need to go and grab the files from onedrive so the get file content action i will drag it into this loop and here i need to provide the unique.Identifier to that file and that is available in this json array itself and to get that head over to expression item question mark under square brackets and single quotes there's a property called id click ok this will grab the content of the file i need to add it as an attachment to my created sharepoint list item i will add an action add attachment from the sharepoint.
Connector once again connect to my sharepoint site connect to my sharepoint list i need the id of the list item where i would like to attach this file that i can directly get from the id property of the create item action so i'll select this the name of the file the json output has the name of the file as well once again expression item off the property is called.
Name i'll click ok and to get the content of the file that we've already fetched from onedrive so i can go and select the file content property this now completes my flow i will select save i will submit this form i'll upload the files in this case pictures and i will submit the response the moment the response is received power automate has triggered.
For that one response it's gone ahead and grabbed the details of the response if i head over to show raw outputs this is where i can see the response details who the responder was when did they submit the response and all the other pieces of information the attachments are the files those are right here we can see how it comes in string format but it is an array that has a lot of details around the file the name of the file the link to the file.
The id of the file that's what i leveraged to grab the content of the file from onedrive we then created the item in sharepoint and then for every attachment i went ahead and grabbed the content of the file from my onedrive and then started uploading them as list item attachments to the incident list item that got created and if we head over to sharepoint here is an entry that has come in automatically from power automator if i select this i can see all the.
Details and here are those files that have come in as attachments now there can also be scenarios wherein you have multiple file upload controls in your microsoft form let's add another one i'll pick file upload this one i'll call additional documents are limited to two and i will not make this a required field now my requirement is that all of these files that are being uploaded whether.
They are photos or whether they are these additional documents i would like to add them to that same sharepoint list item as attachments to deal with the new file upload question that i added to my microsoft form right after the trigger action i will add an action here i will initialize a variable i will call this var files this will be of type array.
Right after getting the response details i will add an action set variable and we will set that files variable to i can go to expression json off my question which is photos which has that mandatory file upload control i'll click ok now for the additional documents since they are not mandatory i will have to add a condition.
To check to see if it is not null the value would be an expression json this time dynamic content i will pick my additional documents question click ok this is not equal to here i have to ensure i go to expression and enter null so if this is not equal to null i would like to add the files that have been.
Uploaded in this additional documents question i would like to append it to my variable that i created called var files and to do that i will have to loop through all of those files so i'll add and apply to each loop here the expression json off the question additional documents and inside this loop i will use the append to add a variable action and to that variable called var files.
I will append the current item and to do that i'll head over to expression and use the function item i can straight away use this variable in the apply to each attachment loop so i will remove this expression of mine and replace this with the variable where files which has all the file information all the steps after that are the same i do not have to make any changes i'll go ahead and save my flow.
Let's try and submit another incident i've uploaded one photo and i will add some sample files i've uploaded a pdf i'll click submit and here is the new entry that has come in to my sharepoint list if i select this i have the attachments that are coming in from both those attachment controls that's my image file and that's my pdf.
Document that i uploaded now there are also going to be scenarios wherein you want to add those files to a sharepoint document library so let's create a document library i will call it incident files incident reports that are being created in my sharepoint list each list or library in sharepoint has a column called id it's the internal unique identity of that record in sharepoint.
Since i am planning to add my files in a sharepoint document library i would like to somehow relate the list with the library and i will relate it using the id so in my document library i will add a column of type number and i will call this incident id my files that are coming in could either be photos or additional documents if i would like to even categorize them in the sharepoint document library i could do that.
I'll add a column here of type choice i will call this category and i have gone ahead and defined a couple of categories photos and additional documents now in power automate to add those files to my document library i will add another action here called create file i'll pick my sharepoint site and then for the folder path i'll select.
On the show picker option and select my document library which is incident files the name of the file would be the expression item name and the content of the file for dynamic content i can search for file content and pick the file content that is coming from my onedrive action one thing to note in a document library you cannot have multiple files with the.
Same name so it could be possible that many users are using that incident reporting form so they could potentially upload documents with the same name so to ensure that they are unique where i have the file name added right before that i can use an expression for the date and time i can use the expression utc now or i can use the expression function good for creating a random good right before the file name.
Once the file is created i would want to update the properties of the file because i want to add the incident id and the category associated with it to do that i will add an action update file properties connect to my sharepoint site my incident files library the id of the file that i would like to update will come from the create file action dynamic content property item id.
The incident id will be the id of the list item that i created so from the create item action i will pick id and the category value will be dependent upon whether the attachment or the file has come from my photos question or my additional documents question now to know from which question it has come from i will go to enter custom value and plug in the following expression if.
Equals the equals condition first parameter is item of reference id if the reference id of the item equals to if you look at any previous flow run and the get response detail action and head over to show raw outputs those files that you upload per question here are those two file upload options these files have a specific property.
Called reference id and these reference ids are unique for those specific questions that i added so for photos this is my reference id so i will copy this i will paste that reference id right here so my result would be photos else my result would be additional documents i will click on ok to plug in that expression and i will go ahead and save my flow.
I'll report another incident i've uploaded an image and a pdf document i'll click submit the response is received and once that flow runs here is the new entry that has come in to my sharepoint list the id here is five and if i look at the entries that have come in my sharepoint document library here are those two files they have the incident id mapped so i know which incident this relates to and i also have.
The categories depending upon which question in the form they belong to now one neat thing you could do in sharepoint to kind of relate the library and the list item together is use something known as list formatting so as an example i'll add a column here single line of text i will call it view files and save and for this column i'll go to column settings and format this column go to advanced mode.
I will share this json code the link will be in the video description i'm just going to paste it right here and all you have to do in this json code is replace the url here with the url of your document library so for example here's my document library so i will copy the link to my document library head back to the column formatting and paste my link right here and click save.
Now the advantage here is this creates a link to those documents based on the id of the list item so if i'm only interested in looking at documents related to this specific entry which is oil spill if i click on view documents it will redirect me to my document library and actually apply a filter on that specific incident which is the incident id5 let's submit another response here i've uploaded one image.
And two additional documents and i'll submit my response as soon as the result comes in id is seven if i select view documents it will redirect me to my document library and filter out based on id seven and here is that photo that i uploaded and these are those two additional documents one more scenario is to upload it into a document library that has folders.
I want all of those file uploads to go into these specific folders in my attachments loop i will add an action create file in sharepoint pick my sharepoint site the folder path this is where i want to make it dynamic depending upon where the file is uploaded to photos and additional documents those are the questions i will click on the show picker my library is called incident docs.
I will click on show contents and let's say i pick a folder called photos so you can see how it generates the path to it incident docs slash photos but what i need here is to dynamically add that path and that is something we already did for the category value when we were checking to see the attachment was related to which question so i'll do something very similar i will select in here go to expression.
