Creating email-driven business process in SharePoint
Introduction
With the help of Ultimate Forms it's easy to create any type of business process in SharePoint. Unlike some other tools, we designed Ultimate Forms to provide value not just to seasoned developers. As a fully no-code solution, even novices can quickly build advanced solutions with little to no training.
Ultimate Forms requires no deployment steps. You can modify your configuration at any time on a live system, thereby allowing for continuous improvements. This helps to increase customer satisfactions.
Ultimate Forms works the same way both in SharePoint Online (Microsoft 365) and on premises.
Today, I will explain how to create a real email-driven support desk app. We will use a SharePoint site and Ultimate Forms.
The system will be able to:
- Receive support calls sent to a designated email address
- Manage these support calls by tracking status, resolutions steps and associated tasks
- Mark support calls as resolved and automatically request customer approval via email
- Closing the support call by replying to an email
Several advantages to the system go beyond the limitation of a plain vanilla SharePoint site:
- We are email-enabling a custom list, so we are free to use any columns. That greatly exceeds the capabilities of SharePoint email integration on premises.
- You can make support calls without logging into the system. This makes it easy to receive them from outside the organization.
- You can break down the support call into multiple tasks, and you can track each one individually.
- Support agents cannot close calls without the customer's consent.
- Users can only modify information when item status and their identity allow it.
Required Components
We will need the following Infowise Ultimate Forms components:
- Forms - adds tabs, tab permissions and custom validations (amongst other capabilities)
- Alerts - an upgrade to built-in SharePoint alerts. Allow us to send to external recipients and also use conditions.
- Import - allows importing emails and DB items into SharePoint lists and document libraries. We will be using it both for creating new items and to implement email-based approvals.
- Associated Items column - adds the ability to create and manage relationships between different lists
- Color Choice column - adds color-coding and icons
- Indicator column - adds KPIs, progress bars and countdowns
You can download a 30-day trial version of Ultimate Forms, which is fully functional. Use the Try Now Free button located in the top right corner of any page on our site. Our user-friendly installation wizard will get it installed and configured in just moments.
Creating Site and Lists
We will begin our process by creating a blank site and call it Service Center.
Next create two lists:
- Support Calls - custom list that will contain the support calls
- Tasks - Tasks list. You can also create a custom list and simply add common task list columns, such as Status, Due Date and Assigned To.
Now let's add the columns for Support Calls list:
- Description (multiple lines of text) - will hold the problem description as submitted by the customer
- Importance (single line of text) - will hold the Importance settings of the incoming email
- Submitted by (single line of text) - will hold the email sender name
- Email (single line of text) - will hold the email sender address
- Status (Infowise Color Choice) - will hold the current status of the support call. You can set it do differentiate the values by color or by icon. The possible values are:
- New (red, default) - the call was just received
- In process (yellow) - the call is being handled
- Resolved (blue) - the support team resolved the issue and asks for the customer approval to close the support call
- Closed (green) - the call is closed following customer approval
- Steps (multiple lines of text in Append mode) - document resolution steps in this column
- Tasks (Infowise Associated Items) - manage associated tasks, so you can create and track multiple related tasks for each support call. Just use the default settings when adding the column (make sure Tasks list already exists and is of Associated Tasks type). This component allows us to track resolution steps right within the support ticket.
- Customer Approval (yes/no, default no) - will hold customer's approval to close the support call
- Approval Submitter (single line of text) - will hold the approving customer's name
- Approval Submitted (date and time) - will hold the date and time of the approval
Business Process Configuration
Now we can configure Ultimate Forms components.
Form settings
First, let's create tabs, tab permissions and validation rules using Form Designer.
- Click on Design button on the toolbar of the list
- Click on Form Designer
- Drag and drop Tabs control
- Rename the first tab General
- Drag the following columns onto the tab
- Title
- Description
- Importance
- Submitted by
- Attachments
- Add the following permission rules to the tab:
- Write mode in New form - allow data entry for new support calls (when they do not originate in emails)
- Read mode in Edit/View forms - do not allow anyone to change the columns values when submitted
- Rename the second tab Resolution. It will hold the handling and resolutions steps.
- Drag the following columns to the tab
- Status
- Steps
- Tasks
- Add the following permission rules to the tab
- Deny (hidden) mode in New form - we don't want any hanlding when adding a new support call
- Read mode in Edit/View forms - read/only mode for closed support calls
- Write mode in Edit/View forms when Status is not equal Closed - allow editing while the support call is open. Optionally specify user/groups who are allowed to update the handling steps
- Add a new tab named Closure. It will hold the customer's approval to close the support call.
- Drag the following columns to the tab
- Customer Approval
- Approval Submitter
- Approval Submitted
- Add the following permission rules to the tab
- Deny mode in New form
- Read mode in View/Edit forms - because we will be getting approvals via email, we never need to edit these columns manually
We are done creating the tabs, now the information can only be editing when it is allowed.
For column Status add a Column validation rule "not equals" "Closed" with error message "Support call cannot be closed until confirmed by the customer" with the condition "Customer Approval" equals "No". That would mean that when Customer Approval value is No, Status column cannot contain the value Closed. That will prevent users from closing support calls until it is confirmed by the customer.
Alerts settings
We use the Alerts component of Ultimate Forms. It replaces the built-in alert mechanism and sends out notifications to customers when support calls are marked as Resolved. By replying yes/no to this messages the customers can approve closing the support calls.
- Click on Design on the toolbar of Support Calls, then on Alerts.
- Click on Add new alert.
- Open up the Recipients section and remove your name from the To recipients.
- Add column Email to the To recipients by selecting it and clicking on Add.
- Open up the What to send section
- Uncheck Item is added and Item is deleted checkboxes, only leaving Item is modified
- Add condition "Status" "After change" "Equals" "Resolved". That will send the alert only when the support call's status is changed to Resolved
- Open up the Email Content section
- Enter the following into Subject: Approve resolution of '[Title]' - ID:[ID]
- Enter the following (or similar) into Body:
Hello,
Our support team marked your service call '[Title]' as Resolved. Please reply with yes if you confirm that the issue has been resolved, otherwise reply with no.
This is not a monitored email account, please only reply with "yes" or "no".
Thank you,
Support Team
Import settings
With the Import component we are enabling retrieval of emails. Wirth the data coming from emails we can both create new SharePoint list items and update existing items.
- Click on Design on the toolbar of Support Calls, then on Import
- Click on Add new profile
- Name it Email (the actual name does not matter)
- Leave the polling interval at 15 minutes (the default minimal value, 0 means every hour)
- Select the mail server protocol. For SharePoint Online, leave it at Microsoft 365. On premises, choose Exchange 2007 or POP3, depending on what email server you are using. For on-prem customers:
- Enter the address of the mail server (such as mail.example.com or mailserver)
- Enter the user name and password for authentication, note that they are stored securely.
- If needed change the default port and SSL settings
- Choose to delete the emails from server (optional)
- If on premises, click Connect
- Now let's add two actions, one for creating new support calls and one for updating approval value in existing ones
- Create a new Create list item/document action
- Call it Create support call
- In Values to set fill in the following pairs:
- Title - [Subject]
- Description - [Body]
- Importance - [Importance]
- Submitted by - [Sender Name]
- Email - [Sender Address]
- In conditions add "Subject" "Not contains" "ID:". This will prevent importing emails containing "ID:" in the subject line, which are basically the replies to our approval requests.
- Save the action
- Create a new Update list item action
- Call it Confirm closure
- In Values to set fill in the following pairs:
- Customer Approval - $Extract([Body]|^) - that will extract the first word from the email body. For more details on the Extract function, please refer to the user guide
- Approval Submitted - [Date Sent]
- Approval Submitter - [Sender Name]
- In Item selection conditions add the following:
- ID equals $Extract([Subject]|?ID:^) - that will extract the text immediately following "ID:" from the subject line, which will be the ID of the support call item, as included in the alert.
- Status equals Resolved - only update the items requiring an update
- In conditions add "Subject" "Contains" "ID:". This will only run the actions on emails, which are replies to the alert.
- Save the action on the Import profile.
- Save the Import profile itself.
Testing
Send an email to the service email account. Now see how the Import component converts into a support call. This process may take as long as 15 minutes to complete.
Handle the support call by repeatedly adding and completing tasks and modifying the steps. When you have finished, change the status to Resolved.
You will receive an email from the system requiring your approval to close the support call. Reply yes and watch the support call being confirmed for closing. In this case as well, you should wait up to 15 minutes for the import job to run. You will now be able to close the support call.
Tip: When you test the application, you can run the Import profile manually. Go to the Import management page in Ultimate Forms. This saves you the time needed to wait for the timer to trigger the next import task.
We have finished creating a functional automated support desk system in minutes without any custom development!