# 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: ```javascript // 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: ```sql Copy code SELECT customerid FROM customers WHERE city = ? ORDER BY customerid ASC ``` Query Parameters: ```javascript ['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: ```javascript // 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: ```sql SELECT customerid FROM customers WHERE city = ? AND postalcode = ? ORDER BY customerid ASC ``` Query Parameters: ```javascript ['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: ```javascript // 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: ```sql SELECT customerid FROM customers WHERE (city = ? AND postalcode = ?) OR (city = ? AND postalcode = ?) ORDER BY customerid ASC ``` Query Parameters: ```javascript ['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: ```javascript // 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: ```sql 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: ```javascript // 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: ```sql 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.
OperatorDescriptionApplicable Data TypesExample
||OR: Used to implement a logical OR for two or more search conditions in the same data providerAny
// 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 valueText, Numeric
// All freights which are null or 0
freight = '^=';
<Less than: Matches records where the column is less than the operandAny
// 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 operandAnyF8O2s5oMUoiX
// 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 operandAny
// 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 operandAny
// 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 cardsTexthgSOmuhYCwmC
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 onText

// i.e. Toledo, Torino
city = '%To___o%';
</code>Escape Character: Used to escape other string operatorsText

// Escape the wild card, i.e. ...50% of Capacity...
notes = '%%%';
nowNow: Matches records where the condition is right now, including timeKSE9DIDkadUw
// exact match on this second
creationdate = 'now';
todayToday: Matches records where the condition is any time todayDate
// 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: ```javascript 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 } ``` {% hint style="info" %} 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. {% endhint %} Example: ```javascript 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: ```javascript /** @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: ```javascript /** @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: ```javascript // 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. --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.servoy.com/guides/develop/programming-guide/working-with-data/searching/find-mode.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.