Cireson Partners, Customers and Community members share your customizations and examples here to help benefit the community as a whole to earn Kudos and badges.

DISCLAIMER

All files and projects located here are provided and come "as-is" and without any warranty or support. Use at your own risk. Your use of Community Uploads is subject to our Terms of Use.

Cireson does not and will not support or maintain these enhancements, extensions, and scripts.

For Team Cireson uploads click here.

WorkItem Full-Text Search - Download

Roland_KindRoland_Kind Partner Advanced IT Monkey ✭✭✭
edited April 4 in Community Uploads

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


Comments

  • Tuan_LuuTuan_Luu Customer IT Monkey ✭

    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'

Sign In or Register to comment.