Integrating Sage CRM with Indexing Services to enable full document search

Wow!

I am already thinking about some customers that will love this.

Great Jeff!  Showed the prospect and they were overjoyed at how fast we came back with the solution.

Thanks!

I love it. I am going to have to look into cross referencing the indexing data against the filepath and filename information in the library table to expand the filter to library record fields.

Hello Jeff,

I'm just try this but cannot search document included Thai, Chinese language.

Any ideas?

I recently stumbled upon this article and gave it a try. I think it's a great idea Sage CRM is really missing the feature to search in Documents. But if anyone else wants to dust off this excellent article and give it a shot I can possibly save him some trouble.

The indexing service is still available in Windows Server 2008 (R2) of course but the IXSSO Object isn't supported any more in 2008+ you have to use MSIDXS Data Provider instead.

So the changed part of the script could look like this:

...

function doSearch()

{

var strQuery = Request.Form("query")+"";

var strDirectory = "D:\Program Files\Sage\CRM\CRM\Library";

var strConn = "PROVIDER=MSIDXS; DATA SOURCE=" + SEARCH_CATALOG;

//With this Search every query is treated as OR-Search (think about splitting strQuery)

var strComm = "SELECT DocAuthor, doctitle, FileName, Path, Size, Rank, Write, Create FROM SCOPE() WHERE FREETEXT('" + strQuery + "') order by rank";

var oRS= Server.CreateObject("ADODB.Recordset");

if (strQuery != 'undefined') {

oRS.open(strComm, strConn);

var strOutPut

if (oRS.EOF) {

return "No pages were found for the query <i>" + strQuery + "</i>";

}

else

{

...

...

Hi Oliver,

I have Windows Server 2008 R2. I have add the Role "File Services" and the Role Services "Indexing Service".

Then I have change the ASP Page from Jeff with your tip. But I still can't run this work.

Do you have any tip for me?

Christian

Ok I get it now running. Here is the complete ASP Page for Windows Server 2008 R2

<!-- #include file ="accpaccrm.js"-->

<%

/////////////////

//  Search Catalog

//////////////////

var SEARCH_CATALOG = "Library"

//get path information

var strPath = CRM.URL(1282);

var arrayContext = strPath.split("?");

var strContextInfo = arrayContext[0];

var strSID = new String(Request.QueryString("SID"));

var strFullPath ="";

if (CRM.Mode==View)

{

CRM.Mode = Edit;

}

var searchBlock = CRM.GetBlock("entrygroup");

searchBlock.Title = "Search Documents";

var customTextEntryBlock = CRM.GetBlock("entry");

with (customTextEntryBlock)

{

//EntryType set to be single line text entry

EntryType = 10;

DefaultValue = Request.Form("query");

DefaultType = 1

FieldName = "query";

Caption = "Search Word or Phrase:";

size = 100;

maxLength = 255;

Required = true;

}

searchBlock.AddBlock(customTextEntryBlock);

var resultsBlock = CRM.GetBlock("content");

strtest = new String(Request.Form("query"))+"";

if(!strtest)

{

}

else

{

resultsBlock.contents=doSearch();

}

var myBlockContainer = CRM.GetBlock("Container");

with (myBlockContainer)

{

ButtonTitle = "search";

ButtonImage = "search.gif";

AddBlock(searchBlock);

AddBlock(resultsBlock);

}

CRM.AddContent(myBlockContainer.Execute());

if (CRM.Mode==Save)

{

CRM.Mode = Edit;

}

Response.Write(CRM.GetPage("find"));

function doSearch()

{

var strQuery = Request.Form("query")+"";

var strDirectory = "C:\Program Files (x86)\Sage\CRM\CRM\Library";

var strConn = "PROVIDER=MSIDXS; DATA SOURCE=" + SEARCH_CATALOG;

//With this Search every query is treated as OR-Search (think about splitting strQuery)

var strComm = "SELECT DocAuthor, doctitle, FileName, Path, Size, Rank, Write, Create FROM SCOPE() WHERE FREETEXT('" + strQuery + "') order by rank";

var oRS= Server.CreateObject("ADODB.Recordset");

if (strQuery != 'undefined')

{

oRS.open(strComm, strConn);

var strOutPut

if (oRS.EOF)

{

return "No pages were found for the query <i>" + strQuery + "</i>";

}

else

{

strFullOutPut= "<TABLE class=CONTENT border=1 cellSpacing=0 borderColorLight=#ffffff borderColorDark=#ffffff cellPadding=1 width='100%'><TBODY><TR>";

strFullOutPut+="<TD class=GRIDHEAD>"+CRM.GetTrans("colnames","filename")+"</TD>";

strFullOutPut+="<TD class=GRIDHEAD>"+CRM.GetTrans("GenCaptions","size")+"</TD>";

strFullOutPut+="<TD class=GRIDHEAD>"+CRM.GetTrans("colnames","comp_createddate")+"</TD>";

strFullOutPut+="<TD class=GRIDHEAD>"+CRM.GetTrans("colnames","comp_updateddate")+"</TD>";

strFullOutPut+="<TD class=GRIDHEAD>"+CRM.GetTrans("colnames","comp_createdby")+"</TD>";

strFullOutPut+="</TR>";

var strClass = "ROW2";

var intCurrentRow = 1;

var intOldRow = 1;

while(!oRS.EOF)

{

if (intCurrentRow==1)

{

strClass = "ROW2";

intCurrentRow = 2;

}

else

{

strClass = "ROW1";

intCurrentRow = 1;

}

var strFullLibraryInfo = new String(oRS("Path"));

var arrayLibraryInfo = strFullLibraryInfo.split("\\library\\");

strOutPut = "<TD class="+strClass+"><a href='"

strOutPut +=strContextInfo+"/"+arrayLibraryInfo[1]

strOutPut +="?SID="+strSID+"&Act=1282&Mode=0&FileName="

strOutPut +=arrayLibraryInfo[1]+"' target=new title='"+oRS("Path")+"'>"

strOutPut += oRS("FileName")+"</a></TD>";

strFullOutPut+="<TR>";

strFullOutPut+=strOutPut;

strFullOutPut+="<TD class="+strClass+">"+formatSize(oRS("Size"))+"</TD>";

//strFullOutPut+="<TD class="+strClass+">"+oRS("Size")+"</TD>";

//strFullOutPut+="<TD class="+strClass+">"+oRS("Create")+"</TD>";

strFullOutPut+="<TD class="+strClass+">"+formatDate(oRS("Create"))+"</TD>";

//strFullOutPut+="<TD class="+strClass+">"+oRS("Write")+"</TD>";

strFullOutPut+="<TD class="+strClass+">"+formatDate(oRS("Write"))+"</TD>";

strFullOutPut+="<TD class="+strClass+">"+oRS("DocAuthor")+"</TD>";

strFullOutPut+="</TR>";

oRS.MoveNext();

}

strFullOutPut+="</TBODY></TABLE>";

return strFullOutPut;

}

function formatDate(x)

{

// This retrieves the users preferred dates and uses this to format returned dates.

//makes use of the leadingZero function

 var myRecordId = eWare.GetContextInfo('user','user_userid');

 var myRecord = eWare.FindRecord('usersettings',"uset_key like 'NSet_userdateformat%' and uset_userid="+myRecordId);

 var sourceDate = new Date(x);

 var gotDate = leadingZero(sourceDate.getDate())

 var gotMonth = leadingZero(sourceDate.getMonth()+1);

 var gotYear = sourceDate.getYear();

 var gotHours = leadingZero(sourceDate.getHours());

 var gotMinutes = leadingZero(sourceDate.getMinutes());

 var gotTime = gotHours+':'+ gotMinutes;

 var resultDate

 switch(myRecord.uset_value)

 {

 case 'mm/dd/yyyy':

  resultDate = gotMonth +'/'+ gotDate +'/' + gotYear +' '+gotTime;

  //+ ' ' + sourceDate.getHours() +':' sourceDate.getMinutes()

  return resultDate;

 case 'dd/mm/yyyy':

  resultDate = gotDate +'/' + gotMonth+'/'+ gotYear +' '+gotTime;

  //+ ' ' + sourceDate.getHours() +':' sourceDate.getMinutes()

  return resultDate;

 case 'yyyy/mm/dd':

  resultDate = gotYear +'/' + gotMonth +'/'+ gotDate +' '+gotTime  ;

  //+ ' ' + sourceDate.getHours() +':' sourceDate.getMinutes()

  return resultDate;

 default:

  resultDate = gotMonth +'/'+ gotDate +'/' + gotYear +' '+gotTime;

  //+ ' ' + sourceDate.getHours() +':' sourceDate.getMinutes()

  return resultDate;

 }

}

function leadingZero(x)

{

//this examines a number to determine whether or not to add leading zeros.

//useful for dates

//example usage:

//var myDate = new Date();

//Response.Write(leadingZero(myDate.getDate()));

//

if (x <10)

return '0'+x;

else

return x;

}

function formatSize(startSize)

{

if (startSize >1024)

{

endSize = Math.round(startSize/1024)+"KB";

}

else

{

endSize = startSize;

}

return endSize

}

}

}

%>

Oliver and Christian GREAT GREAT post !  Is there a way to edit the search screen (block) to have more then just the search word or phrase option.  

A few more filters I guess like

Create Date = <>

Updated Date. = <>

Created By (Drop Down User List)

Size = <>

File Name

In addition to the free text search of 255 characters?

Either way this is very useful the way it is would just add to the power if you had more filters ;)

Thanks to all !

Ok is it asking to much to possible be able to sort the columns too :)

Only reason I ask is our library is over 33GB with 180,893 files.  

Right now when running searches with the scripts provided above it is producing ALOT ALOT ALOT of files.  I tried using operators like + and or () ""

but not really filtering down the results any.

It's a very interessant codi, but I have a question!

How I can add the option in the search menu for call ASP page with this code??

Thanks!

Hi,

Just to round add to this thread the replacement to Indexing Services is the Windows Search Service which works in much the same way but obviously with some different connection details etc and is effectively the replacement for Indexing Services. However you cannot run both on a single server. The follow code is as per the code above but rewritten to work with Windows Search Services:

<!-- #include file ="accpaccrm.js"-->

<%

/////////////////

//  Search Catalog

//////////////////

var SEARCH_CATALOG = "Library"

//get path information

var strPath = CRM.URL(1282);

var arrayContext = strPath.split("?");

var strContextInfo = arrayContext[0];

var strSID = new String(Request.QueryString("SID"));

var strFullPath ="";

if (CRM.Mode==View)

{

CRM.Mode = Edit;

}

var searchBlock = CRM.GetBlock("entrygroup");

searchBlock.Title = "Search Documents";

var customTextEntryBlock = CRM.GetBlock("entry");

with (customTextEntryBlock)

{

//EntryType set to be single line text entry

EntryType = 10;

DefaultValue = Request.Form("query");

DefaultType = 1

FieldName = "query";

Caption = "Search Word or Phrase:";

size = 100;

maxLength = 255;

Required = true;

}

searchBlock.AddBlock(customTextEntryBlock);

var resultsBlock = CRM.GetBlock("content");

strtest = new String(Request.Form("query"))+"";

if(!strtest)

{

}

else

{

resultsBlock.contents=doSearch();

}

var myBlockContainer = CRM.GetBlock("Container");

with (myBlockContainer)

{

ButtonTitle = "search";

ButtonImage = "search.gif";

AddBlock(searchBlock);

AddBlock(resultsBlock);

}

CRM.AddContent(myBlockContainer.Execute());

if (CRM.Mode==Save)

{

CRM.Mode = Edit;

}

Response.Write(CRM.GetPage("find"));

function doSearch()

{

var strQuery = Request.Form("query")+"";

var strDirectory = "C:\\Program Files (x86)\\Sage\\CRM\\CRM\\Library";

var strConn = "Provider=Search.CollatorDSO.1;Extended Properties='Application=Windows';"

//With this Search every query is treated as OR-Search (think about splitting strQuery)

var strComm = "SELECT System.ItemPathDisplay, System.FileOwner, System.ItemType, System.Filename, System.Author, System.Size, System.DateCreated, System.DateModified FROM systemindex where freetext('" + strQuery + "') and SCOPE='" + strDirectory + "'";

var oRS= Server.CreateObject("ADODB.Recordset");

if (strQuery != 'undefined')

{

oRS.open(strComm, strConn);

var strOutPut

if (oRS.EOF)

{

return "No pages were found for the query <i>" + strQuery + "</i>";

}

else

{

strFullOutPut= "<TABLE class=CONTENT border=1 cellSpacing=0 borderColorLight=#ffffff borderColorDark=#ffffff cellPadding=1 width='100%'><TBODY><TR>";

strFullOutPut+="<TD class=GRIDHEAD>"+CRM.GetTrans("colnames","filename")+"</TD>";

strFullOutPut+="<TD class=GRIDHEAD>"+CRM.GetTrans("GenCaptions","size")+"</TD>";

strFullOutPut+="<TD class=GRIDHEAD>"+CRM.GetTrans("colnames","comp_createddate")+"</TD>";

strFullOutPut+="<TD class=GRIDHEAD>"+CRM.GetTrans("colnames","comp_updateddate")+"</TD>";

strFullOutPut+="<TD class=GRIDHEAD>"+CRM.GetTrans("colnames","comp_createdby")+"</TD>";

strFullOutPut+="</TR>";

var strClass = "ROW2";

var intCurrentRow = 1;

var intOldRow = 1;

while(!oRS.EOF)

{

if (intCurrentRow==1)

{

strClass = "ROW2";

intCurrentRow = 2;

}

else

{

strClass = "ROW1";

intCurrentRow = 1;

}

var strFullLibraryInfo = new String(oRS("System.ItemPathDisplay"));

var arrayLibraryInfo = strFullLibraryInfo.split("\\Library\\");

strOutPut = "<TD class="+strClass+"><a href='"

strOutPut +=strContextInfo+"/"+arrayLibraryInfo[1]

strOutPut +="?SID="+strSID+"&Act=1282&Mode=0&FileName="

strOutPut +=arrayLibraryInfo[1]+"' target=new title='"+oRS("System.ItemPathDisplay")+"'>"

strOutPut += oRS("System.FileName")+"</a></TD>";

strFullOutPut+="<TR>";

strFullOutPut+=strOutPut;

strFullOutPut+="<TD class="+strClass+">"+formatSize(oRS("System.Size"))+"</TD>";

strFullOutPut+="<TD class="+strClass+">"+formatDate(oRS("System.DateCreated"))+"</TD>";

strFullOutPut+="<TD class="+strClass+">"+formatDate(oRS("System.DateModified"))+"</TD>";

strFullOutPut+="<TD class="+strClass+">"+oRS("System.FileOwner")+"</TD>";

strFullOutPut+="</TR>";

oRS.MoveNext();

}

strFullOutPut+="</TBODY></TABLE>";

return strFullOutPut;

}

function formatDate(x)

{

// This retrieves the users preferred dates and uses this to format returned dates.

//makes use of the leadingZero function

var myRecordId = eWare.GetContextInfo('user','user_userid');

var myRecord = eWare.FindRecord('usersettings',"uset_key like 'NSet_userdateformat%' and uset_userid="+myRecordId);

var sourceDate = new Date(x);

var gotDate = leadingZero(sourceDate.getDate())

var gotMonth = leadingZero(sourceDate.getMonth()+1);

var gotYear = sourceDate.getYear();

var gotHours = leadingZero(sourceDate.getHours());

var gotMinutes = leadingZero(sourceDate.getMinutes());

var gotTime = gotHours+':'+ gotMinutes;

var resultDate

switch(myRecord.uset_value)

{

case 'mm/dd/yyyy':

 resultDate = gotMonth +'/'+ gotDate +'/' + gotYear +' '+gotTime;

 //+ ' ' + sourceDate.getHours() +':' sourceDate.getMinutes()

 return resultDate;

case 'dd/mm/yyyy':

 resultDate = gotDate +'/' + gotMonth+'/'+ gotYear +' '+gotTime;

 //+ ' ' + sourceDate.getHours() +':' sourceDate.getMinutes()

 return resultDate;

case 'yyyy/mm/dd':

 resultDate = gotYear +'/' + gotMonth +'/'+ gotDate +' '+gotTime  ;

 //+ ' ' + sourceDate.getHours() +':' sourceDate.getMinutes()

 return resultDate;

default:

 resultDate = gotMonth +'/'+ gotDate +'/' + gotYear +' '+gotTime;

 //+ ' ' + sourceDate.getHours() +':' sourceDate.getMinutes()

 return resultDate;

}

}

function leadingZero(x)

{

//this examines a number to determine whether or not to add leading zeros.

//useful for dates

//example usage:

//var myDate = new Date();

//Response.Write(leadingZero(myDate.getDate()));

//

if (x <10)

return '0'+x;

else

return x

}

function formatSize(startSize)

{

if (startSize >1024)

{

endSize = Math.round(startSize/1024)+"KB";

}

else

{

endSize = startSize;

}

return endSize

}

}

}

%>