SuperOffice Customer Service supports fulltext searching on Request content if you are using Microsoft SQL Server. Fulltext indexing radically increases the speed when searching for text in messages. I don’t have exact numbers, but when we introduced this on our database (some hundred thousands requests), searching for a particular text went from minutes to seconds. If you have a few requests, I highly recommend enabling it.
The full text searching is installed when you create the database (using ServerSetup/DBSetup), if it is already enabled in MSSQL. If it is not yet enabled in MSSQL, we will not configure it. Enabling it in MSSQL later on will not help, as you have already created the database. Consequently, you will have to configure it manually. This article will explain how.
Installing the full text searching capability
Installing the full text searching capability is done using the MSSQL installation utility. You can read about it here: http://msdn.microsoft.com/en-us/library/ms142571.aspx. After you have installed it, you should be able to execute the following query: “SELECT fulltextserviceproperty('IsFulltextInstalled')” and it should return 1.
Creating the catalog
Before you start: backup your database!
The easiest way to create a full text catalog is from within MSSQL Management Studio. Navigate to [database] > Storage > Full text catalogs. You probably do not have a catalog, so right click and create one. You can name it “crm7_FT” and set it to be owned by the same user who is accessing your database. Set “Accent sensivity” to true. Set it as the default catalog. After you have created the catalog, you can edit it and create the indexes we would like. There indexes on three tables, and you must create all of them to make sure our software does not crash when it tries to use them: EJ_MESSAGE, KB_ENTRY and TEXT. The dialog should look like this:
You first click on the chosen object (table) and the right arrow to assign it to the catalog. Afterwards, you can edit each Object (table) and specify the columns and unique index. They are:
- EJ_MESSAGE:
- Unique index: IDXEj_messageId
- Columns: body, html_body, search_title
- KB_ENTRY:
- Unique index: IDX_Kb_entryId
- Columns: answers, keywords, question, title
- TEXT:
- Unique index: IDXTextId
- Columns: text
Check “Table is full-text enabled” for all, and automatick tracking of changes. OK out of the dialog.
Enable full text searching in SuperOffice
Inside CRM, we check for a preference when we verify whether to use full text searching or not. In order to enable full text, you can create this preference manually. (We use userpreference_id = –1 so that we don’t have to bother with adjusting the sequence. If you (because you’ve inserted user preferences manually before) already have –1, then use –2, –3, etc.) Execute first the following query to get the system owner:
“select current_id from travelcurrent”
Then, using that value as [owner_id], execute the following to insert the userpreference:
“insert into userpreference (userpreference_id, deflevel, maxlevel, owner_id, prefsection, prefkey, prefvalue) values (-1, 3, 3, [owner_id], ‘System’, ‘FulltextIndexing’, ‘1’)”
Test the result
Go to “Find requests” inside CS, and add the column “Request content” as a search field. You should now be able to search for a text and even with a large number of requests, the response should be very quick:
