Please enable JavaScript to view this site.

Navigation: Advanced topics > Events > Sample events > Database

Search Master and Details tables together

Scroll Prev Next More

 

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:

About Search API

setSearchSQL

About Search API

Master-details relationship between tables

SQL query screen