Find Mode

Find Mode is a special mode that can be assumed by a foundset object to perform data searches using a powerful, high-level abstraction. When in Find Mode, the foundset's Data Providers, normally used to read/write data, are instead used to enter search criteria. Any data provider can be assigned a search condition which should evaluate to a String, Number or Date. Because forms typically bind to a foundset, criteria may be entered from the GUI by the user or programmatically.

A foundset enters Find Mode when its find method is invoked. This method returns a Boolean, because under certain circumstances, the foundset may fail to enter find mode. Therefore, it is good practice to enclose a find in an an if statement, so as not to accidentally modify the selected record. A foundset exits Find Mode when its search method is executed, upon which the foundset's SQL query is modified to reflect the expressed criteria and the matching records are loaded. The search method returns an integer, which is the number of records loaded by the find. However this doesn't necessarily represent the total number of matching records as foundset records are loaded in blocks.

Example:

// Find all customers in the city of Berlin
if(foundset.find()){   // Enter find mode
    city = 'Berlin';   // Assign a search criteria
    foundset.search(); // Execute the query and load the records
}

Results in the foundset's SQL query:

 SELECT customerid FROM customers WHERE city = ? ORDER BY customerid ASC //Query params: ['Berlin']

Search Criteria with Logical AND

When multiple search criteria are entered for multiple data providers, the criteria will be concatenated with a SQL AND operator.

Example:

// Find all customers in the city of Berlin AND in the postal code 12209
if(foundset.find()){     // Enter find mode    city = 'Berlin';     // Assign city search criterion
    city = 'Berlin';     // Assign a search criteria
    postalcode = '12209' // Assign postal code criterion
    foundset.search();   // Execute the query and load the records
}

Results in the foundset's SQL query:

 SELECT customerid FROM customers WHERE city = ?  AND postalcode = ? ORDER BY customerid ASC //Query params: ['Berlin','12209']

Multiple Find Records for Logical OR

It's important to note that when in Find Mode, a foundset will initially contain one record object. However, multiple record objects may be used to articulate search criteria. This has the effect that the criteria described in each record are concatenated by a SQL OR.

Example:

// Find customers in the city of Berlin AND in the postal code 12209...
// OR customers in the city of San Francisco AND in the postal code 94117
if(foundset.find()){        // Enter find mode    city = 'Berlin';
    city = 'Berlin';   // Assign a search criteria
    postalcode = '12209';
    foundset.newRecord();   // Create a new search record
    city = 'San Francisco'
    postalcode = '94117';
    foundset.search();      // Execute the query and load the records
}

Results in the foundset's SQL query:

 SELECT customerid FROM customers WHERE (city = ?  AND postalcode = ?) OR (city = ?  AND postalcode = ?) ORDER BY customerid ASC //Query params: ['Berlin','12209','San Fransisco','94117']

Finding records through a relation

Find Mode is very flexible as searches can traverse the entire data model. When a foundset enters find mode, any foundset related to a search record can be used to enter criteria. Moreover, related foundsets can use multiple search records so any permutation of Logical AND / OR is possible.

Example:

// Find customers that have 1 or more orders which were shipped to Argentina
if(foundset.find()){                               // Enter find mode
    customers_to_orders.shipcountry = 'Argentina'; // enter criteria in a related foundset
    foundset.search();                             // Execute the query and load the records
}

Results in the foundset's SQL query:

SELECT DISTINCT customers.customerid FROM customers
LEFT OUTER JOIN orders ON customers.customerid=orders.customerid
WHERE orders.shipcountry = ? ORDER BY customers.customerid ASC

And there are no limitations to the number of traversals across related foundsets.

Example:

// Find customers with one or more orders containing one or more products supplied by a vendor in USA
if(foundset.find()){
    customers_to_orders.orders_to_order_details.order_details_to_products.products_to_suppliers.country = 'USA';
    foundset.search();
}

Finding records within a related foundset

It is worth pointing out that related foundsets may be put into Find Mode as well. The foundset will maintain the constraints imposed by the relation in addition to the criteria specified in the data providers.

Example: This operation is nearly identical to the previous search on ship country, however it matters which foundset is in Find Mode. The difference is that this operation searches for order records of a particular customer.

// Find orders of THE SELECTED CUSTOMER that were shipped to Argentina
if(customers_to_orders.find()){
    customers_to_orders.shipcountry = 'Argentina';
    customers_to_orders.search();
}

Results in the foundset's SQL query (notice the relation constraint is preserved):

SELECT orderid FROM orders  WHERE customerid = ? AND shipcountry = ? ORDER BY orderid ASC

Special Operators

Servoy's Find Mode provides several special operators that when used in combination can articulate the most sophisticated search requirements.  Operators and operands should be concatenated as strings.