In this post, I will share how I built a LowCode NoCode customer service request management suite using the PowerPlatform. We will take a look at building a bot using Power Virtual Agents (PVA) for customer interaction, flows in Power Automate to invoke as actions on the bot and interact with data in SharePoint, canvas apps in Power Apps for internal employees to view and manage their customer request tickets and finally a Power BI report to view the metrics.
Let's Get Started!
So how the process works is, a user (customer) logs on to the customer management portal (.NET based web app hosted in Azure). Here, we have the Power Virtual Agent running. The user chats with the bot and the bot can perform actions like creating a new request ticket and tracking a previously created ticket. These tickets are stored on a SharePoint List and the data here is pulled into canvas apps for customer service agents to view and work on the tickets. The metrics on number of tickets created, solved, pending etc. can be viewed on a Power BI report. Let's start building the solution piece by piece.
SharePoint List Schema
Here, we will define the list schema to store the customer request details and information from the customer service agents working on those tickets. The SharePoint List has the following schema:
Power Automate
There are two process that we will look into here: (as an extension of one of my previous articles: (MS Flow as a Service)
Process 1: The customer interacts with the chat bot to create a new request.
Process 2: The customer interacts with the chat bot to get the status update on a previously created request.
Note: These Flows have to be created in a solution in the same environment as the Virtual Agent.
Both the flows will be created with the "When an HTTP request is received trigger" and correspond to the schema/ process as shown below.
Flow 1 (Create a new request)
The JSON schema in the trigger has 3 keys: name, email and comments that will be received from the user and used to create the item in SharePoint.
The "Response" action at the end has a key "ResponseVal" that will be sent as an output to the virtual agent.
Flow 2 (Get request status)
The JSON schema has the key "trackingNumber" that will be requested from the user and then further this will be used to retrieve the item from the SharePoint list.
The "Response" action at the end has a key "ResponseVa" that will be sent as an output to the virtual agent.
Power Virtual Agents
Now let's look at building the Power Virtual Agent. This is based on one of my previous posts here.
This agent allows the user to select from 4 options:
Enter 1 to create a new request
Enter 2 to get status of a previous request
Enter 3 to end chat
Enter 4 to connect with an agent
For Condition Branch 1: To create a new request:
This path, invokes the first flow created in Power Automate to create a new customer request item on the SharePoint List.
For Condition Branch 2: To get status of existing request:
This path, invokes the second flow created in Power Automate to get the status of the customer request from the SharePoint List.
For Condition Branch 3: To end chat:
Step a: Add a node as "End Chat with Feedback" to end this branch.
For Condition Branch 4: To connect to an agent:
Step a: Add a node as "Transfer to Agent" to end this branch. (Here you can add more custom set of responses or provide any live support as applicable.)
Setup in action
Power Apps
We will create two apps here. The first one is designed for admins and allows the admin to access all the customer requests created in SharePoint. The second one, is for individual customer service agents and this allows the agents to view and add information on the tickets assigned to them.
Master App
This is a direct app created using the SharePoint List as a data-source. The master app is accessed by an administrator who can access, view and edit all the customer requests.
1. Screen 1: This is the home screen of the app that has all the list items on a gallery control.
2. Screen 2: This is the detail form screen where the details of the item selected in the first screen are shown.
3. Screen 3: This is the edit form screen where the administrator can make edits to a particular item and the same changes are then reflected on the SharePoint list.
Agent App
This app consists of a single screen that shows the items that have been assigned to the customer service agent currently logged in to the app. The agent can provide comments, add attachments, re-assign the ticket and change the status of the ticket using this app.
Screen 1
1. This is a Gallery control that displays items that are assigned to the current logged in user. Expression used on the 'Items' property of this control:
SortByColumns(Filter([@'Request Details'], 'Assigned to'.Email = LoggedInUser.Email && StartsWith(Title, TextSearchBox1.Text)), "Title", If(SortDescending1, Descending, Ascending))
2. This is an edit form that displays information on the columns for the item selected in the above gallery. The user can edit only the 'Current Status', 'Assigned to', 'Attachments', and 'Agent Comments' fields on this form.
Power BI
The Power BI report is created using the SharePoint list as a data source. This report has three charts configured that are primarily based on the 'Created On' column of the list. To read the value on the Created On field as a day value, a new column needs to be created and the value needs to be formatted accordingly. The expression used on the new column to format the date value is:
Column = DATE(YEAR('Request Details'[Created]),MONTH('Request Details'[Created]),DAY('Request Details'[Created]))
Once this data is loaded, we will use it on the chart and map the other data columns accordingly.
1. Chart1: This is a 'Line Chart' where the 'Values' is set to 'Count of Created' and the 'Axis' is set to the 'Column' (that we created using the expression above) with 'Day' option selected.
2. Chart2: This is 'Stacked Column' type chart where the 'Values' is set to 'Count of CurrentStatus' and the 'Axis' is set to 'CurrentStatus'.
3. Chart3: This is a 'Stacked Column' type chart where the 'Values' is set to 'Count of CurrentStatus' with values filtered on 'Completed' and the 'Axis' is set to the 'Column' (that we created using the expression above) with 'Day' option selected.
In this post we saw how we can leverage the PowerPlatform to build LowCode NoCode business solutions and implement them with ease. The interconnecting capabilities of the platform makes each and every tool a powerful component in building enterprise applications, tracking productivity and streamlining complex processes.
I hope you found this interesting and it helped you!