Sage CRM offers the ability to search using Keywords across all entities but this keyword searching does not extend into documents that may have been uploaded into the library.

As you can see from the screen shot displayed below, it is possible to easily integrate Microsoft Indexing Services into Sage CRM to allow full document search across all files contained within the library.

Note: This depends on the usage of Microsoft Indexing Services which will need to be running on your webserver.

When building this example I used the following Microsoft references

• Indexing Service
• Integrating Microsoft Index Server

Note: You will need to double check the information for the operating system you are developing on. I have created this example on my demonstration machine which runs XP.

Set Up the Catalog

The first thing that you must do is to create a new catalog that will be populated by information from the Library folder underneath the installation folder.

Within the Windows navigate to

Control Panel > Administrative Tools

and open the Computer Management short cut and expand the view to examine the Services and Applications. In the tree view you need to open the Indexing Service option

In my system I created a new 'Library' catalog.

To create a new catalog then right click the Indexing Service option, select New then 'Catalog'.

Enter the name of your new Catalog and its location. This is where the catalog file will be created.

e.g.

Name: Library
Location: C:\Program Files\Sage\CRM\CRMSP1\Library

Once the Catalog has been created, you will need to add the directory that is going to be Indexed.

My installation of CRM is called CRMsp1 so the directory I am indexing has the path "C:\Program Files\Sage\CRM\CRMSP1\Library"

Once the directory is added and the indexing is running

The Code

The following is the code used for this example

<!-- #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 oQuery = Server.CreateObject("IXSSO.Query")
var util = Server.CreateObject("ixsso.Util");
//util.AddScopeToQuery(oQuery, "C:\Program Files\Sage\CRM\CRMSP1\Library", "deep")

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

oQuery.Catalog = SEARCH_CATALOG
oQuery.Query = strQuery;
oQuery.MaxRecords = 25;
oQuery.SortBy = "Write[d]"
oQuery.Columns = "DocAuthor, vpath, doctitle, FileName, Path, Write, Size, Rank, Create, Characterization, DocCategory"

try
{
var oRS = oQuery.CreateRecordSet("nonsequential")
}
catch (e)
{
return (e.message);
//Response.End();
}
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
}

}

%>

Notes

1. The page uses the Indexing Services object to allow querying of the catalog. The object is IXSSO.Query. I have not used the IXSSO.Util object in this code but you may want to research whether this is useful for your own purposes.
2. Both the search box and the results list are created using blocks not bound to data. The search box uses the technique of building a screen not bound to meta data. See the article "Building a Screen not bound to Meta Data using the ASP COM API"
3. The result block is 'hand crafted' mixing the results from the Indexing Services query with HTML. That is then added to a Sage CRM Content block and the result block and the search box are added to a container before being output to the screen.
4. I have kept the query options simple to allow free form search that include examples like
   
   time and #path NOT *global*
   
   For more details you will need to reference the Index Server Query Language.
   
5. The result output has been formatted to include column titles based on existing translations to avoid hard coding.
6. The dates in the result have been formatted using the functions discussed in the article "Useful Date Functions".

The file can be downloaded by DPP members from here.