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

Finding Records within a Related Foundset

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.

Operator	Description	Applicable Data Types	Example
||	OR: Used to implement a logical OR for two or more search conditions in the same data provider	Any	// Cities of London or Berlin city = 'Berlin||London';
|	Format: Used to separate a value and an implied format.	Date	// exactly 01/01/2001 (00:00:00 implied) orderdate = '01/01/2001|MM/dd/yyyy';
!	Not: Used to implement a logical NOT for a search condition.	Any	// Anything but Berlin city = '!Berlin';
#	Sensitivity Modifier: Implies a case-insensitive search for text columns. Implies a match on entire day for date columns.	Text, Date	// i.e. Los Angeles, lOS aNGeLES city = '#los angeles'; // any time on 01/01/2001 orderdate = '#01/01/2001|MM/dd/yyyy';
^	Is Null: Matches records where a column is null.	Any	// All null contact names, not including empty strings contactname = '^';
^=	Is Null/Empty/Zero: Matches records where a column is null, empty string value or zero numeric value	Text, Numeric	// All freights which are null or 0 freight = '^=';
<	Less than: Matches records where the column is less than the operand	Any	// i.e. 50, 99.99, but not 100, 101 freight = '<100';
<=	Less than or equal to: Matches records where the column is less than or equals the operand	Any	// i.e. Atlanta, Baghdad, Berlin, but not Buenos Aires, Cairo city = '<=Berlin';
// i.e. Atlanta, Baghdad, Berlin, but not Buenos Aires, Cairo city = '<=Berlin';
>=	Greater than or equal to: Matches records where the column is greater than or equals the operand	Any	// Any time on/after 12am new year's day 2001 orderdate = '>=01/01/2001|MM/dd/yyyy';
>	Greater than: Matches records where the column is greater than the operand	Any	// i.e. 100.01, 200, but not 99,100 freight = '>100';
...	Between: Matches records where the column is between (inclusive) the left and right operands.	Any	// Any time during the year 2001 orderdate = '01/01/2001...01/01/2002|MM/dd/yyyy'; // i.e. freight = '100...200'; // i.e. London, Lyon, Madrid, Omaha, Portland city = 'London...Portland';
%	Wild Card String: Matches records based on matching characters and wild cards	Text	city = 'New%'; // Starts with: i.e. New York, New Orleans city = '%Villa%; // Contains: i.e. Villa Nova, La Villa Linda city = '%s'; // Ends with: i.e. Athens, Los Angeles
city = 'New%'; // Starts with: i.e. New York, New Orleans city = '%Villa%; // Contains: i.e. Villa Nova, La Villa Linda city = '%s'; // Ends with: i.e. Athens, Los Angeles
_	Wild Card Character: Matches records based on	Text	// i.e. Toledo, Torino city = '%To___o%';
</code>	Escape Character: Used to escape other string operators	Text	// Escape the wild card, i.e. ...50% of Capacity... notes = '%%%';
now	Now: Matches records where the condition is right now, including time		// exact match on this second creationdate = 'now';
// exact match on this second creationdate = 'now';
today	Today: Matches records where the condition is any time today	Date	// match on anytime today orderdate = 'today';

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.