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 ) {
dynamic sql = DB.PrepareSQL("OrderID in (select OrderID from OrderDetails where ProductName like '%:1%')", value.ToString());
srchObj.setSearchSQL("product", sql);
}
In this event, we perform a subquery to find all the orders that contain the product in question.
See also:
•Master-details relationship between tables
