Hello,
Today I will be explaining how to build a real life email-driven support desk application using a SharePoint site and a few of our components.
The system will be able to:
- Receive support calls sent to an 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
There are several advantages to the system that 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.
- The support calls are generated without having to log into the system, so they can be easily received from outside of the organization.
- The support call can be broken down into multiple tasks, each one of them can be tracked individually.
- Support calls cannot be closed without customer's consent.
- Information can only be modified when allowed by item status and user identity.
- WSS 3.0 or SharePoint Foundation 4.0 are required, which are free
Here is an example of the system in action:
Required Components
We will need the following Infowise components:
- Smart List Pro - adds tabs, tab permissions and custom validations (amongst other capabilities)
- Smart Alert Pro - replaces the built-in alert mechanism to allow sending custom alert to any recipient, and only subject to specific conditions
- Smart Import Pro - allows importing emails and DB items into SharePoint lists and document libraries
- Associated Tasks Field - adds the ability to create and manage relationships between different lists.
- Color Field - adds color-coding, icons, KPIs, progress bars and countdowns
You can download 30-day trial versions, which are fully functional.
Please install all products first, for the ones requiring feature activation on specific site collections, make sure the site collection where you intend to create the application is selected (it will be by default).
Creating Site and Lists
Let's begin 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 - Associated Tasks list. If you don't see this list type,make sure Associated Tasks site collection feature is activated
Tasks list will not require adjustments (although it is advisable to remove unnecessary columns from the default view, such as % Completed)
Enable versionning in the Support Calls list and let's now add the columns:
- 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)
- 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
Product Configuration
Now we can configure additional products.
Smart List Pro
First, let's create tabs, tab permissions and validation rules using Smart List Pro.
- Go into List Settings of Support Calls
- Click on Tabs and tab permissions
- Add a new tab named General
- Assign the following columns to the tab
- Title
- Description
- Importance
- Submitted by
- Attachments
- Add the following permissions 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
- Add a new tab named Resolution. It will hold the handling and resolutions steps
- Assign the following columns to the tab
- Status
- Steps
- Tasks
- Add the following permissions to the tab
- Deny 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.
- Assign the following columns to the tab
- Customer Approval
- Approval Submitter
- Approval Submitted
- Add the following permissions 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.
Now let's add a validation rule that will make it impossible to close support calls without customer's approval:
- Go to List Settings for Support Calls
- Click on Column Validation Settings
- 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.
Smart Alert Pro
We use Smart Alert Pro, our replacement for the built-in alert mechanism, to send 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.
- In the ribbon of Support Calls list (or the Actions menu when using SharePoint 2007) click on Add Alert (do not confuse with the built-in Alert Me)
- 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 to To
- 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 Mail Template section
- Name the name template "Approval Request"
- Enter the following into Subject: Approve resolution of '[Title|Title]' - ID:[ID|ID]
- Enter the following (or similar) into Body:
Hello,Our support team marked your service call '[Title|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 - The template will create an email with the subject containing the title and the ID of the support call and body containing instructions.
- Save the template and select it for the alert by using the radio buttons (for now all radio buttons should be set to Default template, choose the new template for Modified event).
- Save the alert, you should receive a confirmation email.
Smart Import Pro
Now it's time email enable our list.
- Go to List Settings for Support Calls
- Click on Import Settings
- Start creating new import 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. POP3, Exchange 2003 and down or Exchange 2007 and up are supported
- 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)
- 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.
- Save the profile.
Testing
Send an email to the service email account and see it converted into a support call (it could take up to 15 minutes). Handle the support call by repeatedly adding and completing tasks and modifying the steps. When done, 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 (you should wait up to 15 minutes for the import job to run). Now you can close the support call.
When testing the application, you can run the Infowise Smart Import Pro scanner job manually through Central Administration timer jobs management page, instead of waiting 15 minutes for it to run. It is possible on SharePoint 2010 only.
We are done, we've just created a functional help desk system in minutes without any custom development!