Please enable JavaScript to view this site.

Navigation: Advanced topics > Programming topics > Search API > Methods

Search API: setSearchSQL

Scroll Prev Next More

 

Replaces the current search expression for the selected field. Please note that this function is designed to replace the search condition. It user didn't search for anything in the field this function will have no effect. To create a new search condition use setFieldValue() function. This code should go to AfterTableInit event of the List page.

Syntax

setSearchSQL(field, sql);

Arguments

field

the name of the field.

sql

an SQL query to be used for the field.

Return value

No return value

Example 1

Change a search condition for the Make field.

 

dynamic srchObj = SearchClause.getSearchObject("carsmodels");
dynamic value = srchObj.getFieldValue("make");
if( value != null ) {
  srchObj.setSearchSQL("make", "make='" + value.ToString() + "' or id>12 and id<15");
}

Example 2

Here is how you can search master and details tables together. Let's say that you have Orders and OrderDetails tables and need to find orders that contain a certain product.

 

1. Modify the Orders SQL Query to add a dummy field named 'product'. Make sure this field is searchable.

 

SELECT
       OrderID,
       CustomerID,
       EmployeeID,
       OrderDate,
       ShipAddress,
       ShipCity,
       ShipRegion,
       ShipPostalCode,
       ShipCountry,
      '' as product
FROM orders

 

2. Orders table, AfterTableInit event:

 

dynamic srchObj = SearchClause.getSearchObject("orders");
dynamic value = srchObj.getFieldValue("product");
if( value != null ) {
  srchObj.setSearchSQL("product", "OrderID in (select OrderID from OrderDetails where ProductName like '%" + value.ToString() + "%'"));
}

 

Note: in this event, we do a subquery to find all orders that contain the product in question.

See also:

Search API: getSearchObject

Search API: getFieldValue()

SQL query screen: SQL tab

About Search API