This article discusses the challenges that can be faced when building a Search page that queries an external database table through an ODBC driver. I have used for this example a connection to a Sage ERP MAS 90 table.

I have assumed that you will have read the following articles

• Connecting to an External ODBC database e.g. Sage ERP MAS 90
• A Useful SQL Query Tool for when working with ODBC Databases
• Changing the EntryType for Fields in an External Table. e.g. ap_vendor from Sage ERP MAS 90
• Creating a simple ASP List Page that Displays Data from an external Database using an ODBC driver e.g. Sage ERP MAS 90

Below is an image of a search screen called from the main search menu.

When building a connection to an external database using an ODBC driver you have to be aware of the limitations that this provides.

The ODBC driver for Sage ERP MAS 90 is Read Only which for data access is not a problem. More of an issue is that the strings are case sensitive so this means that the values used in where clauses in SQL have to be carefully considered. Another point to consider is the nature of the primary key. Sage CRM assumes key integrity is provided by a single unique numeric field. In the case of a table like ap_vendor the key is actually a composite of apdivisionno and vendorno, both fields are exposed to Sage CRM through the driver as text fields.

Another issue to be aware of is that Sage CRM expects to be able to pass SQL like this

[code language="sql"]
select * from ap_vendor WHERE vendorname LIKE N'A%' ESCAPE '|'
[/code]

This type of query if run against the Sage ERP MAS 90 database will result in an error

Error: Expected lexical element not found: USER (State:37000, Native Code: 3F7)

Note: I have been using the WinSQL tool from Synametrics Technologies (http://www.synametrics.com/SynametricsWebApp/WinSQL.jsp) to allow me to test my SQL against the Sage ERP MAS 90 tables.

The error occurs because Sage CRM prefixes its strings 'xxxx' pass in where clauses with N. I have discussed why Sage CRM does this in the article "Why does N' appear in system generated SQL?".

But it is not just the lack of support for unicode strings it is that the driver does not support the syntax that SQL Server would support when generating LIKE statements

match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]

The ESCAPE is not supported by the Sage ERP MAS 90 ODBC driver.

Note:

All the ASP page code below is assumed to be placed in the normally tags

<%
//code goes here
%>

Normally the code for a Search Page would look like this

[code language="javascript"]
var searchBlock = CRM.GetBlock("VendorSearchBox");
searchBlock.Title = CRM.GetTrans("tabnames","search");
var listBlock = CRM.GetBlock("VendorList");
var myBlockContainer = CRM.GetBlock("Container");
with (myBlockContainer)
{
AddBlock(searchBlock);
AddBlock(listBlock);
}
listBlock.ArgObj = searchBlock;
CRM.AddContent(myBlockContainer.Execute());
Response.Write(CRM.GetPage("find"));
[/code]

In the code above the values entered into the search screen by the user would be passed to the list block in the line

[code language="javascript"]
listBlock.ArgObj = searchBlock;
[/code]

But the driver will throw an error because of the way Sage CRM automatically builds the Where Clause.

As a consequence the page has to be re-written to assemble the Where Clause in such a way that is would not generate the error.

[code language="javascript"]
var searchBlock = CRM.GetBlock("VendorSearchBox");
searchBlock.Title = CRM.GetTrans("tabnames","search");
var listBlock = CRM.GetBlock("VendorList");

var myBlockContainer = CRM.GetBlock("Container");
with (myBlockContainer)
{
AddBlock(searchBlock);
AddBlock(listBlock);
}

var myFormValue
var ArgString ="";
var intFlag = 0
var myE = new Enumerator(searchBlock);
while (!myE.atEnd())
{
myFormValue = Request.Form(myE.item())+"";
if (myFormValue.length>0)
{
if (intFlag >0)
{
ArgString += " and ";
}
ArgString += myE.item() +" like '"+Request.Form(myE.item()) +"%'";
intFlag ++
}
myE.moveNext();
}

if (CRM.Mode==Save)
{
listBlock.ArgObj = ArgString;
}

CRM.AddContent(myBlockContainer.Execute());
Response.Write(CRM.GetPage("find"));
[/code]

The code above uses an enumerator to check the fields of the search block and to see whether the user has supplied a value. If a value is passed to restrict the rows it is included into the where clause as it is assembled as a string 'ArgString'. The string is then passed to the list block when the screen mode has changed to indicate the users has pressed the search button.

[code language="javascript"]
if (CRM.Mode==Save)
{
listBlock.ArgObj = ArgString;
}
[/code]

There are other changes that can be made to the code.

I have added a message to remind the user that the fields are case sensitive.

I have added a button to clear out any search values and start the search again.

I have also supressed the display of the 'My CRM' tags by forcing the use of the 'find' tabs group in the line

[code language="javascript"]
Response.Write(CRM.GetPage("find"));
[/code]

You may have read elsewhere about building Search Screens for Custom Entities and the use of the line

[code language="javascript"]
CRM.SetContext("Find");
[/code]

BUT this is not useful for a external entity especially as it assumes the primary key is an integer.

The final code then for a page that will search on the Sage ERP MAS 90 table ap_vendor will look like:

[code language="javascript"]
var contentBlock = CRM.GetBlock("Content");
contentBlock.contents = "

"; contentBlock.contents += CRM.GetTrans("GenCaptions","fields") +" "+CRM.GetTrans("Colnames","CaseSensitive"); contentBlock.contents += "

";

var searchBlock = CRM.GetBlock("VendorSearchBox");
searchBlock.Title = CRM.GetTrans("tabnames","search");
var listBlock = CRM.GetBlock("VendorList");

var strSearchFormClearButton = CRM.Button("Clear", "clear.gif", "javascript:document.EntryForm.em.value='6';document.EntryForm.submit();");

var myBlockContainer = CRM.GetBlock("Container");
with (myBlockContainer)
{
AddBlock(contentBlock);
AddBlock(searchBlock);
AddBlock(listBlock);
AddButton(strSearchFormClearButton);
}

var myFormValue
var ArgString ="";
var intFlag = 0
var myE = new Enumerator(searchBlock);
while (!myE.atEnd())
{
myFormValue = Request.Form(myE.item())+"";
if (myFormValue.length>0)
{
if (intFlag >0)
{
ArgString += " and ";
}
ArgString += myE.item() +" like '"+Request.Form(myE.item()) +"%'";
intFlag ++
}
myE.moveNext();
}

if (CRM.Mode==Save)
{
listBlock.ArgObj = ArgString;
}

CRM.AddContent(myBlockContainer.Execute());
Response.Write(CRM.GetPage("find"));
[/code]