Using Microsoft Full-Text Search within Dovetail applications

For years, Clarify has used the full-text search engine from Fulcrum (now Hummingbird OpenText) to allow full text searches in its applications. This was important back in the day, since the database vendors didn’t offer good solutions to this problem. Now they do.

A couple years ago, as we started expanding our knowledgebase, we had the need to perform full-text searches. However, we preferred to stay away from the Fulcrum/Hummingbird product, mostly because of how Clarify integrated with it. Corrupted indexes, huge amounts of time to create/re-create indexes, and having to setup cron jobs to schedule index runs, were just a few of the problems I ran into on previous implementations. As we used Microsoft SQL Server as our database platform, we decided to use the FTS engine that is bundled with SQL Server, and we’ve been happy with the result.

This article will outline the changes made to incorporate Microsoft FTS with fcClient (our agent-facing web application for Clarify). In a future article, I’ll also cover the changes made to support this within Dovetail SelfService (our customer-facing web application for Clarify).

We’ll add a Full Text Search section to the left side of the console:

FTS on Console Left


Performing a search will open the search results window:

FTS Results


The following instructions will outline how to incorporate this customization in your implementation. 

Install & Configure Microsoft FTS 

1. Install Microsoft Search. This is an optional component of SQL Server, installable from the SQL Server media.

A. When this is complete you will see "Microsoft Search" in the Services control panel.
B. To turn it on for a particular database, you may need to run the following script:
USE <your database name>
EXEC sp_fulltext_database ‘enable’

2. FTS enable your database

A. Create FTS catalog. From Enterprise Manager, right-click on the database, choose "FTS Index", then "Define …".
B. Add tables/columns to catalog.

Right-click on the table, select Full Text Index.
Follow the wizard (use objid for index) to add fields:

Table: Workaround
Add columns:

  • title
  • description

Table: Probdesc
Add columns:

  • title
  • description

Table: Case
Add columns:

  • title
  • case_history

Table: Bug
Add columns:

  • title
  • initial_desc
  • history

C. Populate catalog.

In enterprise manager, r-click on the full text catalog and select “Start Full Population”.

D. Setup schedule.

Right-click on the full text catalog, select Schedules and follow the wizard. 

Customize fcClient 

A zip file containing the new files can be downloaded here: http://folgers/download/

3. Copy the following files to $fcclient/pages/console:

  • fts.asp
  • inc_fts.asp
  • console_fts.asp
  • check.png
  • empty.gif

4. Modify $fcclient/pages/console/console_left.asp:

In console_left.asp, you’ll see:
<div id="renameMenu" class="clsConsole-menu">

Immediately BEFORE the above code, add the following:

<!—————————–START FTS————————————>
<!–#include file="inc_fts.asp"–>
<!–#include file="console_fts.asp"–>
<!——————————–END FTS————————————>

Note: If you have a custom version of the console, you will need to modify the relative paths in fts.asp, inc_fts.asp, and console_fts.asp

That’s it. Try it out, let me know if you run into any difficulties.