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:
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:
•Master-details relationship between tables
