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.
set srchObj = getSearchObject("carsmodels")
value = srchObj.getFieldValue("make", null)
if not IsNull(value) then
srchObj.setSearchSQL "make", "make='" & value & "' or id>12 and id<15"
end if
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:
set srchObj = getSearchObject("orders")
value = srchObj.getFieldValue("product", null)
if not IsNull(value) then
srchObj.setSearchSQL "product", "OrderID in (select OrderID from OrderDetails where ProductName like '%" & value & "%')"
end if
Note: in this event, we do a subquery to find all orders that contain the product in question.
See also:
