Simple search

Add a search condition by clicking on the field header in your table/form. As a result, the selected column or field will be added to the query builder as a search term. On the query panel, you can see the default query operator and, for some operators, one or two text fields to define the search value for each criterion. You can change the operator by clicking in the operator field and selecting the appropriate item from a drop-down list. The following query operators are available for the different data types:

• Numeric integer values: <, >, =, ≤, ≥, ≠, between, empty, not empty, not in list, in list;

• Numeric decimal values: <, >, between, empty, not empty;

• Text values: contains, equals, does not equal, starts with, ends with, empty, not empty, in list, not in list, is like, is not like, any of, none of;

• Date values: today, last 7 days, last 14 days, after, before, between, last, =, empty, not empty.

Most of these operators take a single value. Exceptions to this rule are:

• The between operator, where you have to specify the lower and the upper boundary of the interval you want find with the search.

• The in list and not in list operators both require a comma separated list of values.

• The empty and not empty operators make it possible to search for records where the value of the given field is missing or present, respectively.

• The last date query operator requires a number and a value selected from a drop-down list. For example: last 5 months.

In the case of text fields, you can use wildcards in your query together with the is like and is not like operators. Use the appropriate wildcard character for the type of database (usually "%" for zero or more characters and "_" for a single character). For instance like amino% will find all values starting with the term "amino".

You can add new conditions to the query from the context menu as well:

• Right-click on column headers in tables, and select the Filter by <column name>... option if you want to add a new condition without a pre-specified value.

• Right-click on a table cell or on a widget of a form, and use the Filter by selection option if you want to use content of the grid cell or widget as the value of the new search condition.
  In the case of a structure cell or widget, Filter by selection will open the Marvin JS editor on the search panel and the selected chemical structure will be imported on the editor canvas.

When you finished building up the query, the search can be started either by pressing Enter or by clicking the Run Search button below the query conditions. This button also works as an indicator: after a query has been executed and the data in your active form or spreadsheet is in accordance with the current query on the search panel, this button will hold the Search Again label. But whenever you modify the query (e.g., you change the criteria, add a new condition to the panel or remove one from it), the button label will be changed again to Run Search indicating that the data in your active widget does not match the query you built.

During searching, results are being loaded continuously into the active database view, so you can keep browsing in your data without any interruption.
If you started a search process which is running too long, you can cancel it at any time by pressing the Stop Search button on the panel. In thise case, the search will stop and the result set will contain only those records which were found before the cancellation.

As a result of the search, the filtered table or form will contain only the hits matching the set up query, and the field which was used for the search will have a "funnel" icon in its header. On the image below, you can see an example where the spreadsheet data has been filtered by its "Mol Weight" field ("Mol Weight < 500"), and then a new condition has been added to the query ("Donors ≤ 5"). The search has not been executed yet with the new query since there is no "filtering" icon in the "Donors" field of the spreadsheet, and the Run Search button is active.

If you want to undo filtering by a certain search condition, you can click on the "X" button in the top right corner of the condition to remove it from the query, and then you can run the search again (either by the Run Search button or by Enter).

Complex queries

You can set up complex queries as well by adding as many search conditions from your widget to your query as you like. Multiple search terms can be added to your query one by one, or by selecting various fields while holding down the Ctrl or the Shift key and then using the context (right-click) menu to add them to the query builder panel in one step.
When you have finished building up your query, click the Run Search button on the panel to execute the filtering. As a result, the widget will only contain the filtered records, and the headers of the fields which took part in the query will will have a search icon, indicating that the current data set in that widget is restricted to the query results. For instance, on the image below, filtering by the molecular weight, the activity type and the activity value returned 60 hits from the original table of 1146 activity measurements.

Visibility of search hits

There can be cases when you need a quick comparison between the result set of a query and the complete record set of a database table. A solution for this issue is provided by the search result visibility button on the query panel ( ). Turning this button on and off lets you change between displaying the search hits or the whole record set. The button is available on each tab of the query panel: on the Search and Save Query tabs as well.
On the first image below, where the search result visibility button is turned on, we can see only the 11 search hits found by a query (where the compounds contain a pyrrole ring, and their mol weight is less than 500) from a total of 1000 records:

If you want to see the whole record set instead, you have to turn off this search result visibility button. As a result, you will see the complete table of 1000 records, and the fields which were part of the query ("Structure" and "Mol Weight" in our case) will not have the search icon anymore:

Querying BLOB and text fields with Oracle text index

You can browse and query Binary Large OBjects (BLOBs) with Oracle text index. You can search keywords or run contextual queries on documents in your database (such as pdf, Microsoft Word or other formats).

It is possible to run queries on text fields using Oracle text index. The available query operators are those which are specified in the Oracle text index, like CONTAINS, NEAR, AND, etc.

For more details check this white paper on the Oracle website.