Survey variables can be used in a Forms Pro survey to personalise forms, but also to pass information back and forth from the invitation to the response. When using these variables, they can be seen in a field named ‘Context Data’ in a survey response. If you have a license for Power Apps and can create a model-driven app that’s awesome! What if you don’t, and are trying to update an Excel file or a SharePoint list? Let’s walk through the steps of extracting survey variables when you don’t have access to a model-driven Power App.
First, in our survey, we will add in all of the variables we need. For this example, we’ll send out a survey when a case is closed and add the responses to an excel file in OneDrive, so we want to pass through the Case Number, Case Title, Case Owner and the Case Owner First name. Add these in from the ellipsis (three dots) on the top right of your survey. The name and the default can be the same. The default is used when the value for your variable cannot be found.
Next, add the variables on to your survey. The variables can be added into the survey description, questions and question descriptions. You CANNOT pass variables in as answers to your questions.
For this, I will add all responses to Excel, so I need to create my spreadsheet, and include a table. Make sure you indicate that the table has headers when you create it. Upload the file to either SharePoint or OneDrive, somewhere it can be accessed online.
Now we need to move to Power Automate and create two different flows. The first one can trigger whenever you need it to. For this example, it will trigger when a case is closed in the Common Data Service (CDS). All of the actions aren’t really that important for you to see how this works, it’s the Create an invitation or Send a survey actions that are important. These are both found in the Forms Pro connector. Below we have the Create an invitation action. This step is where we will populate the variables. Pick the survey to use, then populate the Email field with the email from your Contact record linked to the case. If you are working with SharePoint, make sure you have an email address somewhere in a field on your list. then pull in the First name and Last name. The Regarding and Recipient details do not need to be populated if you are not using CDS. Instead, you can leave those blank. If you are using CDS, type out the name of the entity to link the invite too, then a comma, then use the record ID value. Add the values for each of your variables. Finally, if needed, add a step to add your invitation URL too. Check out this post if you haven’t done it before.
Now when the email is received, and the respondent clicks on the link, the survey will be populated with values where you added the survey variables.
Now we turn our attention to what happens when the response is received. First, start your flow off with a trigger from the CDS connector when a Survey response is received. You can add in a second step to get the survey the response is linked to, and then use a condition to check and make sure it’s the survey you want to take action on.
In your get response details, use the following expression in the Response id field. This converts the id to an integer, which is needed in this situation.
Now, this next part is ONLY needed if you want to get the variables out of the response (the ones we passed through the invitation), and you don’t have access to CDS, which is where the data is stored. Add in a Compose step after the Get response details step.
Search for Context Data from your trigger, and add that into the inputs for the Compose step. We are adding this for a one time need only. Add this in, then go and fill out your survey with data. Come back to your flow, and you should see it has run with success. Click on the successful run to view it.
This is the part we need. Copy the value from the inputs or outputs and just paste it into Notepad for now.
Now let’s carry on and finish editing the flow. You can leave your Compose step in the flow, just in case you make more changes to your survey in the future. No harm in it being there. If you have access to CDS, open up your survey response and access the Context Data from the main form. Next, add a parse JSON step. Add the Context Data to the Content value. Then click on the generate from sample at the bottom of the action step.
Paste in the value you copied and pasted earlier. Then click Done. This will get all of the variables that you passed through from your survey invitation.
In this example, we will add the survey responses to an excel file. Search for an select the action to add a row into a table from the Excel connector. Select the location you added the file, then find the document library and the file. You should have your table available in the table field. Now we start populating the fields you added to the table. Here you can see the Parse JSON step shows all of the variables added to the survey. Start populating your fields.
The table also has cells for the survey responses that will show after the variables. So you can add those in from the get response details step.
When a new response comes in for the survey, the second flow will run. Below we can see in blue there are values from the Survey for the name, and Survey Response for the date submitted. Orange shows values that are survey variables, and green shows values that are responses to the questions.
Want to just watch how to do this? Check out the video: https://youtu.be/NbLWx7wkYkg