Adding Extra Filter Columns to the Call-Index Table

Status:	Published
Version:	1.0
Authors:	HTL QA
Applies to:	Supportworks ESP Version 7.3.5 to 7.4.1 (then in on-line help)

When you perform a free-text search in any of the client's call-search views, it is the index table updatedb_call_index (found in the sw_systemdb database) that is queried instead of the actual Open/Closed Call table (opencall) or Call Diary table (updatedb). The index table initially contains just the basic columns, from both call tables, that may typically be required to filter the results of such searches of call data. Therefore, if you try to filter by any other columns, an error message will be displayed whenever a search is attempted.

In order to successfully filter the free-text search results by these other columns, you will first have to add them to the index table and then rebuild that table to populate the columns with the relevant data from the call-table records. On a dual-core machine with a 3GHz processor and 2GB of RAM, you can expect the rebuilding to take approximately 15 minutes per million updatedb records (irrespective of how many columns you have added or which tables they came from). The procedure is as follows:

1. Stop SwServerService and its dependent services: SwMailService and SwMailSchedule.
2. Back up the entire Supportworks database.
3. Using an SQL tool such as Interactive SQL, enter an SQL command sequence to add the requisite columns to the updatedb_call_index table. For example, to add appcode and callclass, you would enter the following:

   use sw_systemdb;
   alter table updatedb_call_index add appcode varchar(160);
   alter table updatedb_call_index add callclass varchar(64);

4. Run the following utility to rebuild the indexes:
   C:\Program Files\Hornbill\Supportworks Server\bin\swudbidx.exe
   (where C:\Program Files\Hornbill\ is the default installation folder)
5. Once the rebuild has been completed, enter an SQL command to show the contents of your added columns, thus confirming that they are now populated. For the two columns in the above example, the command would be:

   select appcode,callclass from updatedb_call_index;

6. Restart SwServerService and any other services that you stopped (SwMailService and SwMailSchedule).

Note: If you have a column of the same name in both call tables, and you add that to the index table, you will find that it will fail to populate when the indexes are built. It is not possible to add columns when the name is the same in both the call table (opencall) and the call diary table (updatedb).