WorkItem Full-Text Search - Download

Roland_Kind

Hello

based on the SQL Server Full-Text search engine I have created a small and simple solution using this functionality inside the Cireson Portal for WorkItem searches.

The search results will include all types of Workitems, action log entries, manual and review activities. Workitem custom fields (class extensions) will also be included in the search. Additional relationships can be included (by adjusting the SQL files)

 

The result looks like the following Screen-Shot

 

 

Installation

• copy the content of the enclosed zip file to the CustomSpace directory preserving the directory structure

• execute the SQL Scripts (in the RKWIFTS\SQL folder) in the following order (using MSSQL Server Studio)

 

1. fn_RKLocalizeText.sql

2. fn_RKGetRelatedUser.sql

3. fn_RKWorkItemFTS.sql

4. RKCreateWorkItemFTS_DisplayStrings.sql

5. RKCreateWorkItemFTS_PortalQuery.sql

6. RKCreateWorkItemFTS_IdxTable.sql

7. add the following lines to the custom.css file:

.rkrow:after {
    content: "";
    display: table;
    clear: both;
}

.rkbody {
    width: 100%;
    padding-top: 0px;
    background: #fff;
    overflow-y: hidden;
    overflow-x: auto;
    -ms-overflow-style: scrollbar;
}

 

The index is created by executing the RKCreateWorkItemFTS_IdxTable.sql file. After the first time execution this should be done periodically (e.g. every 24h – or if necessary more often by using either a scheduler task on the SQL Server (see enclosed .ps1 example file)  or a SQL Server Agent script.)

 

The enclosed RKFTSTest.sql script can be used for a simple testing of the full text search inside MSSQL MS.

 

Please Note:

This small solution was tested in SCSM 2016 / SQL 2016 @ Cireson portal 8.2.x – I really recommend to test this on a non-production/testing environment first, before using this in a production environment to check the impact on the database!

starting the application

http://yourportal/view/WIFTS

regards

Tuan_Luu

Hi,

FYI..

In RKCreateWorkItemFTS_IdxTable.sql

Below /* Search in ReviewActivityLog View (!) */
It should be where name = 'MTV_System$WorkItem$Activity$ReviewActivity' instead of where name = 'MTV_System$WorkItem$Activity$ManualActivity'