Find mode

Overview

Find Mode is a special mode in Servoy that allows users and developers to perform data searches using a high-level abstraction. When a foundset enters Find Mode, its data providers (normally used to read/write data) are instead used to enter search criteria. These criteria can be entered both through the user interface and programmatically.

Entering and Exiting Find Mode

A foundset enters Find Mode when its find method is invoked. The find method returns a Boolean, indicating whether the foundset successfully entered Find Mode. It is good practice to use the find method within an if statement to avoid accidentally modifying the selected record. The foundset exits Find Mode when its search method is executed, which modifies the foundset's SQL query to reflect the specified criteria and loads the matching records. The search method returns an integer representing the number of records loaded by the find.

Example:

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

Resulting SQL Query:

Copy code
SELECT customerid FROM customers WHERE city = ? ORDER BY customerid ASC

Query Parameters:

['Berlin']

Search Criteria with Logical AND

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

Example:

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

Resulting SQL Query:

SELECT customerid FROM customers WHERE city = ? AND postalcode = ? ORDER BY customerid ASC

Query Parameters:

['Berlin', '12209']

Multiple Find Records for Logical OR

Multiple record objects can be used to articulate search criteria in Find Mode, concatenating them with a SQL OR operator.

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()) {
    city = 'Berlin';
    postalcode = '12209';
    foundset.newRecord();  // Create a new search record
    city = 'San Francisco';
    postalcode = '94117';
    foundset.search();     // Execute the query and load the records
}

Resulting SQL Query:

SELECT customerid FROM customers WHERE (city = ? AND postalcode = ?) OR (city = ? AND postalcode = ?) ORDER BY customerid ASC

Query Parameters:

['Berlin', '12209', 'San Francisco', '94117']

Finding Records Through a Relation

Find Mode can traverse the entire data model, allowing criteria to be entered in related foundsets.

Example:

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

Resulting 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

Related foundsets can also enter Find Mode and maintain the constraints of the relation.

Example:

// 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();
}

Resulting SQL Query:

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 sophisticated search requirements. Operators and operands should be concatenated as strings.

Using Find Mode from Scripting without Using Special Operators or Spaces

You can use Find Mode with non-strings as well. For example, dates and numbers are not interpreted and will be used literally. Arrays can also be used when searching for multiple values.

Example:

if (foundset.find()) {
    city = ['Berlin', 'Amsterdam'];  // city in (?, ?) ['Berlin', 'Amsterdam']
    companyid = 42;                  // literal numerical value
    startdate = new Date(99, 5, 24, 11, 33, 30, 0); // literal date value
    foundset.search();               // Execute the query and load the records
}

Note: When using a string for searching, it will be trimmed (except in the case of a CHAR column, which is padded with spaces by the database). To ensure the argument is not interpreted, use a single-element array.

Example:

if (foundset.find()) {
    // tag = ' Hello Servoy ';  // would search for trimmed
    tag = [' Hello Servoy '];  // will search for literal (untrimmed)
    foundset.search();         // select ... from ... where tag = ? [' Hello Servoy ']
}

Find Mode and the User Interface

Find Mode can be entered in one action and searched in another, allowing users to manually enter values into fields to express search criteria. Once complete, the user can execute the search, and the form's foundset will show the results.

Example:

/** @AllowToRunInFind
 * @properties={typeid:24,uuid:"088B830C-2A4F-483C-A135-5FA32A010AE9"}
 */
function doFind() {
    if (foundset.isInFind()) {  // if the foundset is already in find mode, run the search
        foundset.search();
    } else {
        foundset.find();  // otherwise, enter find mode
    }
}

Find Mode blocks the execution of any methods normally invoked from the user interface. This ensures that unintended actions do not occur during a search. The @AllowToRunInFind JSDocs tag allows the specified method to run while the form's foundset is in Find Mode.

Read-Only Fields

By default, read-only fields become editable in Find Mode, enabling users to enter search criteria. However, this behavior can be overridden using the Application API's setUIProperty method.

Example:

/** @AllowToRunInFind
 * @properties={typeid:24,uuid:"088B830C-2A4F-483C-A135-5FA32A010AE9"}
 */
function doFind() {
    if (foundset.isInFind()) {
        foundset.search();
        application.setUIProperty(APP_UI_PROPERTY.LEAVE_FIELDS_READONLY_IN_FIND_MODE, false);  // reset to default
    } else {
        application.setUIProperty(APP_UI_PROPERTY.LEAVE_FIELDS_READONLY_IN_FIND_MODE, true);  // enforce read-only fields
        foundset.find();
    }
}

Complex Searches

Servoy's Find Mode can easily satisfy complex search requirements. Any related foundset can be used to enter criteria, and any number of search records may be used in any foundset, combining any operators for each data provider.

Example:

// Find all customers in Berlin with postal code starting with '12' 
// and customers in San Francisco with phone number ending in '789'
if (foundset.find()) {
    city = 'Berlin';
    postalcode = '12%';  // Using wildcard to match postal codes starting with '12'
    foundset.newRecord(); 
    city = 'San Francisco';
    phone = '%789';  // Using wildcard to match phone numbers ending with '789'
    foundset.search();  // Execute the query and load the records
}

Canceling Find Mode

Find Mode can be programmatically canceled by invoking the loadAllRecords method of the foundset, reverting it to the query prior to entering Find Mode.

Last updated