We may have a need to access the SQL clause of a search page. How can we know the SQL that is issued from a search page?

Below is the code for a simple search page.

[code language="javascript"]
var searchBlock = CRM.GetBlock("opportunitysearchbox");
var gridBlock = CRM.GetBlock("opportunitygrid");
var myBlockContainer = CRM.GetBlock("Container");
with (myBlockContainer)
{
AddBlock(searchBlock);
AddBlock(gridBlock);
}
gridBlock.ArgObj = searchBlock;
CRM.AddContent(myBlockContainer.Execute());
///Start
/// Extra Code will go here
///End
Response.Write(CRM.GetPage());
[/code]

The ArgObj property allows the parameters entered into the search box to be passed to the query that draws the grid. This forms the basis of the SQL WhereClause.

The ArgObj is interesting because it is a variant parameter and in other circumstances it can be passed a string containing the name value pairs to be used in the query.

In the above example however it is being passed the screen object. If you include the line

[code language="javascript"]
Response.Write(gridBlock.ArgObj);
[/code]

It will output "opportunitysearchbox".

We can not directly access the WhereClause using the ArgObj property. But we will make use of it indirectly.

Note: Listblocks do have a property called SelectSQL but this will only return the base SQL of the grid used in the search screen and not the full SQL used to return the rows. It is mainly used when creating grid block in code without reference to MetaData.

We will have to reconstruct the SQL used in the searchpage. We can do this using the knowledge that the ArgObj property holds the screen object. Screen objects are enumerable so we can start to examine the fields.

Using the above code we can insert this sample to show the name value pairs that are passed to the Grid.

[code language="javascript"]
var strX ="";
var myE = new Enumerator(gridBlock.ArgObj);
while (!myE.atEnd())
{
if (Defined(Request.Form(myE.item())))
{
strX += myE.item() +"/"+Request.Form(myE.item());
}
myE.moveNext();
}
Response.Write(strX);
[/code]

This is still not finished because we would need to do a little more work to build the actual where clause. We would need to check each fields entrytype to see if they were strings. If they were then we would need to make sure we used "like" and wild cards. I have written elsewhere on the blog about the EntryTypes.

Once we have built our string that represents the SQL Clause then it can be used with a QueryObject or a record object.