Here is how you can search master and details tables together.
For example, you have Orders and OrderDetails tables and you want to find the 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 '%" + valueToString() + "%')");
}
Note: In this event, we do a subquery to find all orders that contain the product in question.
See also:
•Master-details relationship between tables
