Chris Munro

Use an MS form to create a Dataverse record and store documents in SharePoint

Scenario: Nordic Knit-Off – Public Entry Form

You’re helping run a ‘Nordic Knit-Off‘—a community competition celebrating the most beloved and bizarre Nordic jumpers. The team wants to collect entries via Microsoft Forms and store them in Dataverse. Each user in the community already has an account in the community M365 tenant. Each participant submits:

  • Their name and email
  • A short story about their jumper
  • A photo of the jumper (uploaded to the form)

The goal is to have the form automatically create a new Dataverse record in a ‘JumperEntries’ table. Each record stores the metadata, with a link to the photo stored in SharePoint.

1. Create the MS form

Create an MS form with at least these questions so the team can collect the entries

  • Name
  • Short story about the jumper
  • Photo upload

NB best practice is to get an MS team to own the form – so it is not just owned by one person. However if you change the owner it will change the ID of the form so you will need to bear that in mind. Once you change the owner the form will get a new ID and you will likely need to access the form in Power Automate by pasting in the ID manually – it may no longer automatically show.

Since we ask for document uploads the form can only be completed by users with accounts in the M365 tenant. As they are logged in we also don’t need to ask for their email address as we get it stored automatically when they submit the form.

2. Create a Power Automate Flow triggered by the form submission

Next we are going to create a Power Automate Flow that creates the Dataverse record and links the contact/puts the photo in SharePoint storage. We use a ‘When a new response is submitted’ trigger, and ‘Get response details’ to get the response submission and then ‘Add new row’ in Dataverse to create the Jumper Entry row, and initialize a variable for the contact record Id.

3. Create or find the contact

We are going to use the email address from the ‘responders email’ from the MS form submission to either create a contact if there is not one already, or create one.

For the true branch – a contact exists then we set the contact variable to the first element in the list rows:

first(outputs('List_contacts')?['body/value'])?['contactid']

For the no branch – no contact exists, we set the variable to the newly created contact Id:

4. Create the Dataverse record

First we need to create the JumperEntries table with the following:

  • Lookup to a contact record – the person entering the competition
  • SharePoint document integration enabled for the table and set up
  • Columns for
    • Name (text)
    • Jumper story (multiple lines of text)
    • MS form submission Id (text)
  • A form for the table
  • Views for the table
  • Add all of these to an app so the team can use them

We are putting a timestamp on the end of the name and stripping out the / characters so that we can use it in SharePoint.

5.Test the MS forms to Dataverse part

If we submit the form now, we should now get a Jumper Entry linked to a contact, with the columns populated and the MS form submission Id set to 1 as it’s the first submission.

In the next post we will store the photo document in SharePoint.

6. Add photo to SharePoint

Next we need to add some actions to allow us to get the photo from the MS forms submission and store it against the Dataverse record in SharePoint document storage. We need to

  1. Get the Document Location in Dataverse for the Jumper Entries table,
  2. Create a Document Location for the Dataverse Jumper Entry row we are going to add,
  3. Get the photo from the MS form submission and use a Create File action from SharePoint to link the file to Dataverse.

Create a new Scope: Put Photo into SharePoint document storage.

Let’s get the document location for the Jumper Entries table via a List Rows for Document Locations. The filter rows statement will need to be different for you depending on what logical/schema name you used for the table.

Set the variable for the filename in SharePoint – we are doing it to match what happens in Dataverse automatically – file name is: {Primary column}_{Dataverse record ID}. We are stripping the hyphens from the Id

Next we create a new SharePoint folder

Next we create the Document Location for the Dataverse record. We can use a compose with a first statement to pick off the sharepointdocumentlocationid.

Next create the Document Location for the Dataverse row we are adding

The regarding may be slightly different depending on your table naming, but for me it is be

table plural name(jumper record ID)

new_jumperentries(outputs('Add_Dataverse_Jumper_Entry_row)?['body/new_jumperentryid']

To get the photo from the MS forms response we need a parse JSON action, and the content argument is the MS form photo upload question response.

There’s a blog here about how to save MS form attachments to SharePoint which you can use for the above bit: https://plumsail.com/blog/save-microsoft-forms-attachments-to-sharepoint

There could be multiple attachments so we iterate over them with an apply to each to get each attachment and create a file for it in SharePoint:

Finally create the file in SharePoint

If you submit the form and upload a photo it should now all get created, for a real scenario you would also want to wrap this in some error handling too.

admin