Exercise - Use Power Automate flows to extract survey response data

Completed

Contoso Retail also wants to integrate the survey response data in the Microsoft Excel file with external applications such as ERP, Inventory Management Systems, or other retail applications.

Personas and scenario

Alex, the district manager, creates a plan template with a defined set of activities for the store associate to perform.

Monica, the store manager for Contoso Stores 101, is assigned with tasks for the store. Then, Monica assigns the completion of these tasks to store associates of respective sections of the store.

Riley is a store associate who works at Contoso Retail Store 101. Riley completes the survey and marks the store opening activity as complete. After Riley answers all questions of the survey checklist, the system stores the answers to each question as JSON in the Survey responses entity.

Reed, the Microsoft Power Platform administrator for Contoso Retail, develops a low-code solution by using a Power Automate flow to parse the survey responses for the Store Opening Checklist survey. Reed then uses the flow to write the responses to an Excel file in a SharePoint site. Also, if the store associate encounters issues in the point-of-sale application, the Power Automate flow triggers an automatic email to the IT help desk of Contoso Retail so that someone can create a case for faster resolution.

In this exercise, you assume the role of Reed and perform the following tasks.

Task: Import the sample solution for Power Automate flows

This task is an example of the customization and extension capabilities of the Microsoft Cloud for Retail - Store operations solution. It outlines the steps that are required for creating a custom solution that includes a Power Automate flow triggered by a store associate's response to a survey list of questions.

In this task, you act as Reed and import a sample Microsoft Power Platform solution called Contosoflows_1_0_0_4.zip. You use this solution to extract the survey responses and write them to Excel files.

Note

The Power Automate flows are provided for ease of understanding and reusing them to complete the exercise. However, in an ideal customer’s environment, you need to develop the Power Automate flows based on specific customer requirements.

Follow these steps for importing the solution:

1. Sign is as Reed in Power Apps in an InPrivate or Incognito browser session.

2. In the upper-right corner of the screen, select the correct environment that you used so far for this exercise.

   

3. Select Import solution to import the solution to the environment.

   

4. Browse and select the solution that you downloaded.

   

5. Select Next.

   

6. In the Import a solution screen, ensure that the Enable Plugin steps and flows included in the solution option is selected and then select Next.

   

7. In the next screen, you need to establish the connections that you use in the flows before you import. If the connections aren't created automatically, select New connection to create a connection of the respective type of connector, such as the Microsoft Excel, Outlook, and Dataverse types. Select Create and then sign in with Reed Smith's credentials for this exercise. Check that the connections are successful for all the connections for the Outlook and Dataverse connectors.

   

8. After you create and set all connections, select Import to import the solution.

9. Select the solution to observe the flows and connections that are imported.

   

Task: Extract survey responses

In the custom solution, the survey responses and the product audit responses are automatically written to an Excel file stored in a Microsoft Teams site. You can use this flexibility to integrate the responses with an external application, if necessary. Additionally, if issues are reported, an email is automatically triggered to the correct department for appropriate action.

In this task, you act as Reed and review the Contoso survey responses. You modify the flow to write the results of the survey responses to the ContosoSurveyReponses.xlsx file uploaded in your SharePoint site.

1. Sign is as Reed Smith in Power Apps in an InPrivate or Incognito browser session.

2. In the upper-right corner of the screen, select the correct environment that you've been using in this exercise.

   

3. Select the Contosoflows_1_0_0_4.zip solution and then select Edit to open the solution.

   

4. Select to open the Contoso survey responses flow.

   

5. The Power Automate flows are displayed as shown. Select Edit to review and modify the flow.

   

6. Perform the following steps to modify the flow.

   

   • Rename the trigger When a visit activity for Store opening is completed to When a survey for store opening is completed connector.

   • Select the table name as Tasks and set the scope to Organization.

   • Contoso Retail's requirement is to only have the survey response for the Store Opening Checklist plan integrated into external applications. Hence, in the Filter rows field, filter the task name (subject) as Store Opening Checklist and the state code as Completed (subject eq 'Store Opening Checklist' and statecode eq 1).

   Note

   The filter that's set for the task name, Store Opening Checklist, is case sensitive and exactly matches the task name that's used in the implementation plan. If you've used a different name for the task in your plan, modify it accordingly.

7. Complete the Get survey definition and response of visit action, which is the next step in the process. This step retrieves the Survey response record based on the survey response ID returned from the connector. You also use this action to retrieve the survey definition and survey response records.

   

   • The Table name is Survey response

   • The Row ID is the Value of Survey response

   • The Expand Query is set to return the survey definition and survey response records to use further in the flow.

     owningbusinessunit($select=name),owninguser($select=fullname)

     You can use this code to retrieve the username and the business unit of the user who's submitting the response.

8. The flow has a set of steps to initialize variables. Your next step is to use Parse JSON to retrieve responses from the JSON response.

   

9. Use the Apply to each answer connector to extract the responses and append to an array. In the Store Opening Checklist survey definition used in this exercise, the ninth question in the survey is of type task; therefore, you'll only extract and store the first eight responses in an array.

   

10. Add the Condition check for POS issues connector to identify if branching questions exist. You can use this condition to check if more than six questions exist in the array of answers because six were direct questions in the Store Opening Checklist survey. If the answer to the Are the POS hardware and software working properly question is No, then you need two more branching questions, such as Issue description and Select any additional user to whom this issue needs to be reported, as shown in the following image.

    

    In the Yes branch of the condition to check POS issues, a couple of conditions are added to retrieve the issue description and the user to notify for the issues.

    

11. Further down is the Apply to each response connector, which has the Add a row into a table connector for you to write the responses to the Excel file. You need to modify the location of the file based on your environment by following the steps as shown in the following images.

    

    

    Note

    Integration to an Excel file is only an example to illustrate the method for extracting the JSON format responses. In a real-life customer integration scenario, the implementation might vary. You could integrate the data by writing the data to a Microsoft Azure Blob or data lake storage or to a file system that's used as a middleware, or you can integrate it by using API calls to an external application.

12. Enter the details in the connector as follows.

    • Location - Replace the SharePoint site

      (This location is the SharePoint site that you set up as part of the Exercise - Set up prerequisites unit in the Task: Create and upload sample files for integration requirements section.)

    • Document Library - Remove the contents and select Documents again

    • File - Remove and select Outbound/ContosoSurveyResponses.xlsx (select from the Outbound folder of the SharePoint site)

    • Table - Remove and select CheckListData

      (After you enter the table name, all questions set up as columns in the Excel file will appear in the connector, as shown.)

    • Task owner - Owning user full name

    • Store - Owning business unit

    • Are the POS hardware and software working? - Review if the expression is set to Variables('Updatedresponsesarray')[0] because it's the first element in the array of responses

    • Issue description - Review if the expression is set to Variables('issuedescription')

    • Cash in register - Review if the expression is set as Variables('Updatedresponsesarray')[1]

    • Date in calendar - Review if the expression is set to formatDateTime(Variables('Updatedresponsesarray')[2],'MM-dd-yyyy', 'en-US')

    • Cleanliness rating - Review if the expression is set to Variables('Updatedresponsesarray') [3]

    • Is store layout compliant? - Review if expression is set to Variables('Updatedresponsesarray') [5]

13. Review the Outlook email connector to send an email.

    

14. Modify the Send an email (V2) connector to send an email to the IT help desk email account. (You can enter the email account that you're using for this exercise.)

    • To - (your email); usertonotify

      Enter your email address, followed by a semicolon, and then select the usertonotify variable.

    • Subject - Hardware issue in store “Owning business unit name.” (Select the owning business unit name from dynamic content.)

    • Body - Enter the following message:

      Hi Team,

      User:

      '@{outputs('Get_survey_definition_and_response_of_visit')?['body/owninguser/fullname']}' from Store '@{outputs('Get_survey_definition_and_response_of_visit')?['body/owningbusinessunit/name']}' has identified issues with point-of-sale devices.

      Additional Details on the issue: ''@{variables('issuedescription')}''

    

15. Save the flow. Verify if issues are reported as errors and then resolve them. Now, the flow is ready to run.

16. Go back to the list of flows using the back arrow.

    

17. Select the flow and select Turn on.

    

18. After the flow is run successfully, the responses are written to the ContosoSurveyReponses.xlsx Excel file in your SharePoint site Outbound folder, as shown in the following screenshot.

    

The flow is automatically triggered when a record is modified in Visit activities with the task name of Store Opening Checklist and the status is set to completed.

The responses are updated to the ContosoSurveyResponses.xlsx Excel file in your SharePoint site. You can use the captured responses in a file for external reporting or integration.

Congratulations, you learned how to use a Power Automate flow for the customization and extension capabilities of the Store operations solution. You can further enhance Power Automate to gather store associates' responses and integrate with a ticketing or case management application to automate ticket creation for prompt resolution.