Additional configuration CRM Service for SQL Server

Introduction

Below the optional configuration you can do to the CRM Service for SQL Server.

PluginAttributes

This list contains the items which are shown to the user in the WebAgent and ID’s used to identity unique entities. All items are grouped by the Category.

The PluginAttributes list contains the following columns:

Column	Meaning
DisplayName	Name of the property shown in the WebAgent.
SystemName	Must be the Sql database column name.
ShowOnExtensionWindow	Indicate that the property is visible in the WebAgent. The main reasons to set this to ‘No’ and still configure it as an item is when the property should not be shown, but is used in a Hyperlink or it’s an ID.
Order	The order in which the property is to be shown. Important: counting must restart for each Category.
FieldType	Zero or more field types, these are covered per Category later on.
Hyperlink	Make the property clickable with the Hyperlink specified. It is possible to include the value of a property in the Hyperlink. This is done by putting the value of a SystemName property in square brackets in the Hyperlink. For example if there is a property with the SystemName ‘Example’, then the URL becomes: http://www.example.com/?queryString=[Example]
OpenAutomatically	Ignored by the CrmService.
Category	The Category for this item.
ShowOnToast	Can be used to force the inclusion of the attribute in the query.

Contact

The items in this category are the properties shown of a single contact. It supports the following field types:

Field type	Description
Id	At least one Field Type must be marked as Id This field type in combination with a value in the Hyperlink column, will be shown as CRM link.
Name	The name of the contact which will be shown as the contact’s title.
PhoneNumber	Column may contain a phone number.
SipAddress	Column may contain a SIP The Session Initiation Protocol, or SIP, is a protocol for multimedia communication (audio, video and data communication). SIP is also used for Voice over IP (VoIP). SIP has interactions with other Internet protocols such as HTTP and SMTP.-address.
Email	Column may contain an e-mail address.
Date	Column contains a date (and time).
Search	At least one Field Type must be marked as Search. Makes this column searchable. This type should be set for each column containing a PhoneNumber, Email or SipAddress to search for a contact match. This type will also mark a field as a search option if no ContactSearch items are configured.

ContactSearch

This category is used to display a summary of the contact during searching of contacts. All properties which should be searchable, must have the Search field type and should be fully configured. The column containing the values to verify the identity of the caller, e.g. a zip code, must have the field type Verify.

Lead and LeadSearch

See Contact with regards to Lead and ContactSearch for LeadSearch.

Case

The DisplayNames in this category are fixed. The following items are supported:

DisplayName	Description
Title	Multiple items can have this DisplayName. Must have the Name field type.
Description	Only a single item is allowed to have this DisplayName.
Date	Must have the Date field type to be used.
Status	The case status including the case status colors. The values must match the CaseStatus setting. Add field type FormattedValue to obtain the display name of this property.

It is also possible to add a ID field type to an ID item or any of the other items. The ID field type in combination with a URL in the Hyperlink column will be shown as a CRM link for each case.

CaseSearch

These must have the same items as the Case category. The difference here is that items may have the Search field type.

PluginSettings

1. Open the UCC A Unified Contact Center, or UCC, is a queue of interactions (voice, email, IM, etc.) that are handled by Agents. Each UCC has its own settings, IVR menus and Agents. Agents can belong to one or several UCCs and can have multiple skills (competencies). A UCC can be visualized as a contact center “micro service”. Customers can utilize one UCC (e.g. a global helpdesk), a few UCC’s (e.g. for each department or regional office) or hundreds of UCC’s (e.g. for each bed at a hospital). They are interconnected and can all be managed from one central location. Config page on SharePoint

2. Open the Plugin Settings list

3. You can add the following optional settings:

   Setting	Value	Scope
   ShowStatus	Show what kind of contact is calling. Can be a Contact, Lead or Unknown. Default True.	CrmService
   PhoneNumberFormat	The format used when searching the CRM when a call comes in. There are four values: E164 The E.164 phone number format is an international (ITU) standard for dialing telephone numbers on the Public Switched Telephony Network (PSTN). Loosely formulated, only "+" and upto 15 digits (0-9) are allowed For example: +4433221100 (For number notation/display and storage see the E.123 standard): e.g. +0012345678. See The International Public Telecommunication Number Plan. International: e.g. +1 206-555-1234. Based on the E.123 The E.123 format is an international (ITU) standard for notation and storage of international telephone numbers, email addresses and web addresses. Loosely formulated, "+ ", digits, non alphanumeric characters (like brackets, hyphens) and spaces are allowed For example: +44 (333) 221-100 International format. Rfc3966 The rfc3966 format defines an international (IETF) standard for dialing (and storing) a telephone number as a URI. Loosely formulated, start the URI with "tel:" followed by an E.164 or E.123 formatted phone number. For example: tel:+44333221100: e.g. tel:+1-206-555-1234. See The tel URI for Telephone Numbers. Wildcard: do a wildcard search. Default. Warning Change this setting if phone numbers are stored in any of the other formats to improve performance.	CrmService
   CaseStatus	Mapping of a case (incident) status (statuscode) to a WebAgent status. Default: Copy Mapping Active=Open Resolved=Closed Canceled=Closed In Progress=Open On Hold=Open Waiting for Details=Open Researching=Open Problem Solved=Closed Left side is the formatted value as configured in Dynamics, see also "MS CRM Dynamics Entity StateCodes and StatusCodes", the right side is either New, Open or Closed. Note This assumes that statuscode (and not statecode) is configured in PluginAttributes as the case’s status.	CrmService
   BaseLeadsQuery	Optional query used for the retrieval of leads: (No default) Copy Query Select Top (100) {0} From Contact Where {1} Note Each query must contain a row, rownum, limit or fetch first <num> rows. In other words SELECT * search is not allowed (nor advised or good practice).	CrmService
   BaseCaseQuery	The query used for the retrieval of cases: No default) Copy Query Select Top (100) {0} From Cases Where {1}	CrmService
   ContactCaseFilter	Optional where statement for when retrieving cases: (No default) Copy Filter Name like {0} Note Each query must contain a row, rownum, limit or fetch first <num> rows. In other words SELECT * search is not allowed (nor advised or good practice).	CrmService
   UseDefaultPluginAttributes	Setting to overrule the default plugin attributes settings. If this setting does not exist or the value is set to True then the default plugin attributes settings are used. If you want to overrule the default PluginAttributes settings just add this setting and set the value to False. All PluginAttributes will have to be added manually if set to False.	CrmService
   DbType	Setting to indicates what kind of database it is. For example: If the value is "oledb" it indicates it is a OLEDB database and uses another provider to query the database. Important: If this setting not exists or the value is empty it will use the default provider to query the sql database. see the data provider chapter for more details on what to use.	CrmService

For the mandatory Settings see Add PluginSettings

Data Providers

SQL Server is the default protocol protocol to communicate with SQL Server. It is lightweight and performs well because it is optimized to access a SQL Server directly without adding an OLE DB layer. Data provider information:

OLE DB uses native OLE DB through COM interop to enable data access. The data provider for OLE DB supports both local and distributed transactions. For distributed transactions, the data provider for OLE DB, by default, automatically enlists in a transaction and obtains transaction details from Windows Component Services. Data provider information:

SQL Server

Recommended for middle-tier applications that use Microsoft SQL Server.

Recommended for single-tier applications that use Microsoft Database Engine (MSDE) or SQL Server.

Recommended over use of the OLE DB provider for SQL Server (SQLOLEDB) with the data provider for OLE DB.

Connectionstring: Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;

ConnectingString examples:

OLE DB

For SQL Server, the data provider for SQL Server is recommended instead of this provider.

Recommended for single-tier applications that use Microsoft Access databases. Use of an Access database for a middle-tier application is not recommended.

ConnectionString: Provider=MSOLEDBSQL;Server=myServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;

ConnectingString examples: